node-ibm_db

An asynchronous/synchronous interface for node.js to IBM DB2 and IBM Informix.

requirements

Install IBM Data Server Driver
- http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.swg.im.dbclient.install.doc/doc/t0054799.html
Ensure you have run the installDSDriver command as specified in the URL above. Now set the environment variable IBM_DB_HOME to the installed DSDriver location.
- Eg: say you installed DS Driver at location $HOME/dsdriver . Set IBM_DB_HOME as follows
```
  export IBM_DB_HOME=$HOME/dsdriver
```

install

After ensuring that the above requirement is satisfied you may install by one of the two following options:

npm

npm install ibm_db

git

git clone git://github.com/ibmdb/node-ibm_db.git
cd node-ibm_db
node-gyp configure build

quick example

Before running your node.js program source the file db2profile in the DS Driver installation directory
- Eg: source <installed_dsdriver_location>/db2profile

var ibmdb = require('ibm_db');

ibmdb.open("DRIVER={DB2};DATABASE=<dbname>;HOSTNAME=<myhost>;UID=db2user;PWD=password;PORT=<dbport>;PROTOCOL=TCPIP", function (err,conn) {
  if (err) return console.log(err);
  
  conn.query('select * from user where user_id = ?', [42], function (err, data) {
    if (err) console.log(err);
    
    console.log(data);

    conn.close(function () {
      console.log('done');
    });
  });
});

Discussion Forums

To start a discussion or need help you can post a topic on node-ibm_db google group https://groups.google.com/forum/#!forum/node-ibm_db

api

Database

The simple api is based on instances of the Database class. You may get an instance in one of the following ways:

require("ibm_db").open(connectionString, function (err, conn){
  //conn is already open now if err is falsy
});

or by using the helper function:

var ibmdb = require("ibm_db")();

or by creating an instance with the constructor function:

var Database = require("ibm_db").Database
  , ibmdb = new Database();

.openSync(connectionString)

Synchronously open a connection to a database.

connectionString - The connection string for your database

var ibmdb = require("ibm_db")()
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

try {
  var result = ibmdb.openSync(cn);
}
catch (e) {
  console.log(e.message);
}

//we now have an open connection to the database

.query(sqlQuery [, bindingParameters], callback)

Issue an asynchronous SQL query to the database which is currently open.

sqlQuery - The SQL query to be executed.
bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in sqlQuery.
callback - callback (err, rows, moreResultSets)

var ibmdb = require("ibm_db")
	, cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
	;

ibmdb.open(cn, function (err, conn) {
	if (err) {
		return console.log(err);
	}

	//we now have an open connection to the database
	//so lets get some data
	conn.query("select * from customers fetch first 10 rows only", function (err, rows, moreResultSets) {
		if (err) {
			console.log(err);
		} else {
		
		  console.log(rows);
		}

		//if moreResultSets is truthy, then this callback function will be called
		//again with the next set of rows.
	});
});

.querySync(sqlQuery [, bindingParameters])

Synchronously issue a SQL query to the database that is currently open.

sqlQuery - The SQL query to be executed.
bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in sqlQuery.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn, function(err, conn){

  //blocks until the query is completed and all data has been acquired
  var rows = conn.querySync("select * from customers fetch first 10 rows only");

  console.log(rows);
})

.close(callback)

Close the currently opened database.

callback - callback (err)

var ibmdb = require("ibm_db")
	, cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
	;

ibmdb.open(cn, function (err, conn) {
	if (err) {
		return console.log(err);
	}
	
	//we now have an open connection to the database
	
	conn.close(function (err) {
		console.log("the database connection is now closed");
	});
});

.closeSync()

Synchronously close the currently opened database.

var ibmdb = require("ibm_db")()
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

//Blocks until the connection is open
ibmdb.openSync(cn);

//Blocks until the connection is closed
ibmdb.closeSync();

.prepare(sql, callback)

Prepare a statement for execution.

sql - SQL string to prepare
callback - callback (err, stmt)

Returns a Statement object via the callback

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn,funtion(err,conn){
  conn.prepare("insert into hits (col1, col2) VALUES (?, ?)", function (err, stmt) {
    if (err) {
      //could not prepare for some reason
      console.log(err);
      return conn.closeSync();
    }

    //Bind and Execute the statment asynchronously
    stmt.execute(['something', 42], function (err, result) {
      result.closeSync();

      //Close the connection
	  conn.close(function(err){}));
    });
  });
});

.prepareSync(sql)

Synchronously prepare a statement for execution.

sql - SQL string to prepare

Returns a Statement object

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn,funtion(err,conn){
  var stmt = conn.prepareSync("insert into hits (col1, col2) VALUES (?, ?)");

  //Bind and Execute the statment asynchronously
  stmt.execute(['something', 42], function (err, result) {
    result.closeSync();

    //Close the connection
	conn.close(function(err){}));
  });
});

.beginTransaction(callback)

Begin a transaction

callback - callback (err)

.beginTransactionSync()

Synchronously begin a transaction

.commitTransaction(callback)

Commit a transaction

callback - callback (err)

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn, function(err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    var result = conn.querySync("insert into customer (customerCode) values ('stevedave')");

    conn.commitTransaction(function (err) {
      if (err) {
        //error during commit
        console.log(err);
        return conn.closeSync();
      }

    console.log(conn.querySync("select * from customer where customerCode = 'stevedave'"));

     //Close the connection
     conn.closeSync();
    });
  });
});

.commitTransactionSync()

