@mysql/xdevapi won't allow array parameters for WHERE field IN (?) query

Question

I'm using the @mysql/xdevapi npm package with a local installation of mysql-8.0.15-winx64. I'm trying to execute a SQL string with parameters. The query is looking for records where the id is IN an array.

const sql = "SELECT * FROM table WHERE id IN (?)";
const params = [[1,2]];
const result = await session.sql(sql).bind(params).execute();

and I'm getting the following error

Error: Invalid data, expecting scalar
    at SqlResultHandler.BaseHandler.(anonymous function) (C:\...\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:110:19)
    at Array.entry (C:\...\@mysql\xdevapi\lib\Protocol\ResponseHandlers\BaseHandler.js:87:29)
    at WorkQueue.process (C:\...\@mysql\xdevapi\lib\WorkQueue.js:75:19)
    at Client.handleServerMessage (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:198:21)
    at Client.handleNetworkFragment (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:242:14)
    at TLSSocket.stream.on.data (C:\...\@mysql\xdevapi\lib\Protocol\Client.js:89:36)
    at emitOne (events.js:116:13)
    at TLSSocket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11) info: [Object] } ],

It works if I just pass the sql SELECT * FROM table WHERE id IN (1,2), returning two records.

If I pass a single parameter with a comma separated string, so const params = ["1,2"] then it returns just the first record.

I can't find anything in the documentation to support this, and I can't find a way to work around it.

ruiquelhas · Accepted Answer · 2024-12-11 09:56:31Z

1

You need to provide a placeholder for each value in the array. In your case, the SQL statement should eventually be:

const sql = "SELECT * FROM table WHERE id IN (?, ?)";
const params = [1, 2];
const result = await session.sql(sql).bind(params).execute();

You can find more details here.

Update

The initial answer assumes the number of parameters is fixed. With a parameter list of variable length, one can do as follows:

const params = [/* variable length */]
const placeholders = Array(params.length).fill("?")
const sql = `SELECT * FROM table WHERE id IN (${placeholders.join(", ")})`
const result = await session.sql(sql).bind(params).execute()

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js

edited Dec 11, 2024 at 9:56

answered Dec 3, 2020 at 13:40

ruiquelhas

2,0251 gold badge18 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jen Over a year ago

Oh great, that's what I'd done as a workaround anyway. Thanks!

Populus Dec 9, 2024 at 20:34

What if there's an indeterminant number of values?

ruiquelhas Dec 11, 2024 at 9:57

@Populus I've updated the answer with an example that accommodates that case.

Collectives™ on Stack Overflow

@mysql/xdevapi won't allow array parameters for WHERE field IN (?) query

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related