shadow00 shadow00 - 6 months ago 21
SQL Question

Promise not working properly

I am trying to do a nested query with MySql, put the result inside a variable and send over http, but the program always run

console.log("test 2:"+rpsData);
before the query finish. I already tried this, but still getting the same problem.

const express = require('express')
const app = express()

const mysql = require('mysql');
const Connection = require('mysql/lib/Connection');
const Promise = require('bluebird');

Promise.promisifyAll([
Connection
]);

const connection = mysql.createConnection({
host : 'localhost',
user : 'root',
password : 'root123',
database : 'mygallery'
});
app.get('/posts', function(request, response) {
var rpsData;
connection.connectAsync()
.then(function() {
connection.query('SELECT * FROM post WHERE approved = 1', function(err, rows, fields) {
if (err) throw err;
rpsData = rows;
for (var i in rows) {
connection.query('SELECT * FROM image WHERE postCode = ?', [rpsData[i].postCode], function(err, rows, fields) {
if (err) throw err;
rpsData[i].image = rows;
console.log("test 1:"+rpsData);
});
}
});
})
.then(function() {
response.send(rpsData);
console.log("test 2:"+rpsData);
})
.catch(function(error) {
console.error('Connection error.', error);
});
});

Answer

Not familiar with the API.

Has connection.query been converted to a promise-returning function?

If so, you have to return it.

If not, start your first .then() block with

return new Promise(function(resolve,reject) {
    ...
});

or

return Promise.resolve().then(function(){
    ...
});

If you don't return a promise, it won't know that it has to wait for your code to finish executing and will move forward with the next .then() statement on the outside promise (from connection.connectAsync);

You need to apply the same treatment to the inner query.

Sample code:

app.get('/posts', function(request, response) {
  connection.connectAsync()
  .then(function() {
    return new Promise(function(resolve, reject) {
      connection.query('SELECT * FROM post WHERE approved = 1', function(err, rows, fields) {
        if (err) reject(err);
        resolve(rows.reduce(function(accumulator, current) {
          return accumulator.then(function(rpsData){
            return new Promise(function(resolve, reject){
              connection.query('SELECT * FROM image WHERE postCode = ?', [current.postCode], function(err, rows, fields) {
                if (err) reject(err);
                current.image = rows;
                console.log("test 1:"+rpsData);
                resolve(rpsData);
              });
            });
          });
        }, Promise.resolve(rows)));
      });
    });
  })
  .then(function(rpsData) {
    response.send(rpsData);
    console.log("test 2:"+rpsData);
  })
  .catch(function(error) {
    console.error('Connection error.', error);
  });
});

I'm queueing the internal promises using the technique I describe here

Comments