Back

Technologies:

javascriptjavascript
avatar
Tolerim
25 days ago

How come the nested async in JavaScript doesn't produce a result?

The issue seems to be that the results are not being returned for the second nested async DB function. The first DB call retrieves planners from MSSQL that are required for a second call to an OracleDB. The Oracle query appears to be successful, but results are not being displayed in the DB function. The app.get method is called with parameters ParmLocation and ParmDepartment. The first DBQueryBHBrowser() function retrieves planners for the department using a SQL query. The result is looped through and its value is stored in a variable Planners. This value is used in the second DB query, DBQueryJDE(), to retrieve data from an Oracle DB. This function uses oracledb module to connect to the DB and execute the provided SQL query. However, the resultset is always undefined.
Answers(2)
avatar
Vogi
25 days ago
Verified Answer
The issue you are facing could be due to the fact that the resultset is not being parsed correctly. You can try using the outformat option in the execute() function of oracledb module to return the response as an array of objects instead of using JSON.parse(). Another possible reason for this issue could be that there is an error getting the connection and your catch block calls conn.close() without having a valid conn. This gives the error you see. You can try tidying up your code by having your conn.close() just once in a finally() block. Finally, you can also try using the new getRow() and getRows() methods that can be called repeatedly to scroll through the query results. The original node-oracledb behavior of returning all rows at once remains the default. To return a resultSet, use the new execute() option { resultSet: true }. I hope this helps!

Sources

JSON.parse is failing for resultset from Oracle DB in Node.jsoracle - Node.js oracledb module -- TypeError: Cannot read property ...node-oracledb 0.7.0 now supports Result Sets and REF CURSORS
avatar
Tolerim
25 days ago
The issue with the code seems to be that the DBQueryJDE function uses a callback and returns nothing, but the promise expects a return value. Therefore, the PromiseScrap never receives any data. To fix this issue, you can wrap the connection.execute call in a promise that you resolve or reject based on whether the query was successful or not. Here's how you could modify the DBQueryJDE function: 
try {
    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)))"
    };
    var connection = await oracledb.getConnection(connectionProperties);
    var result = await new Promise((resolve, reject) => {
      connection.execute(sqlquery,
        function (err, result) {
          if (err) {
            console.error(err.message);
            reject("Error getting data from JDE DB");
          } else {
            console.log("RESULTSET:" + JSON.stringify(result));
            resolve(result);
          }
          doRelease(connection);
        }
      );
    });
    return result;
  } catch (error) {
      console.log('DBErrorJDE: ' + error.message + '\nQuery:' + sqlquery);
      doRelease(connection);
      throw error;
  }
};
With this modified function, the PromiseScrap should receive the results from the Oracle query as expected.
;