CODEWITHSUNDEEP
mysqlnode.js
Phil_of_odd
Phil_of_odd

Reputation: 13

How to return a JSON object from a mySQL query in node.js

I'm very new to coding and node.js in particular so this is probably a dumb question - but all the examples I read for mySQL database queries end with just logging the query results to the console... what I want to do (or at least what I think I want to do) for my web application is return the query result as the product of a function so that I can allow my .ejs file to make use of the JSON object.

The situation as the moment is no matter what I try my .ejs file receives "Hello World" as the product of getDriver(). I want it to receive 'rows' (ie. the JSON object that my SQL query returns with)

I can see the JSON result in the console & it looks perfect.. I just can't seem to make it 'exist' outside of the function :(

var express = require('express');
var router = express.Router();
var mysql = require('mysql');
/* GET about page. */
router.get('/', function(req, res, next) {
    res.render('SQLtest', { title: 'SQL test',
                        result: getDriver() // I want this to be my JSON object
    });
});

module.exports = router;


function getDriver() {
    var result = "Hello World"; // my .ejs see's this
        var connection = mysql.createConnection(
            {
                host: 'localhost',
                user: 'root',
                password: 'password',
                database: 'my database',
                port: 3306
            }
        );

        connection.query("SELECT * FROM my_table",
            function (err, rows) {
                if (err) {
                console.log(err);
                }

                console.log(rows); // query result looks fine as JSON object
                result = rows; // now change "Hello World" to the JSON object
            }
        );
    return result; // however this still = "Hello World"  :(
}

Upvotes: 1

Views: 17899

Answers (1)

ishmaelMakitla
ishmaelMakitla

Reputation: 3812

The database query is an asynchronous call - this means that the return result statement may execute BEFORE the query results are returned and the value of result is updated via result = rows.

What you need to do is to introduce a callback function as a parameter in your getDriver function. Your code will then look like this (I moved the database connection code out of the getDriver function):

//You should move this out of the function - you want to set it once
 var connection = mysql.createConnection(
     { host: 'localhost',
       user: 'root',
       password: 'password',
       database: 'my database',
       port: 3306
     });

function getDriver(callback) {    
        connection.query("SELECT * FROM my_table",
            function (err, rows) {
                //here we return the results of the query
                callback(err, rows); 
            }
        );    
}

This is how you now call the getDriver function:

router.get('/', function(req, res, next) {   
    //now you can call the get-driver, passing a callback function
    getDriver(function (err, driverResult){ 
       //you might want to do something is err is not null...      
       res.render('SQLtest', { 'title': 'SQL test',
                        'result': driverResult});

    });
});

Give it a try and let me know if it works. I hope this helps.

Upvotes: 7

Related Questions