How to pass parameters to mysql query callback in

Question

I'm trying to figure out the correct way of passing custom data to a query call to be made available in the callback. I'm using MySQL library in nodejs (all latest versions).

I have a call to connection.query(sql, function(err, result) {...});

I couldn't find a way to 1) pass custom data/parameter to the call so that 2) it can be made available when the callback is invoked. So what is the proper way of doing so?

I have the following (pseudo-code):

...
for (ix in SomeJSONArray) {
    sql = "SELECT (1) FROM someTable WHERE someColumn = " + SomeJSONArray[ix].id;
    connection.query(sql, function (err, result) {
      ...
      var y = SomeJSONArray[ix].id;
    };
}

From the code above, I need to be able to pass the current value of "ix" used in the query to the callback itself.

How do I do that?

Answer 1

If you are using node-mysql, do it like the docs say:

connection.query(
    'SELECT * FROM table WHERE id=? LIMIT ?, 5',[ user_id, start ], 
    function (err, results) {

    }
);

The docs also have code for proper escaping of strings, but using the array in the query call automatically does the escaping for you.

https://github.com/felixge/node-mysql

Answer 2

To answer the initial question with a complete answer/example to illustrate, wrap the callback with an anonymous function which immediately creates a scope containing a "snapshot" if you will of the data passed in.

var ix=1;
connection.query('SELECT 1',
    (function(ix){
        return function(err, rows, fields) {
            console.log("ix="+ix);
            console.log(rows);
        };
    })(ix));

For those new to this concept as I was 20 minutes ago, the last })(ix)); is the outer var ix=1 value which is passed into (function(ix){. This could be renamed (function(abc){ if you changed the console.log("ix="+abc);

fwiw (Thanks Chris for the link which filled in the blanks to arrive at a solution)

Answer 3

While it is OK to pass variables or objects to a mysql query callback function using the tactic described earlier -- wrapping the callback function in an anonymous function -- I think it is largely unnecessary, and I'll explain why with an example:

// This actually works as expected!

function run_query (sql, y) {
    var y1 = 1;
    connection.query (sql, function (error, rows, fields) {

        if (! error)
        {
            var r = rows[0];

            console.log ("r = " + r[1]);
            console.log ("x = " + x);
            console.log ("y = " + y);
            console.log ("y1= " + y);
            console.log ("");
        }
        else
        {
            console.log ("error = " + error);
        }
    });
};

var x = 5;

console.log ("step 1: x = " + x);

run_query ("SELECT 1", x);

x = x + 1;

console.log ("step 2: x = " + x);

run_query ("SELECT 1", x);

x = x + 1;

console.log ("step 3: x = " + x);

Produces the following output:

step 1: x = 5
step 2: x = 6
step 3: x = 7
r = 1
x = 7
y = 5
y1= 5

r = 1
x = 7
y = 6
y1= 6

The fear is that the second call to run_query() will overwrite the variable y and/or y1 before the first call to run_query() has a chance to invoke its callback function. However, the variables in each instance of the called run_query() function are actually isolated from each other, saving the day.