"Error: SQLITE_ERROR: no such column: undefined"

ScheepersJohan · 7 April 2020 08:57

Hi,

My sqlite put this error out - "Error: SQLITE_ERROR: no such column: undefined"

Statement to sqlite -
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20.3,1586247316916)"
_msgid: "b4b66b3f.f93918"

This statement come from a function node whit the following:

sql = "INSERT INTO sensor_data (id,device,value,epoch) " +
"VALUES ('28-02131a935aa','watertemp',"+msg.watertemperature+","+epoch+")";
outputs.push({topic:sql});

But if i check my DB the data are written to it.

Any help will be appreciated.

Versions:
7 Apr 10:53:05 - [info] Node-RED version: v1.0.4
7 Apr 10:53:05 - [info] Node.js version: v12.16.1
7 Apr 10:53:05 - [info] Linux 4.19.97-v7+ arm LE
7 Apr 10:53:06 - [info] Loading palette nodes
7 Apr 10:53:09 - [info] Dashboard version 2.20.0 started at /ui

zenofmud · 7 April 2020 09:46

What is the schema for the table? (in CLI start sqlite and issue .fullschema)

ScheepersJohan · 7 April 2020 10:10

sqlite> .fullschema
CREATE TABLE sensor_data (
id INTEGER NOT NULL,
device TEXT,
sensor TEXT,
value REAL,
epoch INTEGER,
timestamp INTEGER DEFAULT CURRENT_TIME

zenofmud · 7 April 2020 11:29

Which node are you using for the sqlite? i just tested with your schema and insert and it worked fine using the node-red-node-sqlite v0.4.3

ScheepersJohan · 7 April 2020 11:44

I amusing node-red-node-sqlite 0.4.3, the same version.

The strange thing it throws the error but still wights it to the DB (so all the data is there).

It is annoying seeing in the Debug these messages.

zenofmud · 7 April 2020 11:48

can yu provide your complete flow (read this first) and I'll take a look

Also, what platform are you running on? Pi?, Mac? in? and what OS/version

ScheepersJohan · 7 April 2020 11:54

Running on a PI, have update every thing this morning.

[{"id":"469cd235.b8809c","type":"tab","label":"Water Temp","disabled":false,"info":""},{"id":"165fb35a.b076ed","type":"inject","z":"469cd235.b8809c","name":"","topic":"","payload":"","payloadType":"date","repeat":"300","crontab":"","once":true,"onceDelay":0.1,"x":110,"y":80,"wires":[["80a9d7ce.c2bcb8","d8ae7611.fa6628","371b0454.030c1c","7ec18c3b.c4d3e4"]]},{"id":"80a9d7ce.c2bcb8","type":"sensor-ds18b20","z":"469cd235.b8809c","name":"watertemperature","topic":"","sensorid":"28-02131a935faa","timer":"1","repeat":false,"x":330,"y":80,"wires":[["741591a.d69257","3da2719f.202a8e","fa94bac6.f91d48","c881cd27.052c4"]]},{"id":"741591a.d69257","type":"ui_chart","z":"469cd235.b8809c","name":"Water Temp","group":"749dec5.2485a14","order":2,"width":15,"height":"10","label":"","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"604800","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":590,"y":80,"wires":[["eeb8d414.3d4c98"]]},{"id":"eeb8d414.3d4c98","type":"file","z":"469cd235.b8809c","name":"","filename":"/home/pi/Documents/database/dateed/chart/watertemp.dump","appendNewline":false,"createDir":false,"overwriteFile":"false","encoding":"none","x":960,"y":80,"wires":[[]]},{"id":"941913a0.b2d3e","type":"function","z":"469cd235.b8809c","name":"Save to DB","func":"var sql = \"\";\nvar d = new Date();\nvar epoch = d.getTime();\nvar outputs = [];\n\n//sql = \"INSERT INTO sensor_data (id,device,sensor,value,epoch,timestamp) \" +\n//        \"VALUES ('28-02131a935aa','watertemp','ds18b20temp',\"+msg.payload.watertemperature+\",\"+epoch+\",\"+epoch+\")\";\n//outputs.push({topic:sql});\n\nsql = \"INSERT INTO sensor_data (id,device,value,epoch) \" +\n        \"VALUES ('28-02131a935aa','watertemp',\"+msg.watertemperature+\",\"+epoch+\")\";\noutputs.push({topic:sql});\nsql = \"INSERT INTO sensor_data (id,device,value,epoch) \" +\n        \"VALUES ('28-00006c7f31d','airtemp',\"+msg.airtemperature+\",\"+epoch+\")\";\noutputs.push({topic:sql});\n\n    \n// Update the status with current timestamp\nvar now = new Date();\nvar yyyy = now.getFullYear();\nvar mm = now.getMonth() < 9 ? \"0\" + (now.getMonth() + 1) : (now.getMonth() + 1); // getMonth() is zero-based\nvar dd  = now.getDate() < 10 ? \"0\" + now.getDate() : now.getDate();\nvar hh = now.getHours() < 10 ? \"0\" + (now.getHours() +1) : (now.getHours() +1);\nvar mmm  = now.getMinutes() < 10 ? \"0\" + now.getMinutes() : now.getMinutes();\nvar ss  = now.getSeconds() < 10 ? \"0\" + now.getSeconds() : now.getSeconds();\nnode.status({fill:\"blue\",shape:\"ring\",text:\"Last update: \"+dd + \".\" + mm + \".\" + yyyy + \" \" + hh + \":\" + mmm + \":\" + ss});    \n      \nreturn [ outputs ];","outputs":1,"noerr":0,"x":930,"y":240,"wires":[["55f055c2.83092c","faaab7b8.1a4d58"]]},{"id":"3da2719f.202a8e","type":"function","z":"469cd235.b8809c","name":"","func":"msg.watertemperature = msg.payload;\nmsg.topic = \"watertemperature\";\nreturn msg;","outputs":1,"noerr":0,"x":570,"y":160,"wires":[["941913a0.b2d3e"]]},{"id":"55f055c2.83092c","type":"sqlite","z":"469cd235.b8809c","mydb":"b03cc2b9.56145","sql":"","name":"Node Red DB","x":1160,"y":240,"wires":[[]]},{"id":"d8ae7611.fa6628","type":"sensor-ds18b20","z":"469cd235.b8809c","name":"airtemperature","topic":"","sensorid":"28-000006c7f31d","timer":"1","repeat":false,"x":300,"y":200,"wires":[["bc142eab.233f7","862d87e.8422a78","bfad3778.264a18"]]},{"id":"bc142eab.233f7","type":"function","z":"469cd235.b8809c","name":"","func":"msg.airtemperature = msg.payload;\nmsg.topic = \"airtemperature\";\nreturn msg;","outputs":1,"noerr":0,"x":730,"y":260,"wires":[["941913a0.b2d3e","faaab7b8.1a4d58"]]},{"id":"faaab7b8.1a4d58","type":"debug","z":"469cd235.b8809c","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1110,"y":360,"wires":[]},{"id":"fa94bac6.f91d48","type":"change","z":"469cd235.b8809c","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"watertemperature","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":570,"y":120,"wires":[[]]},{"id":"862d87e.8422a78","type":"ui_chart","z":"469cd235.b8809c","name":"Air Temp","group":"749dec5.2485a14","order":3,"width":15,"height":"10","label":"","chartType":"line","legend":"false","xformat":"HH:mm","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"604800","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"useOldStyle":false,"outputs":1,"x":560,"y":200,"wires":[["38fe66ed.ffa91a"]]},{"id":"c881cd27.052c4","type":"ui_text","z":"469cd235.b8809c","group":"749dec5.2485a14","order":1,"width":"15","height":"2","name":"","label":"Water Temprature","format":"{{msg.payload}} C","layout":"col-center","x":610,"y":40,"wires":[]},{"id":"bfad3778.264a18","type":"ui_text","z":"469cd235.b8809c","group":"749dec5.2485a14","order":1,"width":"15","height":"2","name":"","label":"Air Temprature","format":"{{msg.payload}} C","layout":"col-center","x":560,"y":280,"wires":[]},{"id":"371b0454.030c1c","type":"file in","z":"469cd235.b8809c","name":"","filename":"/home/pi/Documents/database/dateed/chart/watertemp.dump","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":970,"y":140,"wires":[["655b8206.ecd75c"]]},{"id":"655b8206.ecd75c","type":"json","z":"469cd235.b8809c","name":"","property":"payload","action":"","pretty":false,"x":1270,"y":140,"wires":[["741591a.d69257"]]},{"id":"38fe66ed.ffa91a","type":"file","z":"469cd235.b8809c","name":"","filename":"/home/pi/Documents/database/dateed/chart/airtemp.dump","appendNewline":false,"createDir":false,"overwriteFile":"false","encoding":"none","x":430,"y":340,"wires":[[]]},{"id":"7ec18c3b.c4d3e4","type":"file in","z":"469cd235.b8809c","name":"","filename":"/home/pi/Documents/database/dateed/chart/airtemp.dump","format":"utf8","chunk":false,"sendError":false,"encoding":"none","x":410,"y":400,"wires":[["17f854a4.77428b"]]},{"id":"17f854a4.77428b","type":"json","z":"469cd235.b8809c","name":"","property":"payload","action":"","pretty":false,"x":750,"y":340,"wires":[["bfad3778.264a18"]]},{"id":"749dec5.2485a14","type":"ui_group","z":"","name":"Temperature","tab":"62872003.cd4a5","order":1,"disp":true,"width":30,"collapse":false},{"id":"b03cc2b9.56145","type":"sqlitedb","z":"","db":"/home/pi/Documents/database/dateed/db/dateed.db","mode":"RWC"},{"id":"62872003.cd4a5","type":"ui_tab","z":"","name":"Temperatutres","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

Colin · 7 April 2020 12:33

I think the first thing to do is to add a debug node showing what is going into the sql node. Set the debug node to Show Complete Message. Then you will see exactly what query is being sent to the node.

ScheepersJohan · 7 April 2020 12:36

Hi,

This is form the debug node.

07/04/2020, 14:35:08node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20.3,1586262908564) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20.3,1586262908564)"
_msgid: "d780d3e1.ea93c"

Colin · 7 April 2020 12:39

id should be an integer.

ScheepersJohan · 7 April 2020 12:59

Modified my db now that ID are text - it still throws the same error

CREATE TABLE sensor_data (
id TEXT,
device TEXT,
sensor TEXT,
value REAL,
epoch INTEGER,
timestamp INTEGER DEFAULT CURRENT_TIME
);

The data do go int the DB

zenofmud · 7 April 2020 13:02

Stick a debug node (set to display complete msg object) on the output of your 'Save to DB' function node and look at the output.

What do you see in the debug(s)?

ScheepersJohan · 7 April 2020 13:07

07/04/2020, 15:06:02node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20.1,1586264762883) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20.1,1586264762883)"
_msgid: "9cf4cff6.f74a3"

