CODEWITHSUNDEEP
mysqlnode.js
Ar Aui
Ar Aui

Reputation: 392

Nested Query in Nodejs Mysql

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();
});

Upvotes: 0

Views: 4081

Answers (2)

Holger Will
Holger Will

Reputation: 7526

i advice a two step solution to this problem:

  1. 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.

  1. 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

Upvotes: 4

Mech45
Mech45

Reputation: 339

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/

Upvotes: 0

Related Questions