Skip to main content
1-Visitor
May 30, 2019
Solved

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.

hoaAnswer
1-Visitor
June 1, 2019

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.

3 replies

5-Regular Member
May 31, 2019

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.

Translate
hoa1-VisitorAuthor
1-Visitor
June 1, 2019

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

}

Translate
hoa1-VisitorAuthorAnswer
1-Visitor
June 1, 2019

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.

Translate