Inserting data into sqlite db

[Tanglefoot]

I've been trying to follow the advice given in this post but still having problems
I've created a table using the inject node

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)

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

[Colin]

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

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

[TotallyInformation]

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.