0

I have a Nodejs script, where it's detail look like below :

1) it requests to API to get a list of city, it will get JSON array. using this array, I do looping using forEach.

2) at each iteration (2nd loop), I do request again to API to get details (about 100 rows) and insert it into mysql database.

my question, how to make the function inside the first loop (where to get the list of city) wait to complete before going to next item (city). I want to make a loop sequential with delay.

my source code :

const request = require('request');
var moment = require('moment');
var mysql = require('mysql');

var a = moment('2019-04-01');
var b = moment('2019-04-06');
const sleep = (waitTimeInMs) => new Promise(resolve => setTimeout(resolve, waitTimeInMs));

function timer(ms) {
 return new Promise(res => setTimeout(res, ms));
}

var connection = mysql.createConnection({
    host : 'localhost',
    user : 'user1',
    password : 'password',
    database : 'local'
});

async function getURL(id_city,dates) {

    var url = 'https://localhost/api/format/json/schedule/city/'+id_city+'/date/'+dates;    
    request(url, { json: true }, (err, res, body) => {
      if (err) { return console.log(err); }
      // console.log(body.status);
      var item1 = body.schedule.data.item1;
      var item2 = body.schedule.data.item2;

      connection.connect();
      connection.query('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")', function (error, results, fields) {
         if (error) throw error;
      });
      // connection.end();    
    });
}


async function getDate(id_city)
{
    var end;
    for (var m = moment(a); m.isBefore(b); m.add(1, 'days')) {
        getURL(id_city,m.format('YYYY-MM-DD'));
        await timer(1000); //making delay           
    }
}

async function main () {    
    var url = 'https://localhost/api/format/json/list_city';
    connection.connect();
    request(url, { json: true }, (err, res, body) => {
      if (err) { return console.log(err); }

          var list_city = body.city; //this is an array
          var counter = 0;
          list_city.forEach(function(city){
              getDate(city.id, function(){

              });//i need this to complete before go to next city

          });
    });//end request url
}

main();

my expectation (sequential) :

city1
insert item a done...
insert item b done...
city2
insert item a done...
insert item b done...
insert item c done...
city3
...
questionasker
  • 2,536
  • 12
  • 55
  • 119

2 Answers2

2

For both request and mysql you can use the Promise supported package namely: request-promise and mysql2. To guarantee sequential execution, you can then do:

const rp = require('request-promise');
const mysql = require('mysql2/promise');

// then in your getURL function
async function getURL(id_city,dates) {

  var url = 'https://localhost/api/format/json/schedule/city/'+id_city+'/date/'+dates;
  const body = await rp(url, { json: true })
  const item1 = body.schedule.data.item1;
  const item2 = body.schedule.data.item2;

  const connection = await mysql.createConnection({host:'localhost', user: 'root', database: 'test'});
  const [rows, fields] = await connection.execute('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")');
}

// One await in getDate should do
async function getDate(id_city) {
  var end;
  for (var m = moment(a); m.isBefore(b); m.add(1, 'days')) {
    await getURL(id_city,m.format('YYYY-MM-DD'));
  }
}

For handling error with async/await:

try {
  const body = await rp(url, { json: true })
} catch (e) {
  // handle erorr
  console.error(e);
  // or rethrow error: throw e
}

For efficiency you could use mysql connection pool like:

// myPool.js
const mysql = require('mysql2');

// create pool
const pool = mysql.createPool({
  host:'localhost',
  user: 'root',
  database: 'test',
  connectionLimit: 10,
  queueLimit: 0
});
// now get a Promise wrapped instance of that pool
const promisePool = pool.promise();

module.exports = () => promisePool; 

// Then in your getURL
const getPool = require('./myPool');
async function getURL(id_city,dates) {
  ...

  const pool = await getPool();
  const [rows, fields] = await pool.execute('INSERT INTO schedule (city,item1,item2) values ("'+id_city+'","'+task1+'", "'+task2+'")');
  ...

Also consider using prepared statement.

connection.execute('SELECT * FROM `table` WHERE `name` = ? AND `age` > ?', ['Morty', 14]);
1565986223
  • 6,420
  • 2
  • 20
  • 33
  • when using mysql pool, i got error `TypeError: pool.promise is not a function`, How to solve this ? – questionasker Apr 08 '19 at 07:56
  • Sorry my bad, should have mentioned in the answer the required import. Updated answer, for that to work you need to change import to `mysql = require('mysql2')` – 1565986223 Apr 08 '19 at 08:12
  • thanks, btw how about error `sqlMessage: 'Too many connections`, any idea to solve this ? btw, i have using mysql pool... – questionasker Apr 08 '19 at 10:09
  • check https://stackoverflow.com/questions/1202322/mysql-error-too-many-connections, maybe you specified too many connections in pool? If you've found the answer you sought, mark the specific answer accepted (: – 1565986223 Apr 08 '19 at 10:19
  • If you're using hosting environment, that may be too much depending on how much the environment allow for example. check this too https://stackoverflow.com/questions/14331032/mysql-error-1040-too-many-connection/34176072 If none solves the issue, you're better off posting new question with relevant information – 1565986223 Apr 08 '19 at 10:26
  • now i get this error too often : `UnhandledPromiseRejectionWarning: ReferenceError: id_city is not defined` i cant predict and find root cause of this error. any idea ? – questionasker Apr 08 '19 at 13:17
  • you've to `try/catch` wherever you use `await` in example shown above in `handling error` block – 1565986223 Apr 08 '19 at 14:30
  • @naga-elixir-jar You are mentioned "for efficiency" but you are re-creating pool at the beginngn of getURL function which defeats the whole purpose of pool – Andrey Sidorov Apr 24 '19 at 05:21
  • @AndreySidorov, you're right, I didn't notice that. Thanks, updating my answer – 1565986223 Apr 24 '19 at 05:41
1

Use a for loop instead of forEach, and on each iteration, await the call of getDate, so that one getDate call always finishes before it gets called again:

for (let i = 0; i < list_city.length; i++) {
  await getDate(city[i]);
  await timer(100); // this will put a delay of at least 100ms between each call
}

Make sure to make the containing function async for this to work.

Note that since getDate returns a Promise, it probably shouldn't accept a callback - either chain awaits or thens on to the end instead.

CertainPerformance
  • 356,069
  • 52
  • 309
  • 320