cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

store sql resultset into datatable

hoa
3-Visitor
3-Visitor

store sql resultset into datatable

I have got the Trial Version of Thingworx and have imported the PostgreSQLExtensionPackage from the TW-Marketplace. The database connection works, a simple service of the type "SQL-query" shows results.

How can I alter this service in Java script to store the resultset of the SQL (e.g. select * from tab1) in a data table?

 

Thanks for your help.

1 ACCEPTED SOLUTION

Accepted Solutions
hoa
3-Visitor
3-Visitor
(To:hoa)

3 REPLIES 3
zyuan1
18-Opal
(To:hoa)

If you have PgSQL extension and can create SQL query service on the related thing, then you can get an infotable result from the "select * " command you used.

 

Get this infotable result of the SQL query service and use it as the input for AddorUpdateDatatableEntries service, if you have the Datatable with correct datashape.

hoa
3-Visitor
3-Visitor
(To:zyuan1)

Thanks for the answer. I created a SQL-service called selectall(); I created a DataTable_Spindel which implements a DataShape_Spindel.

In another Service, where I use the resultset of the selectall()-Service, the following script throws:  Error executing service. Message ::Unable To Convert From org.mozilla.javascript.UniqueTag to INFOTABLE - See Script Error Log for more details. Where is the error?

 

var resultset=me.selectall();

for(var row =1;row < 500; row++){
var values = Things["DataTable_Spindel"].CreateValues();
values.zeitstempel=resultset[row].zeitstempel; //text;
values.nc_programm=resultset[row].nc_programm; //text;
values.werkzeug =resultset[row].werkzeug; //text;
values.achsvorschub =resultset[row].achsvorschub; //numeric;
values.vorschuboverride=resultset[row].vorschuboverride; //numeric;
values.program_vorschub=resultset[row].program_vorschub; //numeric;
values.keineangabe =resultset[row].keineangabe; //numeric;
values.spindeloverride=resultset[row].spindeloverride; //numeric;
values.spindeldrehzahl =resultset[row].spindeldrehzahl; //umeric;
values.druck1=resultset[row].druck1; //numeric;
values.druck2=resultset[row].druck2; //numeric;
values.actual_control_postition1 =resultset[row].actual_control_postition1; //numeric;
values.actual_feed_rate_m1 =resultset[row].actual_feed_rate_m1; //numeric;
values.actual_control_postition2=resultset[row].actual_control_postition2; //numeric;
values.actual_feed_rate_m2=resultset[row].actual_feed_rate_m2; //numeric;

var paramstable = {
tags : undefined,
source : me.name,
values : values,
location : undefined
};


var id = Things["DataTable_Spindel"].AddOrUpdateDataTableEntry(paramstable);

}

hoa
3-Visitor
3-Visitor
(To:hoa)

I found the solution here :

 

https://community.ptc.com/t5/ThingWorx-Developers/Unable-To-Convert-From-org-mozilla-javascript-UniqueTag-to/td-p/514016

 

Instead of var resultset, put "result".

Thanks, now it works.

Top Tags