Function doesn't return fetched values from DB . Node.js

Question

I'm stucked with this issues since Thursday and I can't figure out what is the problem. Looking to the log looks like the HTTP request is finish before the data is retrieved from the DB. I tried every combination with async/await though...

Do you guys have any idea how to solve this?

   app.get("/getData", (req, res) => {
    let data = oracledb.getConnection(
      {
        user: "hr",
        password: "hr",
        connectString: "localhost/user"
      },
      (err, connection) => {
        if (err) {
          console.error(err.message);
          return;
        }
        queries.getData(connection, 3);
      }
    );
    console.log('Arrives here:', data)
    res.send(data)
})

    const getData = (conn, id) => {
  const query = `SELECT * FROM SoapDataObject WHERE id = ${id}`;
  let result = conn.execute(query, (err, result)=> {
    if (err) {
      console.error(err.message);
      doRelease(conn);
      return;
    }
    // console.log(result.metaData);
    console.log(result.rows);
    doRelease(conn);
    return result.rows
  })
  return result;
};

Console show this:

 REST API server started on: 3000
[1] Listening on port 8001
[1] WSDL available at http://localhost:8001/soapWs?wsdl
[1] Arrives here: undefined
[1] [ [ 3, '1010', 11, 11, 11, 11, 11, 2 ] ]

Answer 1

As simple as it gets, the issue lies here :

let data = oracledb.getConnection(
      {
        user: "hr",
        password: "hr",
        connectString: "localhost/user"
      },
      (err, connection) => {
        if (err) {
          console.error(err.message);
          return;
        }
        queries.getData(connection, 3);
      }
    );
    console.log('Arrives here:', data)
    res.send(data)

Observe that whatever be the results of the getData is not going to be returned until you return it. Also, inside this method of yours :

 const getData = (conn, id) => {
  const query = `SELECT * FROM SoapDataObject WHERE id = ${id}`;
  let result = conn.execute(query, (err, result)=> {
    if (err) {
      console.error(err.message);
      doRelease(conn);
      return;
    }
    // console.log(result.metaData);
    console.log(result.rows);
    doRelease(conn);
    return result.rows
  })
  return result;
};

Please observe that you cannot return the object from the callbacks merely like you do in the synchronous programming. Rather in both of the methods, you're trying to return execution results of callback methods which doesn't work that way. You have to capture the results inside the scope of the callback and then propagate your results. ( A more elegant solution as Johnathan suggests would be to promisify your methods that call SQL statements, so that you may return the 'promises' and then obtain the values. ( Remember, async/await can be used only with the promises and NOT callbacks ).

A sample implementation thereby becomes ( using promises ) :

app.get("/getData", (req, res) => {
    const dataPromise = new Promise((resolve, reject) => {
        oracledb.getConnection(
            {
                user: "hr",
                password: "hr",
                connectString: "localhost/user"
            },
            (err, connection) => {
                if (err) {
                    reject(error);
                    return;
                }
                queries.getData(connection, 3).then((response) => {
                    resolve(response)
                }).catch(qerror => {
                    reject(qerror);
                })
            }
        )
    });
    dataPromise.then((data) => {
        res.send(data);
    }).catch(error => {
        // Your error handling here
    })

})

const getData = (conn, id) => {
    return new Promise((resolve, reject) => {
        const query = `SELECT * FROM SoapDataObject WHERE id = ${id}`;
        conn.execute(query, (err, result) => {
            if (err) {
                doRelease(conn);
                reject(err);
                return;
            }
            doRelease(conn);
            resolve(result.rows);
        })
    });
};

Or, if you wish to stick to the callbacks :

app.get("/getData", (req, res) => {
    oracledb.getConnection(
        {
            user: "hr",
            password: "hr",
            connectString: "localhost/user"
        },
        (err, connection) => {
            if (err) {
                res.status(500).send(error);
                return;
            }
            const id = 3;
            const query = `SELECT * FROM SoapDataObject WHERE id = ${id}`;
            connection.execute(query, (qerr, result) => {
                if (qerr) {
                    doRelease(connection);
                    // Replace 500 with code of your choice
                    res.status(500).send(qerr);
                    return;
                }
                doRelease(connection);
                res.status(200).send(result.rows);
                return;
            })
        }
    );
});