This is the debug message

zenofmud · 7 April 2020 13:21

Have you changed that function since you posted the flow? Because I'm seeing two output msg's for each input.

sql = "INSERT INTO sensor_data (id, device,value,epoch) " +
        "VALUES ('28-02131a935aa','watertemp',"+msg.watertemperature+","+epoch+")";
outputs.push({topic:sql});
sql = "INSERT INTO sensor_data (id,device,value,epoch) " +
        "VALUES ('28-00006c7f31d','airtemp',"+msg.airtemperature+","+epoch+")";
outputs.push({topic:sql});

Is that code still in our function?

ScheepersJohan · 7 April 2020 13:26

Yes I have two, they are identical in the sens as how they are processes, but two different sensors, different ID and different device.

Both give the same error.

Debug for watertemp

07/04/2020, 15:25:25node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',undefined,1586265925522) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',undefined,1586265925522)"
_msgid: "361602d5.f5f24e"

Debug for airtemp

07/04/2020, 15:25:25node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-00006c7f31d','airtemp',undefined,1586265925614) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-00006c7f31d','airtemp',undefined,1586265925614)"
_msgid: "15a5681d.b183a8"

zenofmud · 7 April 2020 13:31

If you look closely what do you see??

07/04/2020, 15:25:25node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-00006c7f31d','airtemp',undefined,1586265925614) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-00006c7f31d','airtemp',undefined,1586265925614)"
_msgid: "15a5681d.b183a8"

