Javascript Nested async not returning result

Question

For some reason, the second nested async DB function never gets the results returned. First db call gets some planners from MSSQL needed for a 2nd db call to an OracleDB. I can see the Oracle query works as i get the results in the db function displayed.

 app.get('/api/Scrap/:ParmLocation/:ParmDepartment', (req, res) => {
    var ParmLocation = req.params.ParmLocation;
    var ParmDepartment = req.params.ParmDepartment;
    console.log('/api/Scrap called for ' + ParmLocation + ' Department ' + ParmDepartment + ' @ ' + Date());

    // First get planners for this department
    var query = `SELECT Planners FROM DeptPlanners where Department = '${ParmDepartment}'`
    var PromisePlanners = db.DBQueryBHBrowser(query);
    var Planners = '';
    PromisePlanners.then(function(result) {
        result.recordset.forEach(function(row){
            Planners = Object.values(Object.values(row))[0];
        });
        console.log('/api/Scrap Planners completed @ ' + Date());
        console.log('Planners: ' + Planners);
        var query = `SELECT TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW') AS Week, SUM(ILTRQT / 1000 * -1) AS TOTQTY, SUM(ILPAID / 100 * -1) AS TOTAMT
        FROM PRODDTA.F4111
        JOIN PRODDTA.F4102 ON ILITM = IBITM AND IBMCU = ILMCU AND IBANPL NOT IN (50030002,50030039)
        JOIN PRODDTA.F4101 ON IMLITM = ILLITM
        JOIN PRODCTL.F5500001 On ILTRDJ = DCGRDT
        LEFT JOIN PRODCTL.F0005 ON ILRCD= LTRIM(F0005.Drky)
        And DRSY='42' AND DRRT='RC'
        WHERE ILMCU = '       50010' AND ILDCT IN('ID', 'IS')
        AND ILTRDJ BETWEEN  123001 AND 123365
        AND IBANPL IN (${Planners})
        AND ILRCD NOT IN('517','509') AND ILUSER <> 'HIPPK'
        GROUP BY TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW')
        ORDER BY Week`

        var PromiseScrap = db.DBQueryJDE(query);
        PromiseScrap.then(function(result) {
            //if (debugMode == true) {
                console.log(`/api/Scrap ${ParmLocation} ${ParmDepartment}  completed @ ${Date()}`);
                console.log(`JDE Data:` + result );      // This is always 'Undefined'
            //}
            res.send(result);
        }).catch(function () {
            console.log("Promise Rejected for api/Scrap");
        });
    }).catch(function () {
        console.log("Promise Rejected for /api/Scrap get Planners");
    });
})

The second call DBQueryJDE has

    async function DBQueryJDE(sqlquery)  {
  try {
    //if (debugMode) {
      console.log('Oracle SQL:\n' + sqlquery);
    //}
    var oracledb = require('oracledb');
    var connectionProperties = {
      user: process.env.DBAAS_USER_NAME || "SomeUserID",
      password: process.env.DBAAS_USER_PASSWORD || "SomePassword",
      connectString: "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = db92IF.bherp.local)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME= DB92IF.bherp.local)))"};
    oracledb.getConnection(connectionProperties, function (err, connection) {
      if (err) {
        console.error(err.message);
        response.status(500).send("Error connecting to DB");
        return;
      }
      connection.execute(sqlquery,
        function (err, result) {
          if (err) {
            console.error(err.message);
            response.status(500).send("Error getting data from JDE DB");
            doRelease(connection);
            return;
          }
          console.log("RESULTSET:" + JSON.stringify(result));
          doRelease(connection);
          //console.log('returned from close');
          return result;
        }
      );
    });
    //return result;
  } catch (error) {
      console.log('DBErrorJDE: ' + err.message + '\n Query:' + sqlquery);
      doRelease(connection);
      return;
  }
};

The results in console are

node app Dashboard server listening on port 3000

/api/Scrap called for BHNW Department Manifolds @ Wed May 03 2023 09:19:54 GMT-0400 (Eastern Daylight Time)

