JavaScript nodejs mysql with queries in a loop

Question

I feel a bit dumbstruck right now. I am fairly new to nodejs and javaScript and can't figure this one out. I guess it is because of the async nature of queries to mysql...

I made an example that shows my problem. I just want to cycle over a number of sql queries and do stuff with the results. for the sake of the example I just print out stuff. I know that I could use a single sql query like this "SELECT id, name FROM player WHERE id IN (1,2,3,4,5)" but this is not possible in the real application I am trying to write.

this is the relevant part of my nodejs app.js

var mysql = require("mysql");
var mysqlPool = mysql.createPool(conf.mysqlArbData);

for (var i = 0; i<5; i++){

    mysqlPool.getConnection(function(err, connection) {

        var detailSql = "SELECT id, name FROM player " +
            "WHERE id = "+i;
        if (err){
            throw err;
        }
        connection.query(detailSql, function(err, detailRows, fields) {
            connection.end();
            console.log("detailSql="+detailSql);
            if (err){
                console.log("can't run query=" + detailSql +"\n Error="+err);
            }
            else{

                console.log(detailRows[0].id + " " +detailRows[0].name);

            }

        });
    });

};

Now the output:

web server listening on port 3000 in development mode
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla
detailSql=SELECT id, name FROM player WHERE id = 5
5 Jyvaskyla

My question is, why do I get only the result for database entry with id=5? What needs to be changed in order to receive each individual result in the callback?

robertklep · Accepted Answer · 2013-04-17 20:20:52Z

4

The problem is that getConnection is asynchronous and that Javascript doesn't have a block scope, which means that by the time the callback for getConnection is called, the i variable will point to the value of what it had last in the loop (which is 5).

You can use a trick to create a partial function (think of it as a function with the first argument already applied to it) for each turn of the loop, which will pass the current value of i as the first argument of the getConnection callback:

for (var i = 0; i<5; i++) {
  mysqlPool.getConnection(function(i, err, connection) {
    ...
  }.bind(mysqlPool, i));
};

FWIW, your code will open 5 connections (and perform 5 queries) to your database almost instantly (that's how asynchronous I/O works). That's probably not a big issue, but it's something worth realising if that 5 could get higher :)

Also, the for loop will generate [0, 1, 2, 3, 4], whereas in your example query, you write WHERE id IN (1, 2, 3, 4, 5).

edited Apr 17, 2013 at 20:20

answered Apr 17, 2013 at 20:06

robertklep

205k38 gold badges420 silver badges412 bronze badges

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

4 Comments

luksch Over a year ago

Thank you. I will try the solution you suggest asap. The hint about the concurrent queries is helpful, but I think the connection pool is initialized with a max of 10 concurrent connections. Any more would get queued I hoped. Finally, the index starting at 0 is no problem, merely an error on my part, i should have written [0,1,2,3,4]

GameDevGuru Over a year ago

what about binding multiple paramaters?

robertklep Over a year ago

@GameDevGuru function(arg1, arg2, err, connection) { ... }.bind(mysqlPool, i, j)

GameDevGuru Over a year ago

@robertklep I went with an array approach. Looks and feels cleaner to me

shaoyihe · Accepted Answer · 2016-03-24 10:54:57Z

1

For node, you can use let statement. It limit i scope in for loop.

for (let i = 0; i<5; i++) {
  mysqlPool.getConnection(function(err, connection) {
    console.log(i);
  });
};

answered Mar 24, 2016 at 10:54

shaoyihe

1,0871 gold badge13 silver badges29 bronze badges

Collectives™ on Stack Overflow

JavaScript nodejs mysql with queries in a loop

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related