How to pass an array of values as a placeholder to IN(?) with execute()

[sumanthpfs]

Running this wouldn't throw any error, but neither does it delete any records.

async deleteJobItemsInJob(jobItemCodesArr, jobCodeStr, err) {
// jobCodeStr = "097325"
jobItemCodes = '"890", "234", "986"' // I'm converting an array (jobItemCodesArr) into a string to pass into mySql query.
await Promise.all([
            await db.execute('delete jItems from items jItems 
                                            inner join jobs jobs on jobs.id = jItems.job_id 
                                            where jobs.code = ? and jItems.code IN (?);', [jobCodeStr, jobItemCodes]),
            db.end()
        ]); 
}   

[sumanthpfs]

It works if I do a query() instead of execute() by passing an array, as shown below.
await db.query('delete jItems from items jItems inner join jobs jobs on jobs.id = jItems.job_id where jobs.code = ? and jItems.code IN (?);', [jobCodeStr, jobItemCodesArr])

I tried querying the prepared statement to mock an execute() but didn't work.

[sidorares]

.execute() does not do any client side query building and only sends parameters to a server side statement. It requires that number of parameters match exactly number of ? placeholders in a prepared statement

Your options are:

• Expand the list manually: await db.query('delete jItems from items jItems inner join jobs jobs on jobs.id = jItems.job_id where jobs.code = ? and jItems.code IN (' + jobItemCodesArr.map(() => '?').join(',') + ');', [jobCodeStr, jobItemCodesArr])
• partially expand on the client using.format()
• use stored procedure that takes one argument of type JSON array and passes it down to IN, and send an array as a single string value

[sumanthpfs]

Ok, can I map an array in an execute(), or I can't use an array at all with execute()?

[sidorares]

hard requirement is that you have same number of ? in the statement as the number of parameters you pass. So either you expand multiple ? in IN or serialize array as JSON string (one string parameter with array in it) and use mysql json operators ( I don't have much experience on mysql side of json manipulation in sql )