Problems with Node.js and oracledb

Question

I'm having a problem with a node.js script at work (I'm pretty new to node in general, btw.). When I run through the forEach loop, it appears that it might be running asynchronously and therefor is not running problem. However, when I looked through the package documentation, I didn't see anything about get the sql execution to run synchronously. Thanks in advance for the help!

function getLastLoginAndProviderType(jsonObj, connection){
console.log(jsonObj);
jsonObj.forEach(obj => {
console.log("Processing: " + obj["email"]);
//obj["email"], obj["first"], obj["last"], obj["eid"], obj["role"] 

var sql = "select LAST_LOGGED_IN_TIME from EINV_OWNER.CONTACT where EMAIL_ADDRESS='" + obj['email'] + "'";

connection.execute(
  sql, {}, { maxRows: 15 },
  function(err, result) {
    if (err) {
      console.error("Query Error: " + err.message);
    } else {
      console.log("Number of rows returned: " + result.rows.length);

      obj["lastLoggedIn"] = "null";
      obj["supplierOrProvider"] = "null";
    }
  });
});

When the script runs, I would expect to see something like:

Processing: XXX@XXX.com
Number of rows returned: XX
Processing: XXX@XXX.com
Number of rows returned: XX
...
Processing: XXX@XXX.com
Number of rows returned: XX

However, what I end up getting is:

Processing: XXX@XXX.com
Processing: XXX@XXX.com
Processing: XXX@XXX.com
...
Processing: XXX@XXX.com
Query Error: NJS-003: invalid connection
Query Error: NJS-003: invalid connection
Query Error: NJS-003: invalid connection
...
Query Error: NJS-003: invalid connection

Any ideas? Thanks!

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

Quick rundown on the basic options:

Callbacks

use package like https://github.com/caolan/async to easily manage a "for loop" of callbacks. (there are other packages)

forEach does not wait for your callback to complete, which is why they are all running at the same time. You can also manage your own for-loop by using a callback with an iterator that stops when the max iterator is reached.

But you could also use :

require('util').promisify to convert a node.js styled callback function

myFn(foo,bar,<function(err,data){}>)

to a promise which then you can do the following:

Promises

Resolve promises one after another (i.e. in sequence)?

Async/Await

Or what you probably would want...

async function myFunction(){
  for(i=0;i<jsonObj.length;i++){
    try{
      let result = await connection.execute(sql, {}, { maxRows: 15 })
      console.log("Number of rows returned: " + result.rows.length);
      obj["lastLoggedIn"] = "null";
      obj["supplierOrProvider"] = "null";
    }catch(e){
      console.error("Query Error: " + err.message);
    }
  }
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 05 '18 at 15:09

Cody Ｇ

8,368
2
35
50

Thanks a ton for the quick response! I have one followup question, in your final example of Async/Await, how do I then use my callback function that I currently have in my execute call? – jhammond Oct 05 '18 at 15:18
I'm also getting an error after doing "npm install utils" where it says "TypeError: require(...).promisify is not a function" thanks a ton!!!! – jhammond Oct 05 '18 at 15:26
Sorry it is `require('util')` not `utils` – Cody Ｇ Oct 05 '18 at 15:41
Actually it looks like oracledb supports it out of the box – Cody Ｇ Oct 05 '18 at 15:49
Thank, mate. You are amazing! – jhammond Oct 05 '18 at 15:49
@jhammond since the answer resolved your issue, kindly *accept* it (answers take up valuable time for respondents). – desertnaut Jan 22 '19 at 00:18

Problems with Node.js and oracledb

1 Answers1

Callbacks

Promises

Async/Await