Mysql: function that returns mysql resultset / mysql result set in variable

[subodh2508]

Hello,

i want to write a function for fetching records from db and store the result set into a variable that function will return.
But i am not able to access the result set outside function
what is the issue??

Sample code:

var result = [];
var  getInformationFromDB = function() {

mysql.connection.query('SELECT * FROM role', function(err, res, fields)
{
    if (err)  throw err;
     if(res.length){
    for(var i = 0; i<res.length; i++ ){     
                    result.push(res[i]);
        }
     }
   console.log("Inside npm");
   console.log(result);
   return result;
});
);

   console.log("Outside npm");
   console.log(result);
 console.log("Call Function");
   console.log(getInformationFromDB());

Output:
Outside npm
[]
Call Function
[]
Inside npm
[{Row1}, {Row2}]

[dougwilson]

Hi @subodh2508, welcome to Node.js! In Node.js, async things like network tasks are done using callback, which means that standard statements like return do not function. This is a standard part of Node.js and you may want to look up some good introductions on Node.js callback to get you started :)

As an immediate example for you, you would do the following to access your results:

var result = [];
var  getInformationFromDB = function(callback) {

mysql.connection.query('SELECT * FROM role', function(err, res, fields)
{
    if (err)  return callback(err);
     if(res.length){
    for(var i = 0; i<res.length; i++ ){     
                    result.push(res[i]);
        }
     }
   callback(null, result);
});
);


 console.log("Call Function");
getInformationFromDB(function (err, result) {
  if (err) console.log("Database error!");
  else console.log(result);
});

I hope that helps get you started on Node.js!

[Nagadinesh]

Can I copy that result to a variable and access it outside the callback function?

[alex-griffiths]

@dougwilson I've been playing around with the code you supplied above, and I'm coming into a bit of an issue.

let packages = [];
    let getInfoFromDB = function(callback){
        connection.query("SELECT * FROM PAV6_Package WHERE PackageLabelPrinted = 0", function(error, result, fields){
            if(error) return callback(error);
            $.each(result, function(tracking_index, package){
                let module_id = package.PackageModuleID;
                let invoice_number = package.PackageInvoiceNumber;
                let module_query = `SELECT * FROM PAV6_Module WHERE ModuleID = ${module_id} AND ModuleInvoiceNumber = ${invoice_number}`;
                connection.query(module_query, function(error, module_result, fields){
                    if(error) throw error;
                    $.each(module_result, function(index, module_data){
                        // packages[tracking_index] = {"module_data": module_data, "package_data": package};
                        packages.push([result[tracking_index], module_data]);

                    })
                })
            })
            callback(null, packages);
        })
    }
    getInfoFromDB(function(err, result){
        if(err) throw err;
        console.log(result[0]); // returns undefined
        console.log(result); // returns the array
    })
    console.log(packages); // returns the array

When I log result[0] I'm getting undefined. However, my other callbacks return the arrays in their entirety.

Could you help me understand why that is happening?

[WillieDavonSmalls]

@Xadren: Did you ever figure your problem out? I am experiencing the same issue.

[alex-griffiths]

I'm not sure, but I think the result that I was having was just down to the nature of async operations, and how the callbacks work.

Ultimately, I moved away from this approach, and instead just opted to keep any code reliant on the result of a query within the query callback. So what I had above would have likely turned into something like:

connection.query("SELECT * FROM PAV6_Package WHERE PackageLabelPrinted = 0;", function(error, results, fields){
    if(error) throw error;
    // Any operations on the data retrieved from the query here.
})

One thing that I definitely try to steer clear of is having queries being run within loops, because it leads to a lot of connections being made to the database, and as I found, caused me to hit my max_connections limit. So I've been trying to consolidate as many queries as I can into a single request, and if I have to nest calls to the database, I make sure they're not within loops.

Hope that helps @WillieDavonSmalls

[WillieDavonSmalls]

@Xadren 👍 That is what I actually ended up doing as well.