Node.js mysql query rows

Question

app.get('/api/db/:tableName', function(req, res){
    res.setHeader('content-type', 'application/json');
    dbPool.getConnection(function(objErr, objConn){
        if(objErr){
            sendError(res, 503, 'error', 'connection', objErr); //503 - Service Unavailable
        }else{
            var tableName = req.params.tableName;
            console.log(tableName);
            var tRows, bRows;
            var today = moment().format('YYYY-MM-DD 00:00:00');
            var current = moment().format('YYYY-MM-DD HH:mm:ss');
            console.log(typeof today);
            console.log(typeof current);
            if(tableName == 'dashboard'){
                objConn.query("SELECT SUM(taranan) as tTara, SUM(bulunan) as tBul, SUM(resmedilen) as tRes FROM "+tableName, function(Err, Rows, Fields){
                    if(Err){
                        sendError(res, 500, 'error', 'query', Err);
                    }else{
                        tRows = Rows;
                        console.log(bRows);
                    }//else
                });
                objConn.query("SELECT SUM(taranan) as bTara, SUM(bulunan) as bBul, SUM(resmedilen) as bRes FROM "+tableName+" WHERE change_on > '"+today+"'", function(Err, Rows, Fields){
                    if(Err){
                        sendError(res, 500, 'error', 'query', Err);
                    }else{
                        bRows = Rows;
                        console.log(bRows);
                    }//else
                });
                console.log(tRows);
                res.send({
                    results : 'success',
                    err : '',
                    err_type : '',
                    Trows : tRows,
                    Brows : bRows
                });
            }else{
                /* EMPTY ZONE */
            }//else
            objConn.release();
        }//else
    });
});

console.logs work perfect. It get the variables in bRows and tRows but last console.log(tRows) is empty. Because when this line running, mysql have not get datas from db yet. How can i fixed this code?

I know its about asynchronous calling.

You'll have to call the tRows after the callback has been executed. — majidarif
– majidarif, Commented Oct 15, 2014 at 10:42
You have two separate async queries running and you need for them both to finish before you send your response. Option 1 is to run your second query inside the callback of the first query, then run your send inside the callback of the second query. Option 2 would be to use promises and have a function that waits until your get responses from both queries before executing. — Dave Briand
– Dave Briand, Commented Oct 15, 2014 at 10:46

majidarif · Accepted Answer · 2014-10-15 11:05:25Z

1

What you said is right, the variables aren't populated because the callback have not been executed (no results returned) yet. A quick fix of your code is:

if(tableName == 'dashboard'){
    objConn.query("SELECT SUM(taranan) as tTara, SUM(bulunan) as tBul, SUM(resmedilen) as tRes FROM "+tableName, function(Err, Rows, Fields){
        if(Err){
            sendError(res, 500, 'error', 'query', Err);
        }else{
            tRows = Rows;
            console.log(tRows);

            objConn.query("SELECT SUM(taranan) as bTara, SUM(bulunan) as bBul, SUM(resmedilen) as bRes FROM "+tableName+" WHERE change_on > '"+today+"'", function(Err, Rows, Fields){
                if(Err){
                    sendError(res, 500, 'error', 'query', Err);
                }else{
                    bRows = Rows;
                    console.log(bRows);

                    console.log(tRows);
                    res.send({
                        results : 'success',
                        err : '',
                        err_type : '',
                        Trows : tRows,
                        Brows : bRows
                    });

                }//else
            });

        }//else
    });

}else{

This way both tRows and bRows are already populated when you use them.

Explanation

What the code I gave does:

You get the tRows
You wait for the result of tRows, then you get the bRows
You wait for the result of bRows, then you send the reply back with bRows and tRows

What was happening in your original code:

You try to get the tRows
You try to get get the bRows
You send the reply (without waiting for the results of bRows and tRows)

Promises

You might notice that the nested callbacks looks dirty. A better solution is by using promises. Like bluebird.

A few reminders

Instead of doing a lot of this:

if(Err){
  sendError(res, 500, 'error', 'query', Err);
}else{
  tRows = Rows;
  console.log(bRows);
}//else

You can try returning early:

if (Err) return sendError(res, 500, 'error', 'query', Err);
tRows = Rows;
console.log(bRows);

Good luck

edited Oct 15, 2014 at 11:05

answered Oct 15, 2014 at 10:45

majidarif

20.4k18 gold badges97 silver badges137 bronze badges

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

6 Comments

Dave Briand Over a year ago

you also might want to fix that first console.log to console.log(tRows)

majidarif Over a year ago

@DaveBriand thanks, but this shouldn't really matter as I'm just telling him the idea. But great catch :)

user2846400 Over a year ago

yes thank you, it works and i understand how to work. But i know my codes is not good, can you share me a documentation to getting better this code?

majidarif Over a year ago

@Touregsys if you want to avoid writing more library code by using Promises yourself. Try checking the package called knex. It returns a thenable.

majidarif Over a year ago

@Touregsys updated my answer. There are a few things you could change but the code is okay. no need to worry about the optimization.

|

Collectives™ on Stack Overflow

Node.js mysql query rows

1 Answer 1

Explanation

Promises

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Explanation

Promises

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related