Updating multiple rows with node-mysql, NodeJS and Q

Question

Updating multiple rows with node-mysql, NodeJS and Q

I am using node-mysql, node-js, and Q promises.

I have successfully updated, deleted, and inserted single rows using the above. As well as inserted multiple rows in a single statement in my test case scenario.

However, I need to update multiple rows with different values (batch mode) either in a single query or in a for loop.

The information on how to use prepared statements in mysql2 (supposed to improve on node-mysql) is very sparse and there are no examples, although that should be the natural choice, together with promises to compensate for node-js asynchronous nature.

In addition, I have successfully used defered.makeNodeResolver() in various test scenarios.

I am trying to update multiple rows in a single query with a where clause and changing conditions.

It is working when I update a single row. However, when I try to update multiple rows with a single query, the records aren't updated.

I am ready to switch to using a for loop to perform multiple updates and then aggregate the result and send it back from the server to the client, which would have been my second preferred choice. And I don't see why it shouldn't be done that way if there isn't too much of a performance hit. But I haven't found any examples for doing it that way.

var values = [
    { users: "tom", id: 101 },
    { users: "george", id: 102 }
    ];

    // var params = [values.users, values.id ];

    var sql = 'UPDATE tabletest SET users = ? WHERE id = ?;';


    connection.query(sql, [{users: values[0].users}, {id: values[0].id }], defered.makeNodeResolver());

The code shown above isn't actually updating multiple rows. I imagine there's an error in my syntax.

But anyway, what is the best approach to go about this in this particular scenario? Prepared statements, repeated queries in a for loop, or stored procedures?

node.js
angularjs
promise
node-mysql

Answer 1

I don't think you can (at least easily/efficiently) update multiple rows in the way you are trying. You would basically have to loop over your values and execute an UPDATE for each object.

Answer 2

this piece of code was taken from vcl.js for node.js, it is written in typescript and provides a multiple update,delete,inseer statment's in a single transaction.

export class SQLStatment {
    sql: string;
    params: Object
}

var dbError: string;
var execCount: number = 0;
function DBexecuteBatchMYSQLSingle(connection: any, SQLStatmentArray: Array<SQLStatment>, index: number, callback: () => void) {
    execCount++;
    connection.query(SQLStatmentArray[index].sql, SQLStatmentArray[index].params, function (err, rows, fields) {
        if (err) dbError = err;
        if (index + 1 == SQLStatmentArray.length) callback();
        else {
            if (!dbError) {
                DBexecuteBatchMYSQLSingle(connection, SQLStatmentArray, index + 1, function () {
                    if (index == 0) callback();
                });
            }
        }
    });
}

function DBBatchUpdateMYSQL(SQLStatmentArray: Array<SQLStatment>, callback?: (err) => void) {
    var mysql = require('mysql');
    var connection = mysql.createConnection({
        host: "host",user: "user",database: "db",
        port: 1022, password: "pass"});
    dbError = null;
    execCount = 0;
    connection.beginTransaction(function (err) {
        if (err && callback) callback("Database error:" + err);
        else {
            DBexecuteBatchMYSQLSingle(connection, SQLStatmentArray, 0, () => {
                if (dbError) {
                    connection.rollback(function () {
                        if (callback) callback("Database error:" + dbError);
                    });
                } else {
                    connection.commit(function (err) {
                        if (callback) callback(null);
                    })
                }
            });
        }
    });
}

Answer 3

You can do it this way:

var values = [
  { users: "tom", id: 101 },
  { users: "george", id: 102 }
];
var queries = '';

values.forEach(function (item) {
  queries += mysql.format("UPDATE tabletest SET users = ? WHERE id = ?; ", item);
});

connection.query(queries, defered.makeNodeResolver());

To use multiple statements feature you have to enable it for your connection:

var connection = mysql.createConnection({
  ...
  multipleStatements: true,
});