Node js mysql multi-statement query placeholders-CodePudding

I' m on end of my project. I only must write final query to database. I wanna use placeholders to do this, but i don't know how to do this right in multi-statement query :(.

This is my query:

var query3 = 'UPDATE accounts SET balance = ((SELECT balance FROM accounts WHERE uid = ?) - ?) WHERE uid = ?;';
query3 =  'UPDATE accounts SET balance = ((SELECT balance FROM accounts WHERE uid = ?)   ?) WHERE uid = ?;';
query3 =  'INSERT INTO transactions SET ?';

I tried something like this:

db.multi.query(query3, [result1[0].id, amount, result1[0].id], [destination, amount, destination], {name: name, source_id: result1[0].id, destination_id: destination, amount: amount, type: 1}, (err3, result3) =>
{
    if (err3) throw err3;
    res.redirect('/account/transfer_success');
})

and this:

db.multi.query(query3, [result1[0].id, amount, result1[0].id, destination, amount, destination, {name: name, source_id: result1[0].id, destination_id: destination, amount: amount, type: 1}], (err3, result3) =>
{
    if (err3) throw err3;
    res.redirect('/account/transfer_success');
})

Error in console: TypeError [ERR_INVALID_ARG_TYPE]: The first argument must be of type string or an instance of Buffer, ArrayBuffer, or Array or an Array-like Object. Received type number (NaN)

Please show me how to do this right. P.S. I use package mysql2. transactions table: id name source_id destination_id amount type accounts table: id name email password balance status

CodePudding user response：

In MySQL, you can't execute a prepared query that includes multiple SQL statements.

https://dev.mysql.com/doc/refman/en/prepare.html:

The text must represent a single statement, not multiple statements.

https://dev.mysql.com/doc/c-api/en/c-api-multiple-queries.html

The multiple statement and result capabilities can be used only with mysql_real_query() or mysql_query(). They cannot be used with the prepared statement interface. Prepared statement handlers are defined to work only with strings that contain a single statement.

There is no practical benefit to using multi-query even if you are not using parameterized queries. Just execute the queries one at a time in separate calls. Then you can use prepared queries with parameters.