SQLite parameterized syntax for 'WHERE IN ("a", "b", "c")'?

General
1

SELECT * FROM table
WHERE field IN ($values)

Does anyone know how a SQLite prepared statement is expecting one to format the $values parameter here? I've tried setting msg.params.$values to these strings:

"a", "b"
'a', 'b'
a, b

As well as this array:
["a", "b"]

But none of them work... I'm guessing that something like:

WHERE IN ($valueA, $valueB)

might work. But if the number of items needed in the list varies, that isn't very helpful.

Is there not a way to do this with a prepared statement? Is creating the full SQL string and passing it to the node as msg.topic the only way to do this?

Thanks!

2

Did you look at the Info tab on the sqlite node?

When using Normal or Prepared the query must be entered in the node config.

Pass in the parameters as an object in msg.params for Prepared. Ex:
msg.params = {
    $id:1,
    $name:"John Doe"
}
Parameter object names must match parameters set up in the Prepared Statement. If you get the error SQLITE_RANGE: bind or column index out of range be sure to include $ on the parameter object key.
The sql query for the example above could be: insert into user_table (user_id, user) VALUES ($id, $name);
1 Like
3

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