Node js mysql multi-statement query placeholders

Question

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

**One query at a time**. Each one can fail. Each one needs error handling. Don't `multi`. It just creates a whole world of hurt. — tadman, Dec 23 '22 at 21:03
That's also a very unusual query to run, it's like you've gone out of your way to introduce a race condition. This should be `UPDATE accounts SET balance=balance-? WHERE uid=?`. — tadman, Dec 23 '22 at 21:04
Tip: When keeping balances, don't actually keep a "balance" column. The account's balance should be trivially computable as `SUM(amount) FROM transactions WHERE account_id=?`, and you insert records that add to or remove from this balance sequentially. This way your "balance" column never falls out of sync because it does not exist. Make a ledger. It will give you a complete break-down of how any balance came to be, which can be *super important* when dealing with money. — tadman, Dec 23 '22 at 21:06
To add more info to what @tadman mentioned (I know this isn't what you asked, but you might find it useful in the long run) - when deciding on the numerical type for columns used for storing them, go with DECIMAL(x,y), where `x` is the precision, and `y` the number of decimals. BIGINT could be a good choice as well. Read more [here](https://stackoverflow.com/questions/13030368/best-data-type-to-store-money-values-in-mysql) — FiddlingAway, Dec 23 '22 at 22:02

score 0 · Accepted Answer · answered Dec 23 '22 at 21:28

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.

Node js mysql multi-statement query placeholders

1 Answers1