binaryBigInt binaryBigInt - 7 months ago 11
SQL Question

Node.js - SQL function doesn't return value

i want to get a data from a MySQL database and i use Node.js with SQL for it, this is my server code:

var app = require('express')();
var http = require('http').Server(app);
var io = require('socket.io')(http);
var mysql = require('mysql');

var connection = mysql.createConnection({
host : '127.0.0.1',
user : 'root',
password : '',
database : 'temp'
});

function getData(res){
var tempVal = 1377;
connection.connect();
connection.query('SELECT * FROM tempvalues ORDER BY datetime DESC LIMIT 1', function(err, rows){
console.log(rows);
tempVal = rows;
});
connection.end();
return tempVal;
}

app.get('/', function(req, res){
res.sendfile('index.html');
});

io.on('connection', function(socket){
socket.on('clientSent', function(data){
if(data == "GET")
socket.emit("serverSent", getData());
})
})

http.listen(3000, function(){
console.log('listening on *:3000');
});


If i go to
localhost:3000
i only get
1377
as value but not the actual value from the database, even though the console prints the correct values. Why is that?

Answer

There's some things bad in your code. First. Think that queries to database, in most of cases are asynchronous.

Your code explained :

function getData(res){
    var tempVal = 1377; // Create tempVal with 1377 as value initially.
    connection.connect(); // Connect to the database.
    // Run the query
    connection.query('SELECT * FROM tempvalues ORDER BY datetime DESC LIMIT 1', function(err, rows){
        // Here you are inside the callback executed asynchronously.
        console.log(rows);
        // You modify the top-level variable.
        tempVal = rows;
    });
    connection.end(); // End connection
    return tempVal; // You return 1377 since the callback is not yet finish and the value of tempVal not changed
}

One simple way to fight with async code are the callbacks. Let your getData function look like :

function getData(callback){
    var tempVal = 1377;
    connection.connect();
    connection.query('SELECT * FROM tempvalues ORDER BY datetime DESC LIMIT 1', function(err, rows){
        console.log(rows);
        return callback(err, rows);
    });
    connection.end();
}

Then use the function as follows:

io.on('connection', function(socket){
    socket.on('clientSent', function(data){
        if(data == "GET")
            getData(function(error, result){
              if(!error) socket.emit("serverSent", result);
            });
    })
});