ScheepersJohan · 7 April 2020 13:55

Found the problem:

Each sensor generates a flow, so each sensor triggers two flows from fx"Save to DB" to sqlite, as there are two statements outputo for each flow out into the fx"Save to DB" (four flows in total between fx"Save to DB" and sqlite) one flow for sensor now missing its value.

To fix it I need to put a join node, one flow into fx"Save to DB" with both two sensor data in it "msg.watertemperature" and "msg.airtemperature"

Thank you for your help, really appreciate it

07/04/2020, 15:35:08node: 812f3dc9.bc1c2
INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20,1586266508486) : msg : Object
object
topic: "INSERT INTO sensor_data (id,device,value,epoch) VALUES ('28-02131a935aa','watertemp',20,1586266508486)"
_msgid: "45279e68.e3b38"

Colin · 7 April 2020 14:50

The moral is, when you post debug output here, make sure that the output you post corresponds to the error situation.

ScheepersJohan · 7 April 2020 17:30

I did not notice there were 4 debugs cumming from the 2 flows, 2 from the messages of the sensors and two error messages, so quit a few information at one time. That is were it were not to easy to pic up the correct information, I try always to be as thorough to provide information so the issues can be solve as easily as possible.

@Colin and @zenofmud thanks for your assistance, greatly appreciated

system · 6 June 2020 17:30

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

Topic		Replies	Views
Sqlite error - no such table General	9	3508	26 October 2020
SQLite "Error: SQLITE_ERROR: no such column:" General	14	13074	25 July 2019
Help SQLite error General	8	2159	3 July 2019
Error while inserting uuid into sqlite3 in express General	7	505	20 April 2021
SQLite Error - Exporting data to sql database General node-red-dashboard	5	294	24 September 2022

"Error: SQLITE_ERROR: no such column: undefined"

Related topics