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 ] ]

Please include code snippets as text rather than as screenshots. It's easier for us to assist when we don't have to type everything out again. [Blocks of code](https://stackoverflow.com/editing-help#code) can be formatted with 4 extra spaces before each line, or with the `{}` button in the post editor's toolbar. – Also: [How do I return the response from an asynchronous call?](https://stackoverflow.com/questions/14220321/how-do-i-return-the-response-from-an-asynchronous-call) — Jonathan Lonowski, Jul 15 '18 at 19:26
[`await`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/await) is a syntax sugar for interacting with [Promises](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise). Check the database module's documentation for whether `getConnection` or `execute` support Promises. If they don't, as the use of callbacks can sometimes suggest, it may be possible to [`promisify()`](https://nodejs.org/dist/latest-v10.x/docs/api/util.html#util_util_promisify_original) each of them them. — Jonathan Lonowski, Jul 15 '18 at 19:35

score 0 · Accepted Answer · edited Jul 15 '18 at 21:01

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;
            })
        }
    );
});

Perfect explanation nerdier.js . I was assuming that oracledb.execute was a already a promise and therefore trying to implement async/await . Thank you! — LuisMendes535, Jul 16 '18 at 08:15

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

1 Answers1