MySQL, Node.js Sequential actions - How can I do that?

Question

I've the following code:

function query1() {

var defered = Q.defer();

console.log("In query1");

var connection = mysql.createConnection({
    host: '........',
    user: 'm...c....a.....i',
    password: '......Z....9...K',
    database: '.....ol'
});

connection.connect(function(err) {
    if (!err) {
        console.log("Database is connected ...");
    } else {
        console.log("Error connecting database ...");
    }
});

sql = '' +
    'select c.ID as CENA_ID, ' +
    '      c.I_KEY as CENA_NUMERO, ' +
    '      c.NM_CENA as CENA_NOME, ' +
    '      b.DS_MAC as MAC_BOX, ' +
    '      v.DS_CLIENTID as ALEXA_ID, ' +
    '    v.FK_ID_GRUPO as GRUPO_ID ' +
    '  from TB_DISPOSITIVOS_VOZ v ' +
    '      inner join TB_GRUPOS g ' +
    '      on g.ID = v.FK_ID_GRUPO ' +
    '      inner join TB_CENAS c ' +
    '      on g.ID = c.FK_ID_GRUPO ' +
    '      inner join TB_CENTRAIS b ' +
    '      on g.ID = b.FK_ID_GRUPO ' +
    'where v.DS_CLIENTID = "' + userId + '" ' +
    'and lower(c.NM_CENA) like "%' + sceneName.toLowerCase() + '%"';


console.log("Created query");

try{

    connection.query(sql, function(erro, rows, fields) {

        if (!erro) {

            console.log("Executed query verifying the userId");

            contador = 0;

            if (rows.length > 0) {

                cena_id = rows[0].CENA_ID;
                cena_numero = rows[0].CENA_NUMERO;
                cena_nome = rows[0].CENA_NOME;
                alexa_id = rows[0].ALEXA_ID;
                grupo_id = rows[0].GRUPO_ID;
                mac_box = rows[0].MAC_BOX;

                contador = contador + 1;

            }

            console.log("contador: " + contador);

        } else {
            console.log("Error - getting the Alexa register in database" + erro);
            context.fail("Error - getting the Alexa register in database" + erro);
        }

    });


}catch (ex){
    console.log("exception: " + ex);
}

}

And this code as well:

Q.all([query1()]).then(function(results) {

        console.log("Q.all log function");

        if (contador > 0) {

            console.log("contador > 0");

            var client = mqtt.connect('mqtt://.............com');
            console.log("connected to MQTT broker");

            var buffer = [26,
                0,0,0,0,555,645,0,0,0,0,0,
                0,5555,2,Math.floor((Math.random() * 200) + 1),
                0,0,0,333,13,4,0,1,0,
                cena_numero
            ];

            console.log("Created buffer");

            client.on('connect', function() {

                client.publish('n/c/' + mac_box + '/app', buffer);

                console.log("sent MQTT");

            });

            speechOutput = "Command " + sceneName + " executed successfully";
            repromptText = "";
            console.log("Process executed successfully")

        } else {

            console.log("contador <= 0");

            speechOutput = "This command was not found!";
            repromptText = "";

        }

    }, function (reason) {

        console.log("reason: " + reason);

    });

How can I do for the second code execute only if the first query1() executed correctly? Because in the function query1() i've a MySQL Query, and I only can continue with the process after the result of this query.

Anyone can help me?

Thanks a lot!

Answer 1

I solved my problem with asyncpackage like this:

var async = require('async');

async.series([
            function(callback) {

                //action 1...

            },
            function(callback){

                //action 2...
            }
        ], function(err) { 
            if (err) {
                speechOutput = "Scene not found!";
                repromptText = "Please try again.";
            }
            console.log("Before speechOutput");
            callback(sessionAttributes,
                    buildSpeechletResponse(cardTitle, speechOutput, repromptText, shouldEndSession));   
        });

Answer 2

You're missing some key concepts regarding callbacks and asynchronous behavior in Node.js. You're using the "Q" library (btw I'd recommend trying bluebird instead) to handle promises, but your "query1" function does not return a promise. That's why query1 executes but your "Q.all log function" will execute before query1 is finished.

You can structure your code like this instead (I'll give an example with bluebird since I'm more familiar with it):

var Promise = require('bluebird');

var _connection;

function query1() {
  return new Promise(resolve, reject) {
    //open your connection
    connection.open(function (err, connection) {
      if (err) return reject(err);

      _connection = connection;
      //do your query
      _connection.query(sql, [params], function (err, data) {
        if (err) return reject(err);
        else resolve(data);
      });
    });
  });
}

function query2(data) {
  return new Promise(resolve, reject) {
    //do your query, using data passed in from query1
    _connection.query(sql, [params], function (err, data) {
      if (err) return reject(err);
      else resolve(data);
    });
  });
}


query1
  .then(function (data) { query2(data); })
  .catch(function (err) {
    console.log('error:', err);
  });

Also, just FYI, concatenating SQL string like this is a no-no that will open you up to a SQL injection attack:

like "%' + sceneName.toLowerCase() + '%"

Instead, use like "%?%" and call your SQL with connection.query(sql, [sceneName], function(err, data) {}). Hope this helps.