How to pass parameters to MySQL query callback function in Node.js

Question

I'm trying to figure out the correct way to pass custom data to a query call so it's available in the callback. I'm using MySQL library (all latest versions) in nodejs.

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

I can't find a way to 1) pass custom data/parameters to the call so it's available when the callback is called. So, what's the right thing to do?

I have the following pseudocode:

...
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 this?

Answer 1

To answer the initial question and illustrate with an example, wrap the callback function in an anonymous function that immediately creates a "snapshot" scope containing the incoming data.

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

For those like me who are just learning this concept, the last })(ix)); is the value of the outer var ix=1 which is passed to (function(ix){. If you would console.log("ix=" abc); is changed to console.log("ix=" abc);, then it can be renamed to (function(abc){.

fwiw (Thanks to Chris for the link, filling in the gaps to come up with the solution)

Answer 2

If you are using node-mysql, please follow the instructions in the documentation:

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

    }
);

Code for properly escaping strings is also provided in the documentation, but using an array in a query call will automatically escape it for you.

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