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

store sql resultset into datatable

SOLVED
hoa
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

Re: store sql resultset into datatable

I found the solution here :

 

https://community.ptc.com/t5/ThingWorx-Developers/Unable-To-Convert-From-org-mozilla-javascript-Uniq...

 

Instead of var resultset, put "result".

Thanks, now it works.

3 REPLIES 3

Re: store sql resultset into datatable

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.

Highlighted

Re: store sql resultset into datatable

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);

}

Re: store sql resultset into datatable

I found the solution here :

 

https://community.ptc.com/t5/ThingWorx-Developers/Unable-To-Convert-From-org-mozilla-javascript-Uniq...

 

Instead of var resultset, put "result".

Thanks, now it works.