Node-oracledb: option resultSet=true with connection pool doesn't return any row

Created on 20 Jul 2017 · 4Comments · Source: oracle/node-oracledb

I have a problem with Oracle pool and resultset.

I have crated a oracledb pool with these parameters:

{
    poolMax:          30,
    poolMin:          2,
    poolIncrement:    5,
    poolTimeout:      30
}

I have wrapped execute in this way:

function execute(sql, bindParams, options) {
    return new Promise(function (resolve, reject) {

      getConnection()
        .then((connection) => {
          connection.execute(sql, bindParams, options, function (err, results) {
            if (err) {
              logger.error("Cannot get data for query:" + sql + " params:" + bindParams + " error: " + err);
              releaseConnection(connection);
              reject(err);
            } else {
              r = [];
              fetchRowFromRS(results.resultSet,r);
              resolve(r);
            }
          });
        })
        .catch((err) => {
          logger.error("Cannot get connection for query:" + sql + " params:" + bindParams + " error: " + err);
          reject(err);
        })
    });
  }

  function fetchRowFromRS(resultSet, res) {
    resultSet.getRow(function (err, row) {
      if (err) throw err;
      if (row) {
        res.push(row);
        fetchRowFromRS(resultSet, res);
        return;
      }
    });
    resultSet.close(function (err) {
      if (err) console.error(err.message);
    });
  }

I'm trying to use these bind parameters: { outFormat: oraclepool.OBJECT , resultSet: true }
But with I have always number of rows = 0 in resultset

The same function executed with option: { outFormat: oraclepool.OBJECT} written in this way:

function execute(sql, bindParams, options) {
    return new Promise(function (resolve, reject) {
      getConnection()
        .then((connection) => {
          connection.execute(sql, bindParams, options, function (err, results) {
            if (err) {
              logger.error("Cannot get data for query:" + sql + " params:" + bindParams + " error: " + err);
              releaseConnection(connection);
              reject(err);
            } else {
              releaseConnection(connection);
              resolve(results);
          });
        })
        .catch((err) => {
          logger.error("Cannot get connection for query:" + sql + " params:" + bindParams + " error: " + err);
          reject(err);
        })
    });
  }

return rows

Anyone ever had the same problem?

Node version: 7.7.4
Oracle Version: 11.2.01
OS: Windows

question

Source

klaus82

All 4 comments

@klaus82 The first thing I noticed is getConnection seems to come out of nowhere. How was that defined?

Next, when using the then and catch methods with promises, you generally want to return a promise if you're doing subsequent sequential operations (even the catch). If you forget to return, things may not work as you expect.

Here's an example where the promise is not returned:

function resolveApi() {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('about to resolve');
      resolve();
    }, 2000);
  });
}

function rejectApi() {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('about to reject');
      reject();
    }, 2000);
  });
}

resolveApi()
  .then(() => {
    rejectApi(); // forgot to return here
  })
  .catch(err => {
    console.log('Caught it!', err); // This will not catch the rejection
  });

If you run that in Node.js or a browser you'll get an uncaught promise rejection error.

Here's the same code with the return statement in the then clause:

function resolveApi() {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('about to resolve');
      resolve();
    }, 2000);
  });
}

function rejectApi() {
  return new Promise((resolve, reject) => {
    setTimeout(() => {
      console.log('about to reject');
      reject();
    }, 2000);
  });
}

resolveApi()
  .then(() => {
    return rejectApi(); // Now we are returning the promise
  })
  .catch(err => {
    console.log('Caught it!', err); // This will catch the rejection
  });

The next issue is that fetchRowFromRS is async, but you're treating it like a synchronous operation with these two lines:

              fetchRowFromRS(results.resultSet,r); // This is async
              resolve(r); // So this executes before the Result Set is processed

The same issue appears inside fetchRowFromRS, only in that case you're closing the Result Set before it's done fetching.

  function fetchRowFromRS(resultSet, res) {
    resultSet.getRow(function (err, row) { // This function is async
      if (err) throw err;
      if (row) {
        res.push(row);
        fetchRowFromRS(resultSet, res);
        return;
      }
    });
    resultSet.close(function (err) { // So this function executes before getRow even completes
      if (err) console.error(err.message);
    });
  }

I recently wrote a series on async development using the driver. There are links at the bottom of that post to other patterns. I recommend reading through that some.

You note that you're using Node.js 7.7, so you can actually take advantage of async functions (async/await) which makes this all much easier!

Here's an example wrapper module that uses async functions to process a result set:

const oracledb = require('oracledb');
const dbConfig = require('./dbConfig.js'); 

function createDefaultPool() {
  return new Promise(async function(resolve, reject) {
    try {
      await oracledb.createPool(dbConfig);

      resolve();
    } catch (err) {
      reject(err);
    }
  });
}

module.exports.createDefaultPool = createDefaultPool;

function executeResultSet(sql, binds, options) {
  // Note the 'async' in front of the annonymous function below, it let's us 
  // use await within the funtion
  return new Promise(async function(resolve, reject) {
    let conn; // Declared here for scoping purposes.

    if (options.resultSet !== true) {
      reject(new Error('This API is for Result Sets only'));
      return;
    }

    try {
      conn = await oracledb.getConnection(); // This is await, it's awesome!

      const result = await conn.execute(sql, binds, options);

      const retval = [];

      let row = await result.resultSet.getRow();

      while (row) {
        retval.push(row);

        row = await result.resultSet.getRow();
      }

      resolve(retval);
    } catch (err) {
      console.log('Error occurred', err);

      reject(err);
    } finally {
      // If conn assignment worked, need to close.
      if (conn) {
        try {
          await conn.close();
        } catch (err) {
          console.log('Error closing connection', err);
        }
      }
    }
  });
}

module.exports.executeResultSet = executeResultSet;

Here's a test script that uses the wrapper module above:

wrapper = require('./wrapper-module');

async function runTest() {
  try {
    await wrapper.createDefaultPool(); // Would normally be done at process start

    const rows = await wrapper.executeResultSet(
      'select * from employees',
      [], // no binds
      {
        resultSet: true
      }
    );

    console.log('Success! Fetched ' + rows.length + ' rows.');
  } catch (err) {
    console.log('Ouch', err);
  }
}

runTest();