Select data from SQlite not working

General
1

Hi,
I am querying from sqlite using but data is not getting selected and under debug its shows undefined.
My flow is here

[{"id":"261492c80ec624f0","type":"tab","label":"Flow 1","disabled":false,"info":"","env":[]},{"id":"8459c16cfddf3fab","type":"inject","z":"261492c80ec624f0","name":"","props":[{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":90,"y":260,"wires":[["de17100763398a0a"]]},{"id":"de17100763398a0a","type":"function","z":"261492c80ec624f0","name":"","func":"var msg = {\n \"topic\" : \"SELECT seq from sqlite_sequence Where name = 'Stn_loss_L3_VIN'\"\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":250,"y":220,"wires":[["db8ee5ccff734a80"]]},{"id":"db8ee5ccff734a80","type":"sqlite","z":"261492c80ec624f0","mydb":"53148151.a90cc","sqlquery":"msg.topic","sql":"","name":"C","x":425,"y":220,"wires":[["3d852928ed498b16"]],"l":false},{"id":"3d852928ed498b16","type":"function","z":"261492c80ec624f0","name":"","func":"var msg = {\n   \"topic\" : \"SELECT Station from Stn_loss_L3_VIN Where Master_ID =\"+ \"'\" + msg.payload[0].seq + \"'\"\n}\n//var a = flow.get('id');\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":590,"y":220,"wires":[["74dcedd03dc65a66"]]},{"id":"f71dd4f3b43c60de","type":"sqlite","z":"261492c80ec624f0","mydb":"53148151.a90cc","sqlquery":"msg.topic","sql":"","name":"C","x":705,"y":220,"wires":[[]],"l":false},{"id":"74dcedd03dc65a66","type":"debug","z":"261492c80ec624f0","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":860,"y":220,"wires":[]},{"id":"53148151.a90cc","type":"sqlitedb","db":"C:\\Users\\PASYSTEM\\Andon.db","mode":"RWC"}]

Error
Stn_db

2

The Master_ID is defined as an integer, the select statement is trying to match with a string = no match.

You can change the 2nd function node to something like:

let msg = {}
msg.topic = `SELECT Station from Stn_loss_L3_VIN Where Master_ID = ${msg.payload[0].seq}`
return msg

Then again, you could do both these queries in a single statement with a subselect, subquery or join, eg something like:

SELECT t1.seq, t2.Station FROM sqlite_sequence t1
INNER JOIN Stn_loss_L3_VIN t2 
ON t1.seq = t2.Master_ID
3

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.