# Help SQLite error

**URL:** <https://discourse.nodered.org/t/help-sqlite-error/12837>\
**Category:** General\
**Created:** [2 July 2019 20:25 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837 "2019-07-02T20:25:26Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![RickyChan21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rickychan21/32/9584_2.png) [@RickyChan21](https://discourse.nodered.org/u/RickyChan21)\
**Post date:** [2 July 2019 20:25 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/1 "2019-07-02T20:25:26Z")

</div>

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:

```auto
[{"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"}]

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [2 July 2019 20:44 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/2 "2019-07-02T20:44:57Z")

</div>

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.

> [@How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506):
>
> To share code samples or flow json in this forum, you need to take care to format it properly so that it is displayed correctly. The easiest way to do that is to click the 'Preformatted Text' button in the toolbar: [image] Then paste in your code or flow json. You should end up with three back-tick characters - ``` - on their own line before and after the code. On standard US English keyboards, this is on the same key as the ~ character. Using the backticks will ens…

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [2 July 2019 21:41 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/3 "2019-07-02T21:41:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![RickyChan21](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/rickychan21/32/9584_2.png) [@RickyChan21](https://discourse.nodered.org/u/RickyChan21)\
**Post date:** [3 July 2019 14:46 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/4 "2019-07-03T14:46:22Z")

</div>

Sorry, here is the full flow

```auto
[{"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

```auto
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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/2X/b/bc9a9b1eb864a5751c749aae0b289c3ac276d5c7.png)

---

<div class="post-metadata">

**Author:** ![ukmoose](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ukmoose/32/13_2.png) [@ukmoose](https://discourse.nodered.org/u/ukmoose)\
**Post date:** [3 July 2019 15:35 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/5 "2019-07-03T15:35:51Z")

</div>

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+"\")"};`

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [3 July 2019 15:53 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/6 "2019-07-03T15:53:21Z")

</div>

Might be helpful as addition, since NR nowadays requires node v8.5 as minimum, you can use [ES6 template literals syntax](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals):

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

```

In the Python world there's a saying "readability counts" (part of [PEP20](https://www.python.org/dev/peps/pep-0020/)). Using a template string will get rid of the additional quotes, carefully put the payload into the message, and bring a more readable result.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [3 July 2019 16:28 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/7 "2019-07-03T16:28:18Z")

</div>

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

Id probably go with single quotes for comparability anyway.

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

```

---

<div class="post-metadata">

**Author:** ![afelix](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/afelix/32/9743_2.png) [@afelix](https://discourse.nodered.org/u/afelix)\
**Post date:** [3 July 2019 16:47 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/8 "2019-07-03T16:47:43Z")

</div>

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...

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [3 July 2019 18:36 UTC](https://discourse.nodered.org/t/help-sqlite-error/12837/9 "2019-07-03T18:36:16Z")

</div>

In sqlite strings should be identified by single quotes.  
[https://sqlite.org/lang\_expr.html](https://sqlite.org/lang_expr.html)  
In particular the section Literal Values.