MySQL problems in Node.js - fetch all query

Question

I have a problem in Node.js i have build my own modul, to handle a kind of stuff, in this modul i have a function getList() where i can get a inputs to my config.

my source code is this:

var config = {
    'limit' : 0
};

var dict = [];


exports.getList = function(db, conf) {
    db.query("SELECT id, title FROM animal a " + (config.limit > 0 ? " LIMIT " + config.limit : "" ), function(err, rows, fields) {
        if(err) {
            console.log(err);
        }

        for(var key in rows) {
                var row = rows[key];
        //
                dict.push({
                    'title' : 'Buster',
                    'animal' : 'Dog',
                    'age' : '12',
                    'photo' : 'https://placeholdit.imgix.net/~text?txtsize=24&txt=260×200&w=260&h=200'
                });
    });

    console.log(dict);
    console.log(config);

    return dict;
}

The problem is when i start my application up and first time i call this script the dict its empty, second time its have 3 rows from my database, and every time i reload the pages its add 3 more to it.

i have trying to find out how i handle this issue whiteout a aware hope somebody can help me here, thanks a lot.

Sean · Accepted Answer · 2015-11-15 00:31:03Z

2

you should pass the callback into the db.query, or return the promise instead of the dist, as db.query is an async method, you can not call an async method in sync way. In old days, we would use a callback, for example

exports.getList = function(db, conf, callback) {
   db.query('', function (err, result) {
     if(err) return callback(err);
     callback(null, result);
   });
}

getList(db,conf, function(err, result) {
  console.log(result);
});

now I would like to recommand use promise.

exports.getList = function(db, conf) {
    return new Promise(function(resolve, reject) {
      db.query("SELECT id, title FROM animal a " + (conf.limit > 0 ? " LIMIT " + conf.limit : "" ), function(err, rows, fields) {
          if(err) {
              reject(err);
              return;
          }
          var dict = [];
          for(var key in rows) {
                var row = rows[key];
        //
                dict.push({
                    'title' : 'Buster',
                    'animal' : 'Dog',
                    'age' : '12',
                    'photo' : 'https://placeholdit.imgix.net/~text?txtsize=24&txt=260×200&w=260&h=200'
                });
        });
        resolve(dict);
    });

}

getList(db,config).then(function(result) {
  console.log(result);
}).catch(function(e) {
  console.log(e);
});

and you can also call two query in parallal for example

Promise.all([getList1(db,config), getList2(db,config)]).then(function(result) {
  console.log('result of getList1', result[0]);
  console.log('result of getList2', result[1]);
});

and with promise, you can use es6 generator with co, to call async method in a sync manner

var co = require('co');

co(function* () {
  try {
     var result = yield getList(db, conf);
     // here is your result
  } catch(e) {
     console.log(e);
  }

});

edited Nov 15, 2015 at 0:31

answered Nov 14, 2015 at 17:43

Sean

3,0722 gold badges23 silver badges35 bronze badges

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

4 Comments

ParisNakitaKejser Over a year ago

Hmmm, now i'm really conferees.. i'm coming from PHP or other none async languages, can you explain me whats happen here and what promise dos?

ParisNakitaKejser Over a year ago

i have get the promise to working now, the only thing i need to fix now, its first time i hit the pages (after my application) is restartet i get a error becomes its say nothing from db is found, if i reload all working, so something not working first time i hit it, have i done something wrong? just following you info.

Sean Over a year ago

@ParisNakitaKejser I may miss type the conf to config, I can't see any problem in getList, you may need to show me more code, the problem may in your controller, when you pass your db to the method getList

ParisNakitaKejser Over a year ago

yeah i see, its a other problem now, every thing run smooth its look a like, only get a problem to parse first time out, i will work with that, thanks a lot you typing help me to make the work done, i use promise after i have reading more about it, thanks a lot.

Nir Levy · Accepted Answer · 2015-11-14 17:38:13Z

2

You are initialiIng dict once, outside of the function. This call happens only once when you require this module.

Later on in the function you push the new rows to it- so it keeps adding ip, theres nothing that clears the array between calls.

You should move the initialization of dict to inside the function

EDIT: after seeing your comments, there is one more (bigger) issue here: you are ignoring the asynchronous nature of node.js.

Node works asynchronously, which means it will run db.query, your both console.log, and the return statement one after the other without waiting for the prior to end. this means that the return statement can be executed before db.query and it's callback (will probably will, as connecting db is slower operation than the two console logs).

so when you are not initializing dict - you will get every time the results that were inserted to it on the previous call of the function. once you are clearing it, you are getting it empty, as it has not filled yet.

The solution should be not to return the result like that, but build your function to except a callback, and return in this callback the result (this way you can control when this callback is called, and run it only once you have the results)

This is how I'd do it:

exports.getList = function(db, conf, callback) {
    var dict = [];
    db.query("SELECT id, title FROM animal a " + (config.limit > 0 ? " LIMIT " + config.limit : "" ), function(err, rows, fields) {
        if(err) {
            console.log(err);
        }

        for(var key in rows) {
                var row = rows[key];
        //
                dict.push({
                    'title' : 'Buster',
                    'animal' : 'Dog',
                    'age' : '12',
                    'photo' : 'https://placeholdit.imgix.net/~text?txtsize=24&txt=260×200&w=260&h=200'
                });
        console.log(dict);
        console.log(config);
        callback(dict); // this will return the results to the calling function's callback
    });

}

edited Nov 14, 2015 at 17:38

answered Nov 14, 2015 at 17:14

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

6 Comments

ParisNakitaKejser Over a year ago

Yes, i now it, but when i move dict inside the function i can't push to it anymore.

Nir Levy Over a year ago

Why not? In any case you can add dict = []; before your for loop

ParisNakitaKejser Over a year ago

yes but its dosen work, when i do it its return nothing,

Nir Levy Over a year ago

@ParisNakitaKejser - take a look at the edit I did on the answer. there's more to it than what I initially wrote

ParisNakitaKejser Over a year ago

i have looking on your update, i can see its maybe becomes its async yes :/ i never hit this issue before, so now i need to learn about it.

|

Collectives™ on Stack Overflow

MySQL problems in Node.js - fetch all query

2 Answers 2

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related