/api/Scrap Planners completed @ Wed May 03 2023 09:19:54 GMT-0400 (Eastern Daylight Time)

Planners: 50030015,50030029

Oracle SQL: SELECT TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW') AS Week, SUM(ILTRQT / 1000 * -1) AS TOTQTY, SUM(ILPAID / 100 * -1) AS TOTAMT FROM PRODDTA.F4111 JOIN PRODDTA.F4102 ON ILITM = IBITM AND IBMCU = ILMCU AND IBANPL NOT IN (50030002,50030039) JOIN PRODDTA.F4101 ON IMLITM = ILLITM JOIN PRODCTL.F5500001 On ILTRDJ = DCGRDT LEFT JOIN PRODCTL.F0005 ON ILRCD= LTRIM(F0005.Drky) And DRSY='42' AND DRRT='RC' WHERE ILMCU = ' 50010' AND ILDCT IN('ID', 'IS') AND ILTRDJ BETWEEN 123001 AND 123365 AND IBANPL IN (50030015,50030029) AND ILRCD NOT IN('517','509') AND ILUSER <> 'HIPPK' GROUP BY TO_CHAR(TO_DATE(SUBSTR(ILTRDJ,-5), 'YYDDD'),'WW') ORDER BY Week

/api/Scrap BHNW Manifolds completed @ Wed May 03 2023 09:19:54 GMT-0400 (Eastern Daylight Time)

JDE Data:undefined

RESULTSET:{"metaData":[{"name":"WEEK"},{"name":"TOTQTY"},{"name":"TOTAMT"}],"rows":[["01",24,1573.83],["02",35,2096.06],["03",26,903.28],["04",16,729.95],["05",25,873.44],["06",61,2015.3500000000001],["07",28,2293.91],["08",23,1223.8500000000001],["09",24,800.9],["10",41,1366.04],["11",17,1712.95],["12",30,1588.1200000000001],["13",45,1189.51],["14",33,1104.3],["15",71,2378.3],["16",38,1430.72],["17",35,2559.43],["18",22,725.89]]}

jeeves · Answer 1 · 2023-05-03T16:47:09.197

Your problem is you are using a mix of callback AND async/promise in your nested function.

You are invoking your nested function DBQueryJDE as if it returns a Promise:

const result = await DBQueryJDE(query)

But the way the function is implemented - it will always return undefined because it is using callbacks.

The reason is that you are returning the result from the callback - which is not getting returned to the caller.

i.e. This is what you are doing

function DBQueryJDE(queryString) {
     // when you get a connection - you then execute a callback function which is passed the connection
     oracledb.getConnection(args, (connection) => {
          // when connection.execute is executed - the following callback is executed with the results
          connection.execute(queryString, (results) => {
              // now you have the results - you need to either execute another callback or do nothing as this cannot be returned via the existing pattern
              return results; // this does not get returned to the caller.
          }
     })

}

As of version 5.5 of oracledb according to their examples - they support promises so you can modify as follows and this return will be returned to the caller.

For a better understanding I suggest you read on the differences between callbacks and async/promises.

const connection = await oracledb.getConnection(connectionProperties); 
return connection.execute(sqlquery);

(You can use the oracledb async or you could also roll your own and wrap the DBQueryJDE functions internal callbacks in a Promise as follows)

async function DBQueryJDE(sqlquery)  {
      try {
        ...
        return new Promise((resolve, reject) => { 
            oracledb.getConnection(connectionProperties, function (err, connection) {
            if (err) return reject(err);
          
            connection.execute(sqlquery, function (err, result) {
               if (err) return reject(err)
                 return resolve(result);
            });
        });
      } catch (error) {
        ...
      }
    };

Also - if you are getting multiple rows back from your first query, you are going to overwrite your Planners variable and lose values. Looks like you should be appending to the variable - not re-assigning it.

var Planners = '';
...
result.recordset.forEach(function(row){
    Planners = Object.values(Object.values(row))[0];
});

Javascript Nested async not returning result

1 Answers1