Node.js oracledb module -- TypeError: Cannot read property 'close' of undefined

Question

I am trying to run simple SQL statements using Oracledb npm for Nodejs. This has worked in the past with no issue but suddenly I am getting this error in a different environment. Possibly due to aliases? I am not sure.

The goal of the line causing the issue is to close out the connection once it completes or fails. I found the logic directly in the docs from Oracle found here: http://oracle.github.io/node-oracledb/doc/api.html#connectionclose

Really would appreciate any help!

const oracledb = require('oracledb');
const async = require('async');
const {getAwsSecret} = require('./awssecret');
var alias = "";

async function initialize() {
  // Set Oracle Pool Settings
  let hrPool = {
    poolMin: 10,
    poolMax: 10,
    poolIncrement: 0
  }

  var secret = await getAwsSecret('MAIN');
  const secretJSON = JSON.parse(secret);
  hrPool.user = secretJSON[process.env.CURENV + 'username'];
  hrPool.password = secretJSON[process.env.CURENV + 'password'];
  hrPool.connectString = secretJSON[process.env.CURENV + 'host'] + '/' + secretJSON[process.env.CURENV + 'dbname'];
  hrPool.poolAlias = secretJSON.alias;
  alias = secretJSON.alias;

  try { 
  await oracledb.createPool(hrPool);
  } catch (err) {
    console.log(err);
  }
}

module.exports.initialize = initialize;

async function close() {
  await oracledb.getPool().close();
}

module.exports.close = close;

async function simpleExecute(statement, binds = [],clientdetails = [], opts = {}) {
    let conn;
    opts.outFormat = oracledb.OBJECT;
    opts.autoCommit = true;
    try {
      // Get Connection
      conn = await oracledb.getConnection(alias);
      // Run Query
      const result = await conn.execute(statement, binds, opts);
    ///////////// POST EXECUTION HANDLING /////////////
    if (conn) { // conn assignment worked, need to close
      try {
        // Close Connection
        await conn.close();
        // Return Result
        return result;
      } catch (err) {
        console.log(err);
      }
    }
    ///////////// POST EXECUTION HANDLING /////////////
    } catch (err) {
      await conn.close();

    } 
  }

module.exports.simpleExecute = simpleExecute;

Error:

TypeError: Cannot read property 'close' of undefined 2021-04-23T14:36:51.269-04:00  at 
Object.simpleExecute (/usr/src/app/services/database.js:59:18)

Christopher Jones · Accepted Answer · 2021-04-24 00:43:27Z

2

If there is an error getting the connection, your catch block calls conn.close() without having a valid conn. This gives the error you see. It may not be the only cause, but tidying the code will help.

Your conn.close() could be just once in a finally() block. Look at examples like example.js e.g. like:

  let connection;

  try {
    connection = await oracledb.getConnection(dbConfig);

    result = await connection.execute(sql, binds, options);
    console.dir(result, { depth: null });

  } catch (err) {
    console.error(err);
  } finally {
    if (connection) {
      try {
        await connection.close();
      } catch (err) {
        console.error(err);
      }
    }
  }

Some other tips:

If you code is going to execute a few statements consecutively then don't do a getConnection()/close() around each. This affects pool scalability because there is some latching on the shared pool resource, and it adds extra code to the whole step.
Avoid always committing. This can destroy transactional consistency and add overhead if done unnecessarily.
Use an explicit drainTime in the pool.close() call to ensure it is closed, see webapp.js.

edited Apr 24, 2021 at 0:43

answered Apr 24, 2021 at 0:38

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

user68288 Over a year ago

Thank you for your info -- based onwhat you have above where are you actually using pool.close()? It looks like it is just using the standard connection.close() attribute of connection. I fear I may be confusing the two and causing myself more problems. I now don't believe my code ever actually calls the close pool (versus connection).

user68288 Over a year ago

One other question -- in that example above where is it actually returning the result? I am using these oracle calls to return data back to my frontend. (I do not see a return result).

Christopher Jones Over a year ago

You can close the pool at app shutdown. For a web app like webapp.js this is when the process is halted. Regarding returning data, replace the console.dir line with a return.

user68288 Over a year ago

Thanks again, lastly if you don't mind I have one more question. If I were to use pool aliases would it change anything? I may have a use case in the future where the alias will be client specific to leverage using Oracle VPD based on the login.

Christopher Jones Over a year ago

Pool aliases won't affect anything. If each Node.js process only has one pool then you won't really need to change the default pool alias. For VPD, look at using connection.clientID instead of having multiple pools.

|

Collectives™ on Stack Overflow

Node.js oracledb module -- TypeError: Cannot read property 'close' of undefined

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related