# Inserting data into sqlite db

**URL:** <https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634>\
**Category:** General\
**Created:** [12 November 2021 13:39 UTC](https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634 "2021-11-12T13:39:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tanglefoot](https://avatars.discourse-cdn.com/v4/letter/t/4da419/32.png) [@Tanglefoot](https://discourse.nodered.org/u/Tanglefoot)\
**Post date:** [12 November 2021 13:39 UTC](https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634/1 "2021-11-12T13:39:23Z")

</div>

I've been trying to follow the advice given in [this post](https://discourse.nodered.org/t/sqlite-insert-command-syntax/21471/5) but still having problems  
I've created a table using the inject node

```auto
CREATE TABLE myroute(id INTEGER PRIMARY KEY AUTOINCREMENT, ModeS TEXT, Reg TEXT, Type TEXT)

```

Viewing the table using DB Browser (on Windows) the table appears to be correct.

Using a function node I'm trying to insert the data from 3 messages into the database  
(at the moment the messages are hard coded with values using an inject node msg.modeS=AE4589, msg.reg=58-2584, msg.type=C-17 - all entered as strings)

```auto
var sql = 'INSERT INTO myroute '
sql += '(ModeS, Reg, Type)'
sql += 'VALUES ('
sql += msg.modeS+', '
sql += msg.reg+', '
sql += msg.type
sql += ')'
msg.topic = sql

return msg;

```

When I try to insert the data I get  
"Error: SQLITE\_ERROR: no such column: AE4589"

I put a debug node on the msg.topic and I get this  
INSERT INTO myroute (ModeS, Reg, Type)VALUES (AE4589, 58-2584, C-17)  
which to me looks correct.  
I've obviously made a mistake somewhere

If I put in actual values in the function node above it works Ok  
Any help appreciated

Paul

---

<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:** [12 November 2021 13:59 UTC](https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634/2 "2021-11-12T13:59:24Z")

</div>

String values have to be in quotes. Check an sqlite tutorial.

It is often easier to build such strings using the template literal syntax, something like

```auto
msg.topic = `INSERT INTO myroute ( ModeS, Reg, Type) VALUES ("${msg.mode}","${msg.req}","${msg.type}")`

```

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [12 November 2021 16:05 UTC](https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634/3 "2021-11-12T16:05:10Z")

</div>

When you have got the basics working. I recommend looking at "prepared statements". Using those will help you avoid some of the pitfalls of SQL injection issues (whether errors in the data being sent or someone deliberately trying to break things depending on what you are using the system for). They are also more performant if doing repeated inserts.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [26 November 2021 16:06 UTC](https://discourse.nodered.org/t/inserting-data-into-sqlite-db/53634/4 "2021-11-26T16:06:09Z")

</div>

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