How do I reuse connection?

[RobertZito]

I have this simple code that I am using to practice with node and js. But I keep on getting an error that the connection is not defined. How do I list the connection so I can reuse the connection anywhere in my code?

var Oracle = require('oracle'); var rownum = 10;

var connectData = { hostname: "localhost", port: 1521, database: "hr", // System ID (SID) user: "user", password: "password" }

Oracle.connect = function(callback) { oracle_driver.connect(connectData, function(err, connection) { if (err) { console.log(err); } else { callback(err, connection); } connection.close(); }); }

var statement = connection.prepare("SELECT * FROM HR where rownum < :1");

function runQuery(stmt, row_num, cb) { stmt.execute([row_num], function(err, count) { if (err) return cb(err); if (count !== 1) return cb(new Error("bad count: " + count)); // We are done return cb(); }); }

runQuery(statement, 90, function(err) { if (err) { console.log("Error executing query:", err); return; }

console.log(results);
connection.close(); // call only when query is finished executing 

});

[mkozjak]

If you want to get away without using any libraries to persist the connection, just add your established connection to an object:

var pool = []; .... oracle_driver.connect(connectData, function(err, connection) { pool[oracleConnection] = connection; }); ...

// use the connection later (unless you closed it somewhere on the way...) pool[oracleConnection].execute(...);

Best regards, mk

[kochecc2]

I tried this and it doesn't save the connection.

pool['mfs'].execute(sql, [], function(err, results) { ^ TypeError: Cannot call method 'execute' of undefined at Object. (/home/ssi/nodemonitor/app.js:16:13) at Module._compile (module.js:456:26) at Object.Module._extensions..js (module.js:474:10) at Module.load (module.js:356:32) at Function.Module._load (module.js:312:12) at Function.Module.runMain (module.js:497:10) at startup (node.js:119:16) at node.js:901:3

here is my code: var oracle = require('oracle'); var connectData = { hostname: "host", port: 1521, database: "thedb", user: "user1", password: "pass1" } var pool = []; oracle.connect(connectData, function(err, connection) { if (err) { console.log("Error connecting to db:", err); return; } pool['mfs'] = connection; });

var sql = "select sysdate from dual"; pool['mfs'].execute(sql, [], function(err, results) { if (err) { console.log("Error executing query:", err); return; } console.log(results); });

I am able to query inside the oracle.connect() function.

[mkozjak]

That's because "pool['mfs'].execute" is executed at once before "oracle.connect" did its job (async). So 'execute' is called before the storing was done. With this approach, you are supposed to introduce a check before using 'execute'. In example:

function checkDb(dbName, callback) { if (!pool[dbName]) { // INFO: open your db and execute the given callback (also handle errors here) ... } else callback(null); }

You would use this function like this, having in mind that the only argument to the callback is an error, if it exists: checkDb('mfs', function(error) { if (!error) { pool['mfs'].execute ... ... } ... }

I hope this helps. :)

With regards, Mario Kozjak

[kochecc2]

so like this?

var pool = [];

function checkDb(dbName, callback) { if (!pool[dbName]) { oracle.connect(connectData, function(err, connection) { if (err) { console.log("Error connecting to db:", err); return; } pool['mfs'] = connection; console.log(pool['mfs']); }); } else callback(null); }

checkDb(pool['mfs'], function(error){ if(!error){ var sql = "select sysdate from dual"; pool[db].execute(sql, [], function(err, results) { if (err) { console.log("Error executing query:", err); return; } console.log(results); }); } });

It executes, and doesn't error out, but it also doesn't print any results.

[kontrafiktion]

I would propose to move such questions to StackExchange. This looks not like an issue with the node oracle driver.

Your code has too many issues:

1. checkDb has a parameter that should be the name of a db but you invoke it with pool['mfs'] which probably is the connection
2. later in checkDb you use 'mfs' instead of the dbName parameter
3. most importantly you do not call callback(null) when you created a new connection, therefore the callback will never be called

I would propose to add console.log to nearly every line, printing the values of your variables until you understand what is happening (poor man's debugging).

And GitHubs allows you to paste code in a way that preserves the indentation, see: https://help.github.com/articles/github-flavored-markdown

[mkozjak]

So you're trying this piece of code:

var pool = []; 
var oracle = require('oracle');

var connectData = { 
// some db data here
};

// here we check for the existence of connection
function checkDb(dbName, callback) {
  // if there's no connection existing, just open the connection
  if (!pool[dbName]) {
    oracle.connect(connectData, function(err, connection) {
      if (err) { console.log("Error connecting to db:", err); return; }

      pool['mfs'] = connection;
      console.log('db opened');
    }); 
  }
  // so first time this won't be ran:
  else callback(null);
}

checkDb('mfs', function(error){
  console.log('running');

  if(!error){
    var sql = "select sysdate from dual";
    pool['mfs'].execute(sql, [], function(err, results) {
      if (err) { console.log("Error executing query:", err); return; }
      console.log(results);
    }); 
  }
});

If you add this on the bottom, you'll see that the connection will be reused:

setTimeout(function() {
  checkDb('mfs', function(error){
    console.log('running 2nd time');

    if(!error){
      var sql = "select sysdate from dual";
      pool['mfs'].execute(sql, [], function(err, results) {
        if (err) { console.log("Error executing query:", err); return; }
        console.log('under timeout:', results);
      }); 
    }   
  }); 
}, 1000);

Regards, Mario Kozjak

[theothermattm]

Seems like this is addressed in the readme now, but it suggests using https://github.com/coopernurse/node-pool to pool connections. I'm using this with pretty good success.