Help SQLite error

General
1

Hello

I have a MQTT node subscribed to an arduino with a temperature sensor. This node then is connected to a function that insert the value to SQLite database. I tried testing this using the terminal on linux to publish into that node and it works with integer numbers but when I try to publish letters o words I get an SQLite error "Error: SQLITE_ERROR: no such column: word"

Here is the flow if anyone want to check it out:

[{"id":"b4c2604c.6ccfd","type":"sqlite","z":"8e0d9d8d.74dab","mydb":"8e6657de.de04b8","sqlquery":"msg.topic","sql":"","name":"db","x":508.77778244018555,"y":298.7407560348511,"wires":[["6b26dad.2402924"]]},{"id":"8e6657de.de04b8","type":"sqlitedb","z":"","db":"/home/pi/test.db","mode":"RWC"}]
2

Please see this post for how to format your flow so that it is importable. You can edit your previous post .
Also put a debug node showing what is being fed to the sql node. Set it to Show Complete Message and post a screenshot here of what you see (after convincing yourself it show what you expect of course.

3

All you provided was the sql node and it doesn't show what you are sending to the database.

  1. what is the code you used to create the table?
  2. what is the code you are using to create the query?
  3. have you added a debug node (set to display the complete msg object) to the node feeding the sqlite node?

A larger copy if your flow would be useful.

4

Sorry, here is the full flow

[{"id":"94608d98.67353","type":"mqtt in","z":"8e0d9d8d.74dab","name":"mq135","topic":"gas","qos":"2","datatype":"auto","broker":"5aea07cc.587608","x":93.62965774536133,"y":145.74074840545654,"wires":[["6b26dad.2402924","434c09c4.654a08"]]},{"id":"6b26dad.2402924","type":"debug","z":"8e0d9d8d.74dab","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":533.8518142700195,"y":109.85188007354736,"wires":[]},{"id":"b4c2604c.6ccfd","type":"sqlite","z":"8e0d9d8d.74dab","mydb":"8e6657de.de04b8","sqlquery":"msg.topic","sql":"","name":"db","x":539.7777633666992,"y":298.7407474517822,"wires":[["6b26dad.2402924"]]},{"id":"209c7162.3a851e","type":"inject","z":"8e0d9d8d.74dab","name":"","topic":"","payload":"fxsdfsdfxd","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123.01953125,"y":288.00390815734863,"wires":[["434c09c4.654a08"]]},{"id":"434c09c4.654a08","type":"function","z":"8e0d9d8d.74dab","name":"INSERT","func":"var newMsg = {\n \"topic\": \"INSERT INTO mq(valor) VALUES (\"+msg.payload+\")\"\n}\nreturn newMsg;\n","outputs":1,"noerr":0,"x":312.5234375,"y":210.88284873962402,"wires":[["b4c2604c.6ccfd","6b26dad.2402924"]]},{"id":"5aea07cc.587608","type":"mqtt-broker","z":"","name":"Core","broker":"172.20.10.2","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"8e6657de.de04b8","type":"sqlitedb","z":"","db":"/home/pi/test.db","mode":"RWC"}]

  1. I created the table on the Raspbian terminal with SQLite. CREATE table mq(valor varchar(20))

  2. I used a function node to create the insert query

var newMsg = {
 "topic": "INSERT INTO mq(valor) VALUES ("+msg.payload+")"
}
return newMsg;
  1. Yes, there is a debug node. I used a inject node to test inserting letter or words and this is the error I get.
    image
5

Just a thought, if it's failing on strings it might be because they aren't in quotes.

Try changing your var newMsg to be

newMsg = { "topic": "INSERT INTO mq (valor) VALUES (\""+msg.payload+"\")"};

6

Might be helpful as addition, since NR nowadays requires node v8.5 as minimum, you can use ES6 template literals syntax:

newMsg = {
    topic: `INSERT INTO mq (valor) VALUES ("${msg.payload}")`
};

In the Python world there's a saying "readability counts" (part of PEP20). Using a template string will get rid of the additional quotes, carefully put the payload into the message, and bring a more readable result.

7

Are double quotes valid SQL?
In SQL server, that would be an error.

Id probably go with single quotes for comparability anyway.

newMsg = {
    topic: `INSERT INTO mq (valor) VALUES ('${msg.payload}')`
};
1 Like
8

I don’t do SQL a lot (trauma stuff, there’s a reason why I outsource the DDL nowadays and use an ORM for the rest), but from what I remember from my classes a decade ago we had to use double quotes everywhere. I can’t remember anything of the MSSQL classes I had 6 years ago...

1 Like
9

In sqlite strings should be identified by single quotes.
https://sqlite.org/lang_expr.html
In particular the section Literal Values.

1 Like