Skip to main content
M
MA873117416-Pearl
16-Pearl
March 21, 2024
Solved

How to read/get SQL Query Output which is NUMBER

  • March 21, 2024
  • 1 reply
  • 1520 views

This is my Query which returns the result in Infotable.

 

 

SELECT COUNT(*) AS TotalCount
FROM dbo.ML1QualityDataTable
WHERE lineName = [[lineName]]
AND CAST(qualityCheckTimestamp AS DATE) = CAST(GETDATE() AS DATE);

 

 

Result of Query:

 

Jamal8548_0-1711000136601.png

 

 

Now here is my service which executes the above service and get the result but here i am unable to get the result as an output. How to read this output of query from the JS service.   Mydatashape contains only one field named TotalCount of infotable type.

 

 

let mergedInfotable = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({
 infoTableName: "InfoTable",
 dataShapeName: "V2.TotalCount.DS"
 });

let ab = Things['V2.MYSQLDatabaseController'].SQLQuery_GetTodaysEntriesCount({
 lineName:'ML1'
});

let a= mergedInfotable.AddRow(ab.rows[0]);
let result=a;
logger.info(ab.rows[0]);

 

 

 

i get this error:-

 

 Error executing service ComputeKey. Message :: Unable To Convert To Value Collection: Conversion Error on Field TotalCount : Unable To Convert From java.lang.Integer to INFOTABLE - See Script Error Log for more details.

Best answer by MA8731174

I got the solution so it does not matter that if query returns the result as an infotable. We can access it as a number. Here is the code:

 

let ab = Things['V2.MYSQLDatabaseController'].SQLQuery_GetTodaysEntriesCount({
 lineName:'ML1'
});

let result= ab.TotalCount;

// I have set the output for this service as a number but the SQLQuery service output is infotable. 
//Happy Coding

1 reply

M
MA873117416-PearlAuthorAnswer
16-Pearl
March 21, 2024

I got the solution so it does not matter that if query returns the result as an infotable. We can access it as a number. Here is the code:

 

let ab = Things['V2.MYSQLDatabaseController'].SQLQuery_GetTodaysEntriesCount({
 lineName:'ML1'
});

let result= ab.TotalCount;

// I have set the output for this service as a number but the SQLQuery service output is infotable. 
//Happy Coding
Rocko
Rocko19-Tanzanite
19-Tanzanite
March 21, 2024

Just so you know, this is just a shortcut/syntactic sugar version of

let result=ab.rows[0].TotalCount;

 

    M
    MA873117416-PearlAuthor
    16-Pearl
    March 21, 2024

    Thank you @Rocko for your reply, as my SQL query returns output as an infotable and only in one row which is TotalCount so just for testing i thought i can directly access it from infotable and access it but then i have discovered that it is just a NUMBER and i can access it direclty as above. 

    Terms & ConditionsCookie settingsAccessibility statement