Node.js executing mysql query after receiving message from mqtt broker-CodePudding

I have a node.js file that subscribes to a topic and upon receiving a published message scans a local mysql db for the most recent entry in a variable named "command". Command values will trigger various responses, but I have left this portion out since my issue is before this.

My mysql query appears to be giving me errors. I am trying to look for the most recent entry of the command column and assign the value to a var command. I thought this code would do the trick:

var sql = 'SELECT command FROM motoron2 ORDER BY id DESC LIMIT 1';
        con.query(sql, function (err, result) {
            if (err) throw err;
        });
        console.log(result);
        var command = result[1];
        console.log(command);

But I am getting the following response which seems to indicate an error in the mysql query:

[email protected] [bin]# node motorlistener.js
Connected to MYSQL!
Connected to Broker!
{"pulse":1}
1
/home/user/etc/domain/bin/motorlistener.js:62
    console.log(result);
                ^

ReferenceError: result is not defined
    at MqttClient.<anonymous> (/home/user/etc/domain/bin/motorlistener.js:62:17)
    at MqttClient.emit (events.js:314:20)
    at MqttClient._handlePublish (/home/user/node_modules/mqtt/lib/client.js:1277:12)
    at MqttClient._handlePacket (/home/user/node_modules/mqtt/lib/client.js:410:12)
    at work (/home/user/node_modules/mqtt/lib/client.js:321:12)
    at Writable.writable._write (/home/user/node_modules/mqtt/lib/client.js:335:5)
    at doWrite (/home/user/node_modules/readable-stream/lib/_stream_writable.js:409:139)
    at writeOrBuffer (/home/user/node_modules/readable-stream/lib/_stream_writable.js:398:5)
    at Writable.write (/home/user/node_modules/readable-stream/lib/_stream_writable.js:307:11)
    at TLSSocket.ondata (_stream_readable.js:718:22)

The full code is below, but does anyone know what is causing this error?

////////////////////////////////////////////////////////////////////////////////
//setup
var mqtt    = require('mqtt'); //for client use
const fs = require('fs');
var caFile = fs.readFileSync("/home/user/etc/domain/bin/ca.crt");
var topic = "heartbeat";
var mysql      = require('mysql');

var con = mysql.createConnection({
  host     : 'localhost',
  user     : 'myuser',
  password : 'mypass',
  database : 'mydb'
});

var options={
    port:8883,
    clientId:"yo",
    username:"myuser2",
    password:"mypassw",
    protocol: 'mqtts',
    clean:true,
    rejectUnauthorized: false,
    retain:false, 
    ca:caFile
};


var client  = mqtt.connect("http://dns.org",options);

//mqtt connection dialog
client.on("connect",function(){
    console.log("Connected to Broker!");
    client.subscribe(topic, {qos:1});
});

//mqtt connection handle errors
client.on("error",function(error){
    console.log("Broker Connection Error");
    process.exit(1);
});

//database connection
con.connect(function(err) {
        if (err) throw err;
        console.log("Connected to MYSQL!");
});

//handle incoming messages from broker
client.on('message',function(topic, message, packet){
    var raw = "" message;
    console.log(raw);
    var obj = JSON.parse(raw);
    var pulse = obj.pulse; 
    console.log(pulse);
    
    var sql = 'SELECT command FROM motoron2 ORDER BY id DESC LIMIT 1';
    con.query(sql, function (err, result) {
        if (err) throw err;
    });
    console.log(result);
    var command = result[1];
    console.log(command);
    
    if (command == 1) {
        console.log("command=1");
    }
    else {
        console.log("command not equal to 0");
    }

});

CodePudding user response：

I am getting the following response which seems to indicate an error in the mysql query

That's not an error in your MySQL query. It's a null reference error because you're trying to use result outside the callback.

Changing your code to this will work:

var sql = 'SELECT command FROM motoron2 ORDER BY id DESC LIMIT 1';
con.query(sql, function (err, result) {
   if (err) { 
       throw err; 
   }
   // access result inside the callback
   console.log(result);
   var command = result[0];
   console.log(command);
});

Depending on your environment you may be able to re-write your code using promises and async/await to reduce the nested scopes.

To do so, you'd need to turn your callback into a promise and then you can await it, like so:

let sql = 'SELECT command FROM motoron2 ORDER BY id DESC LIMIT 1';

// 1 -- we turn the query into a promise 
const queryPromise = new Promise((resolve, reject) => {
  con.query(sql, function (queryError, queryResult) {
    if (queryError) { 
       reject(queryError); 
    }
    resolve(queryResult);
  });
});

try {
  // 2 -- we can now await the promise; note the await
  let result = await queryPromise;

  // 3 -- now we can use the result as if it executed synchronously
  console.log(result);
  let command = result[0];
  console.log(command);
} catch(err) {
  // we can catch query errors and handle them here
}

Putting it all together, you should be able to change the on message event handler to an async function in order to take advantage of the async/await pattern as shown above:

client.on('message', async function(topic, message, packet) {
    
    /* .. you can use await here .. */
    
});