Node-oracledb: Issues node-oracleDB - Async/Await and node-oracledb

[VGamezz19]

Dear All,

I’m using de Package node-oracledb.
I’ev used so much this package and It’s so powerful and I would like to said you that I’m so grateful to use it.

But… I’was try to introduce in my project an async function, and I had some problems about it.

I was put the same as you (in example of the documentation) and the response (resolve) never arrive in my client.

In my Node Console appears this logs :

Server listening at port 5550
Connected to database
Send Results ... From MINA-99-MINA
Resolved undefined
End Observable
Close Connection
undefined

It’s like the Observable never execute, but the try{} isn’t aware of it

!!!(Isn’t problem from client, because, I try it with Postman, and happentds the same)!!!

This is the function:

async function getVesselWork(voyageId) {
  try {
    let res = await VesselIdWork(voyageId);
    console.log(res)
    return(res);
  } catch (err) {
    console.error(err);
  }
}

function VesselIdWork(voyageId) {
  let _id = voyageId.params.voyageId

  return new Promise(async function(resolve, reject) {
    let conn = await oracledb.getConnection(connection);

    try {
      console.log('Connected to database');

      let result = await conn.execute(
        `select * from
      (SELECT NVL(ga.gang_id,99) GANG_ID, NVL(cs.movement_type,'Loading') MOVEMENT_TYPE,st.bay_no BAY_NO,NVL(cs.bay_deck_hold,'D') BAY_DECK_HOLD,
      cs.total_move TOTAL_MOVE,cs.pending_move PENDING_MOVE
      FROM
      (SELECT DISTINCT voyage_id VOYAGE_ID,(CASE WHEN MOD(TO_NUMBER(bay_no),2)=0 THEN TRIM(TO_CHAR(TO_NUMBER(bay_no)-1,'00')) ELSE bay_no END) BAY_NO from STOWAGE_CELL
      order by (CASE WHEN MOD(TO_NUMBER(bay_no),2)=0 THEN TRIM(TO_CHAR(TO_NUMBER(bay_no)-1,'00')) ELSE bay_no END)) st,
      (select distinct voyage_id VOYAGE_ID,bay_no BAY_NO,gang_id GANG_ID from gang_assign) ga,
      (SELECT voyage_id VOYAGE_ID,(CASE WHEN shipment_type='O' and cntr_status in ('RE','RM') THEN 'Restow Loading'
      WHEN shipment_type='I' and cntr_status in ('RE','RM') THEN 'Restow Discharge'
      WHEN shipment_type='O' THEN 'Loading'
      WHEN shipment_type='I' THEN 'Discharge'
      ELSE 'Undefined' END) MOVEMENT_TYPE,
      (CASE WHEN MOD(TO_NUMBER(bay_no),2)=0 THEN TRIM(TO_CHAR(TO_NUMBER(bay_no)-1,'00')) ELSE bay_no END) BAY_NO,
      bay_deck_hold BAY_DECK_HOLD,count(cntr_id) TOTAL_MOVE,
      (count(cntr_id)-sum(CASE WHEN host_shipment_status='C' THEN 1 ELSE 0 END)) PENDING_MOVE from cntr_shipment
      group by voyage_id,(CASE WHEN shipment_type='O' and cntr_status in ('RE','RM') THEN 'Restow Loading'
      WHEN shipment_type='I' AND cntr_status in ('RE','RM') THEN 'Restow Discharge'
      WHEN shipment_type='O' THEN 'Loading'
      WHEN shipment_type='I' THEN 'Discharge'
      ELSE 'Undefined' END),(CASE WHEN MOD(TO_NUMBER(bay_no),2)=0 THEN TRIM(TO_CHAR(TO_NUMBER(bay_no)-1,'00')) ELSE bay_no END),bay_deck_hold) cs
      WHERE (st.voyage_id=ga.voyage_id(+) and st.bay_no=ga.bay_no(+))
      AND (st.voyage_id=cs.voyage_id(+) and st.bay_no=cs.bay_no(+))
      AND st.voyage_id='${_id}'
      order by NVL(ga.gang_id,99), NVL(cs.movement_type,'Loading'),st.bay_no,NVL(cs.bay_deck_hold,'D'))
      where TOTAL_MOVE is not null
      and PENDING_MOVE is not null`
      );

      console.log('Send Results ... From', _id)

      resolve(result.row);
      console.log("Resolved", result.row)
    } catch (err) {
      console.log('Error occurred', err);

      reject(err);
    } finally {
      console.log("End Observable")
      // If conn assignment worked, need to close.
      if (conn) {
        try {
          console.log("Close Connection")
          await conn.close();

        } catch (err) {
          console.log('Error closing connection', err);
        }
      }
    }
  });
}

And I call in my Index.js (node routes) the getVesselWork like this —> app.get('/distri/:voyageId', api.getVesselWork);

Package.json DEPENDENCIES

"dependencies": {
    "body-parser": "^1.18.2",
    "chalk": "^2.3.0",
    "debug": "^3.1.0",
    "express": "^4.16.1",
    "express-asyncify": "^1.0.0",
    "oracledb": "^2.0.15"
  },

[dmcghan]

@VGamezz19 Can you show us more of the index.js? I want to see how you're setting up express.

[bagl]

There is no row property on result object, it is rows.

resolve(result.rows);