28

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?

Improve this question
1
  • I think the title of your question is wrong, I think you mean "call" and not "callback", and that's why some answers are not appropriate. Commented Jan 12, 2024 at 14:05

4 Answers 4

Reset to default
53

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

And as for using 'ix' in the callback, put the query in it's own function, or a self calling anon. function. (I'm still too new to edit my own answer...)
Thanks Chris, but my issue is not on the encoding of data inside the SQL string itself, but on passing additional data that I want returned via the callback, not part of the result set. As you can see in my example, the ix value will be whichever value is current in the outer loop and not the one I used in array index.
That's why I added my first comment. Wrap the whole query in a self calling anon. function so 'ix' doesn't change on you when you get to the callback.
Yes indeed, if I can keep the value of IX within the scope of the callback, then yes, that would help instead of having to figure out how to pass the data through the call and back the callback. Just to be clear on your suggestion, how would you put a wrapper anonymous function around it all?
11

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)

Improve this answer

1 Comment

This is the solution to the OP question
1

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.

Improve this answer

1 Comment

Your printout in your query has console.log("y1= " + y) rather than y1, im assuming it would always print out 1 for y1?
0

MySQL con.query has overloaded function. Inside of callback you use global variable or any variable that is passed into your function parameter. For example:

Example 1: it takes sql string and callback function

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers;
con.query(sql, function (err, result) {
  if (err) throw err;
  console.log(adr);
});

Example 2: it takes sql string, parameter and callback function

var adr = 'Mountain 21';
var sql = 'SELECT * FROM customers WHERE address = ?';
con.query(sql, [adr], function (err, result) {
  if (err) throw err;
  console.log(adr);
});
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.