Return data from database.query() using sailsjs

Question

I am trying to build an api using sailsjs that calls stored procedures of a MYSQL database. I decided to decouple the query by adding it to a service so that others functions might be able to call them and use them. Below is what I came up with.

under /api/controller/MySqlController

getAllUsers: function (req, res) {
    MySqlSpService.spGetAllUsers(function(err, result){
        if(err) return res.serverError(err);
        return res.ok(result[1]);
    });
},

under /api/services/MYSQLService

 var MySqlSpService= {

     spGetAllUsers: function(callback) {
       Database.query('call userDb.sp_get_all_users(@message, @mystatus)', function (err, results) {
        callback(err, results);
    }); // end query

}
 module.exports = MySqlSpService;

When I hit the api the data is displayed exactly how I thought it would be. But the problem is that when I try to call the spGetAllUsers service and assign to a variable, I get a undefined value.

Like this:

  var users = MySqlSpService.spGetAllUsers(function(err, result){
        if(err) return res.serverError(err);
        return result[1];
    });

I believe the problem is with the callbacks but I am not sure how to retrieve the data from the query. I have searched for an answer but I can't seem to find the right search terms that match my problem. Any help would be greatly appreciated Thanks in advance.

Alexis N-o · Accepted Answer · 2015-08-06 18:44:23Z

2

Indeed, your problem is about callback and asynchronous code. The MySqlSpService.spGetAllUsers() function does not return anything, there is no return statement in this method. But it executes a callback function where you have the opportunity to execute code that depends on the SQL query result.

You have to write your code like this because the database query is executed asynchronously.

console.log('This is executed first');

MySqlSpService.spGetAllUsers(function(err, result){
    console.log('This is executed when the result of the database query is received');
    if(err) return res.serverError(err);
    users = result[1];
    /**
     * Here you can access to the "users" data
     * and execute code depending on it
     */
});

console.log('This is executed in second and it is not possible to know the result of the query yet');

Tools like async can help you to organize your asynchronous code. By default, async is available globally in sails.js.

edited Aug 6, 2015 at 18:44

answered Aug 6, 2015 at 13:15

Alexis N-o

4,01328 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user990951 Over a year ago

Thanks this helped, I am using promises for the async!

Collectives™ on Stack Overflow

Return data from database.query() using sailsjs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related