Synchronously commit a transaction

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn, function(err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    var result = conn.querySync("insert into customer (customerCode) values ('stevedave')");

    conn.commitTransactionSync();

    console.log(conn.querySync("select * from customer where customerCode = 'stevedave'"));

     //Close the connection
    conn.closeSync();
  });
});

.rollbackTransaction(callback)

Rollback a transaction

callback - callback (err)

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn, function(err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    var result = conn.querySync("insert into customer (customerCode) values ('stevedave')");

    conn.rollbackTransaction(function (err) {
      if (err) {
        //error during rollback
        console.log(err);
        return conn.closeSync();
      }

    console.log(conn.querySync("select * from customer where customerCode = 'stevedave'"));

     //Close the connection
     conn.closeSync();
    });
  });
});

.rollbackTransactionSync()

Synchronously rollback a transaction

var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
  ;

ibmdb.open(cn, function(err,conn) {

  conn.beginTransaction(function (err) {
    if (err) {
      //could not begin a transaction for some reason.
      console.log(err);
      return conn.closeSync();
    }

    var result = conn.querySync("insert into customer (customerCode) values ('stevedave')");

    conn.rollbackTransactionSync();

    console.log(conn.querySync("select * from customer where customerCode = 'stevedave'"));

     //Close the connection
    conn.closeSync();
  });
});

Pool

node-ibm_db reuses node-odbc pool. The node-odbc Pool is a rudimentary connection pool which will attempt to have database connections ready and waiting for you when you call the open method.

If you use a Pool instance, any connection that you close will cause another connection to be opened for that same connection string. That connection will be used the next time you call Pool.open() for the same connection string.

This should probably be changed.

.open(connectionString, callback)

Get a Databaseinstance which is already connected toconnectionString`

connectionString - The connection string for your database
callback - callback (err, db)

var Pool = require("ibm_db").Pool
	, pool = new Pool()
	, cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
	;

pool.open(cn, function (err, db) {
	if (err) {
		return console.log(err);
	}

	//db is now an open database connection and can be used like normal
	//if we run some queries with db.query(...) and then call db.close();
	//a connection to `cn` will be re-opened silently behind the scense
	//and will be ready the next time we do `pool.open(cn)`
});

.close(callback)

Close all connections in the Pool instance

callback - callback (err)

var Pool = require("ibm_db").Pool
	, pool = new Pool()
	, cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;"
	;

pool.open(cn, function (err, db) {
	if (err) {
		return console.log(err);
	}

	//db is now an open database connection and can be used like normal
	//but all we will do now is close the whole pool
	
	pool.close(function () {
		console.log("all connections in the pool are closed");
	});
});

build options

Debug

If you would like to enable debugging messages to be displayed you can add the flag DEBUG to the defines section of the binding.gyp file and then execute node-gyp rebuild.

<snip>
'defines' : [
  "DEBUG"
],
<snip>

Unicode

By default, UNICODE suppport is enabled. This should provide the most accurate way to get Unicode strings submitted to your database. For best results, you may want to put your Unicode string into bound parameters.

However, if you experience issues or you think that submitting UTF8 strings will work better or faster, you can remove the UNICODE define in binding.gyp

<snip>
'defines' : [
  "UNICODE"
],
<snip>

timegm vs timelocal

When converting a database time to a C time one may use timegm or timelocal. See man timegm for the details of these two functions. By default the node-ibm_db bindings use timelocal. If you would prefer for it to use timegm then specify the TIMEGM define in binding.gyp

<snip>
'defines' : [
  "TIMEGM"
],
<snip>

Strict Column Naming

When column names are retrieved from DB2 CLI, you can request by SQL_DESC_NAME or SQL_DESC_LABEL. SQL_DESC_NAME is the exact column name or none if there is none defined. SQL_DESC_LABEL is the heading or column name or calculation. SQL_DESC_LABEL is used by default and seems to work well in most cases.

If you want to use the exact column name via SQL_DESC_NAME, enable the STRICT_COLUMN_NAMES define in binding.gyp

<snip>
'defines' : [
  "STRICT_COLUMN_NAMES"
],
<snip>

tips

Using node < v0.10 on Linux

Be aware that through node v0.9 the uv_queue_work function, which is used to execute the ODBC functions on a separate thread, uses libeio for its thread pool. This thread pool by default is limited to 4 threads.

This means that if you have long running queries spread across multiple instances of ibmdb.Database() or using odbc.Pool(), you will only be able to have 4 concurrent queries.

You can increase the thread pool size by using @developmentseed's [node-eio] (https://github.com/developmentseed/node-eio).

install:

npm install eio

usage:

var eio = require('eio'); 
eio.setMinParallel(threadCount);

contributors

Dan VerWeire (dverweire@gmail.com)
Lee Smith (notwink@gmail.com)
Bruno Bigras
Christian Ensel
Yorick
Joachim Kainz
Oleg Efimov
paulhendrix
IBM

license

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Files

README.md

Latest commit

History

README.md

File metadata and controls

node-ibm_db

requirements

install

npm

git

quick example

Discussion Forums

api

Database

.openSync(connectionString)

.query(sqlQuery [, bindingParameters], callback)

.querySync(sqlQuery [, bindingParameters])

.close(callback)

.closeSync()

.prepare(sql, callback)

.prepareSync(sql)

.beginTransaction(callback)

.beginTransactionSync()

.commitTransaction(callback)

.commitTransactionSync()

.rollbackTransaction(callback)

.rollbackTransactionSync()

Pool

.open(connectionString, callback)

.close(callback)

build options

Debug

Unicode

timegm vs timelocal

Strict Column Naming

tips

Using node < v0.10 on Linux

install:

usage:

contributors

license