Nested Query in Nodejs Mysql

Question

I want to count a line in table that has FOO table. The following code has a bug which show only the last db_name.

RESULT IS LOOK LIKE THIS:
db_0099,0
db_0099,5
db_0099,10
db_0099,3

Could you please suggest me how to fix the nodejs code?

var mysql  = require('mysql');

var sql1 = "SELECT table_schema as db_name from information_schema.tables WHERE table_name = 'FOO' ";
var sql2 = "SELECT COUNT(*) as solution FROM {0}.FOO";

var connection = mysql.createConnection({
  host     : '$$$$$$$',
  user     : '$$$$$$$',
  password : '$$$$$$$',
});

connection.connect(function(err){
  console.log('connected as id ' + connection.threadId);
});

connection.query(sql1, function(err, result) {
  if (err) throw err;

  for (var i = 0, len = result.length; i < len; i++) {
    var db_name = result[i].db_name;
    console.log(db_name);
    connection.query(sql2.replace("{0}",db_name), function(err, result) {
      if (err) throw err;
      console.log(db_name+','+result[0].solution); //Here db_name is showed only the last one.
    });
  };

  connection.end();
});

Holger Will · Accepted Answer · 2017-01-13 09:25:41Z

4

i advice a two step solution to this problem:

use connection pooling

var pool = mysql.createPool({ host : 'xxxxx', user : 'xxxxx', password : 'xxxxx', connectionLimit : 100 });

pool can do auto connection, so don't connect to your db, just

pool.query(sql,function(err,res){})

this way you use one connection for each query, which will be closed automatically after using it.

use async await for asyncronous sequential queries.

for that create a getResult function which returns a promise

function getResult(sql){
  return new Promise(function(resolve,reject){
    pool.query(sql, function(err, result){
      if(err){
        reject(err)
      }else{
        resolve(result)
      }
    })
  })
}

then you can await each query in the loop

pool.query(sql1, async function(err, result) {
  if (err) throw err;
  for (var i = 0; i < result.length; i++) {
    var db_name = result[i].db_name;
      console.log(db_name);
      var sql = sql2.replace("{0}",db_name)
      var res = await getResult(sql)
      console.log(db_name+','+res[0].solution); //Here db_name is showed only the last one.
  };
  pool.end()
});

P.S.: async await is a feature of the upcomming node 8.0.0 release in april. for node 7.x you will have to start your script with a commandline switch

node --harmony-async-await yourscript.js

answered Jan 13, 2017 at 9:25

Holger Will

7,5761 gold badge35 silver badges41 bronze badges

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

2 Comments

Ar Aui Over a year ago

Thank you so much!

AsGoodAsItGets Over a year ago

2022 and node v.14.18.3 and it still works great! 👍🏻

Mech45 · Accepted Answer · 2017-01-13 08:59:02Z

0

Have you verify the content of result ? console.log(result);

If it's okay try this :

solutions = results.map(result => {
    let dbName = result.db_name;
    let queryResult;
    connection.query(sql2.replace("{0}", dbName), function(err, result) {
      if (err) {
          throw err;
      } else {
        queryResult = `${db_name}, ${result[0].solution}`
        console.log(queryResult);
      }
    });
    return queryResult;  
})
console.log(solutions);

However, try to use a ORM or a sql parser for your query !

Try this one :)

https://hiddentao.com/squel/

answered Jan 13, 2017 at 8:59

Mech45

3491 silver badge9 bronze badges

1 Comment

Ar Aui Over a year ago

Thank you for your answer. I got the answer from this script but I still got the wrong database name.

Collectives™ on Stack Overflow

Nested Query in Nodejs Mysql

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related