SQLite INSERT FUNCTION For random values

[Mohit]

I am using Function node and inside that i am using insert query to insert random values but when using msg.payload for values and description part in debug window it showing values and description but also same time "Error: SQLITE_ERROR: unrecognized token: "5000R00001"" is coming.

here is image of my flow

[Steve-Mcl]

@Mohit

here is image of my flow

Can't tell you what the cause is from an image. You'd need to export your flow if you want someone to find the issue.

That said, the error is fairly clear, your SQL statement is invalid. Put a Debug node after the function and take a close look at the SQL statement you are sending to the sqlite node.

[Mohit]

flows (1).json (14.3 KB)
Here is my flow

[Steve-Mcl]

Hi, please dont upload files. Share your flow by pasting it into a code block.

See this post for more details - How to share code or flow json

Also, add a debug node as I said & show us what the SQL statement being generated is.

[Steve-Mcl]

Seeing as you did not provide sample data (we dont have access to your OPC values) so i made up some fake data.

Here is a version that uses prepared statements (simplifies creation SQL INSERT and protects from SQL Inject)

[{"id":"41cfeb84eeef7b1a","type":"inject","z":"814056de43f6d068","name":"CREATE TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"1","topic":"CREATE TABLE MYDBASE(VALUE NUMERIC NOT NULL, description ntext, currentdate DATE, currenttime TIME)","payloadType":"date","x":240,"y":140,"wires":[["41afe683bb1b4c4c"]]},{"id":"12e42dbf4710315d","type":"inject","z":"814056de43f6d068","name":"INSERT TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"INSERT INTO MYDBASE(value , DESCRIPTION, currentdate, currenttime) values(22.4, 'TEST DESCRIPTION', date('now'), time('now'))","payloadType":"date","x":240,"y":200,"wires":[["41afe683bb1b4c4c"]]},{"id":"609dad1c5e128f06","type":"inject","z":"814056de43f6d068","name":"SELECT *","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM MYDBASE","payloadType":"date","x":240,"y":260,"wires":[["41afe683bb1b4c4c"]]},{"id":"11b02c4cbe8a9536","type":"inject","z":"814056de43f6d068","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from MYDBASE","payloadType":"date","x":240,"y":320,"wires":[["41afe683bb1b4c4c"]]},{"id":"a3e8c79a35eff6a7","type":"inject","z":"814056de43f6d068","name":"DROP","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE MYDBASE","payloadType":"date","x":230,"y":380,"wires":[["41afe683bb1b4c4c"]]},{"id":"41afe683bb1b4c4c","type":"sqlite","z":"814056de43f6d068","mydb":"d42c7defda164a47","sqlquery":"msg.topic","sql":"","name":"SQLITE","x":900,"y":260,"wires":[["06a3f82ee9081e2c"]]},{"id":"41a5e1a16606379f","type":"function","z":"814056de43f6d068","name":"prepare SQL Insert","func":"//build SQL\nmsg.topic = `INSERT INTO MYDBASE \n(VALUE, DESCRIPTION, currentdate, currenttime) \nVALUES \n($VALUE, $DESCRIPTION,  date('now'), time('now'));\n`\n//add parameters\nmsg.params = {\n    $VALUE: msg.payload.value,\n    $DESCRIPTION: msg.payload.displayName.text,\n}\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":710,"y":460,"wires":[["6810944311a39943","2b5cf0cb58196157"]]},{"id":"5b5b9c7c2fa33c5c","type":"function","z":"814056de43f6d068","name":"all in msg.payload array","func":"for (let index = 0; index < msg.payload.length; index++) {\n    const m = { payload: msg.payload[index] };\n    node.send(m);    \n}\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":450,"y":460,"wires":[["41a5e1a16606379f"]]},{"id":"06a3f82ee9081e2c","type":"debug","z":"814056de43f6d068","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":1070,"y":260,"wires":[]},{"id":"1006155ec428b22b","type":"inject","z":"814056de43f6d068","name":"fake data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"value\":999,\"displayName\":{\"text\":\"police\"}},{\"value\":123.456,\"displayName\":{\"text\":\"My Float\"}}]","payloadType":"json","x":240,"y":460,"wires":[["5b5b9c7c2fa33c5c"]]},{"id":"6810944311a39943","type":"sqlite","z":"814056de43f6d068","mydb":"d42c7defda164a47","sqlquery":"prepared","sql":"INSERT INTO MYDBASE ([VALUE], [DESCRIPTION], currentdate, currenttime) \nVALUES ($VALUE, $DESCRIPTION,  date('now'), time('now'))","name":"SQLITE","x":920,"y":460,"wires":[[]]},{"id":"2b5cf0cb58196157","type":"debug","z":"814056de43f6d068","name":"look at my values in debug sidebar","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1000,"y":540,"wires":[]},{"id":"d42c7defda164a47","type":"sqlitedb","db":"c:\\temp\\sqllitedb","mode":"RWC"}]