If I do the following in a Javascript cell…
await db.query(`
SELECT * FROM device_table
WHERE device_id IN ('${device_ids.join("','")}')
`)
… I get the results I’m expecting. But if I use the same string literal in a Database cell I get an error:
Error: bind message supplies 1 parameters, but prepared statement "" requires 0
I’ve tried various forms of building up the set of device_ids but nothing seems to be working for me. If I simply copy/paste the result of '${device_ids.join("','")}' as a string into the Database cell SQL, it works.
How should I be able to do this in a Database cell?
Database cells don’t do string interpolation, they do parameter substitution, so you can’t use them to build up queries like that.
A database cell with contents like
SELECT * FROM device_table WHERE brand = ${selectedBrand}
is converted to something like this (I’m not sure the exact function that is called)
runQuery(`SELECT * FROM device_table WHERE brand = $1`, [selectedBrand])
Here, $1 is only treated as a value, never as a more complicated syntax construction. This is to protect against SQL injection attacks.
To work with arrays in SQL, you need to pass the array itself to the database cell. The way to do this depends on the database engine you are using. For example, this should work in Postgres:
SELECT * FROM device_table
WHERE device_id = ANY(${device_ids})
2 Likes