CODEWITHSUNDEEP
mysqlnode.jscallback
Pattle
Pattle

Reputation: 6016

Node.js returning result from MySQL query

I have the following function that gets a hexcode from the database

function getColour(username, roomCount)
{
    connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)
    {
        if (err) throw err;
        return result[0].hexcode;
    });
}

My problem is that I am returning the result in the callback function but the getColour function doesn't return anything. I want the getColour function to return the value of result[0].hexcode.

At the moment when I called getColour it doesn't return anything

I've tried doing something like

function getColour(username, roomCount)
{
    var colour = '';
    connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)
    {
        if (err) throw err;
        colour = result[0].hexcode;
    });
    return colour;
}

but of course the SELECT query has finished by the time return the value in colour

Upvotes: 32

Views: 88707

Answers (2)

Goran Stoyanov
Goran Stoyanov

Reputation: 2311

If you want to use promises to avoid the so-called "callback hell" there are various approaches.

Here's an example using native promises and the standard MySQL package.

const mysql = require("mysql");

//left here for testing purposes, although there is only one colour in DB
const connection = mysql.createConnection({
  host: "remotemysql.com",
  user: "aKlLAqAfXH",
  password: "PZKuFVGRQD",
  database: "aKlLAqAfXH"
});

(async () => {
  connection.connect();
  const result = await getColour("username", 2);
  console.log(result);
  connection.end();
})();

function getColour(username, roomCount) {
  return new Promise((resolve, reject) => {
    connection.query(
      "SELECT hexcode FROM colours WHERE precedence = ?",
      [roomCount],
      (err, result) => {
        return err ? reject(err) : resolve(result[0].hexcode);
      }
    );
  });
}

In async functions, you are able to use the await expression which will pause the function execution until a Promise is resolved or rejected. This way the getColour function will return a promise with the MySQL query which will pause the main function execution until the result is returned or a query error is thrown.

A similar but maybe more flexible approach might be using a promise wrapper package of the MySQL library or even a promise-based ORM.

Upvotes: 20

Mithun Satheesh
Mithun Satheesh

Reputation: 27845

You have to do the processing on the results from the db query on a callback only. Just like.

function getColour(username, roomCount, callback)
{
    connection.query('SELECT hexcode FROM colours WHERE precedence = ?', [roomCount], function(err, result)
    {
        if (err) 
            callback(err,null);
        else
            callback(null,result[0].hexcode);

    });

}

//call Fn for db query with callback
getColour("yourname",4, function(err,data){
        if (err) {
            // error handling code goes here
            console.log("ERROR : ",err);            
        } else {            
            // code to execute on data retrieval
            console.log("result from db is : ",data);   
        }    

});

Upvotes: 63

Related Questions