Skip to content

Latest commit

 

History

History
1687 lines (1298 loc) · 70.9 KB

APIDocumentation.md

File metadata and controls

1687 lines (1298 loc) · 70.9 KB
Raw

ibm_db API Documentation

Database APIs

APIs for creating and droping Database using node.js application

Database APIs

  1. .open(connectionString, [options,] callback)
  2. .openSync(connectionString)
  3. .query(sqlQuery [, bindingParameters], callback)
  4. .querySync(sqlQuery [, bindingParameters])
  5. .queryStream(sqlQuery [, bindingParameters])
  6. .queryResult(sqlQuery [, bindingParameters], callback)
  7. .queryResultSync(sqlQuery [, bindingParameters])
  8. .close(callback)
  9. .closeSync()
  10. .prepare(sql, callback)
  11. .prepareSync(sql)
  12. .bind(bindingParameters, callback)
  13. .bindSync(bindingParameters)
  14. .execute([bindingParameters], callback)
  15. .executeSync([bindingParameters])
  16. .executeNonQuery([bindingParameters], callback)
  17. .executeNonQuerySync([bindingParameters])
  18. stmt.close(callback)
  19. stmt.closeSync()
  20. .fetch(option, callback)
  21. .fetchSync(option)
  22. .fetchAll(option, callback)
  23. .fetchAllSync(option)
  24. .getData(colNum, Size, callback)
  25. .getDataSync(colNum, Size)
  26. result.close(callback)
  27. result.closeSync()
  28. .beginTransaction(callback)
  29. .beginTransactionSync()
  30. .commitTransaction(callback)
  31. .commitTransactionSync()
  32. .rollbackTransaction(callback)
  33. .rollbackTransactionSync()
  34. .setIsolationLevel(isolationLevel)
  35. .getColumnNamesSync()
  36. .getColumnMetadataSync()
  37. .getSQLErrorSync()
  38. .debug(value)
  39. .executeFileSync(sqlFile,[delimiter],[outputFile])
  40. .executeFile(sqlFile,[delimiter],[outputFile])
  41. .setAttr(attributeName, value, callback)
  42. .setAttrSync(attributeName, value)
  43. .getInfo(infoType, [infoLength], callback)
  44. .getInfoSync(infoType, [infoLength])
  45. .getTypeInfo(dataType, callback)
  46. .getTypeInfoSync(dataType)
  47. .getFunctions(functionId, callback)
  48. .getFunctionsSync(functionId)

1) .open(connectionString, [options,] callback)

Open a connection to a database.

  • connectionString - The connection string for your database.
    • For distributed platforms, the connection string is typically defined as: DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=passwd
    • For z/OS, the ODBC driver makes both local and remote connections using DSN, UID and PWD. The connection string is typically defined as: DSN=dbname;UID=username;PWD=passwd. To connect to remote Db2 databases, the connectivity information will need to be set up in the Communications Database (CDB). Please refer to scenario 1 in the following article. For a complete list of supported configuration keywords, please refer to CLI/ODBC configuration keywords
    • Selecting default schema is also supported using CURRENTSCHEMA=schemaname; (or CurrentSchema)
  • options - OPTIONAL - Object type. Can be used to avoid multiple loading of native ODBC library for each call of .open. Also, can be used to pass connectTimeout value and systemNaming(true/false) for i5/OS server.
  • callback - callback (err, conn)
var ibmdb = require("ibm_db")
  , connStr = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=passwd";

ibmdb.open(connStr, function (err, connection) {
    if (err)
    {
      console.log(err);
      return;
    }
    connection.query("select 1 from sysibm.sysdummy1", function (err1, rows) {
      if (err1) console.log(err1);
      else console.log(rows);
      connection.close(function(err2) {
        if(err2) console.log(err2);
      });
    });
});
  • Secure Database Connection using SSL/TSL - ibm_db supports secure connection to Database Server over SSL same as ODBC/CLI driver. If you have SSL Certificate from server or an CA signed certificate, just use it in connection string as below:
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;Security=SSL;SSLServerCertificate=<cert.arm_file_path>;PROTOCOL=TCPIP;UID=username;PWD=passwd;";

Note the two extra keywords Security and SSLServerCertificate used in connection string. SSLServerCertificate should point to the SSL Certificate from server or an CA signed certificate. Also, PORT must be SSL port and not the TCPI/IP port. Make sure Db2 server is configured to accept connection on SSL port else ibm_db will throw SQL30081N error.

Value of SSLServerCertificate keyword must be full path of a certificate file generated for client authentication. It normally has *.arm or *.cert or *.pem extension. ibm_db do not support *.jks format file as it is not a certificate file but a Java KeyStore file, extract certificate from it using keytool and then use the cert file.

ibm_db uses IBM ODBC/CLI Driver for connectivity and it do not support a *.jks file as keystoredb as keystore.jks is meant for Java applications. Note that *.jks file is a Java Key Store file and it is not an SSL Certificate file. You can extract SSL certificate from JKS file using below keytool command:

keytool -exportcert -alias your_certificate_alias -file client_cert.cert -keystore  keystore.jks

Now, you can use the generated client_cert.cert as the value of SSLServerCertificate in connection string.

ibm_db supports only ODBC/CLI Driver keywords in connection string: https://www.ibm.com/docs/en/db2/11.5?topic=odbc-cliodbc-configuration-keywords

Do not use keyworkds like sslConnection=true in connection string as it is a JDBC connection keyword and ibm_db ignores it. Corresponding ibm_db connection keyword for sslConnection is Security hence, use Security=SSL; in connection string instead.

  • To connect to dashDB in IBM Cloud, use below connection string:
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=passwd;Security=SSL";

We just need to add Security=SSL in connection string to have a secure connection against Db2 server in IBM Cloud.

Note: You can also create a KeyStore DB using GSKit command line tool and use it in connection string along with other keywords as documented in DB2 Infocenter.

If you have created a KeyStore DB using GSKit using password or you have got *.kdb file with *.sth file, use connection string in below format:

connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=db2pwd;" +
          "Security=SSL;SslClientKeystoredb=C:/client.kdb;SSLClientKeystash=C:/client.sth;";
OR,
connStr = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=db2pwd;" +
          "Security=SSL;SslClientKeystoredb=C:/client.kdb;SSLClientKeystoreDBPassword=kdbpasswd;";

If you have downloaded IBMCertTrustStore from IBM site, ibm_db will not work with it; you need to download Secure Connection Certificates.zip file that comes for IBM DB2 Command line tool(CLP). Secure Connection Certificates.zip has *.kdb and *.sth files that should be used as the value of SSLClientKeystoreDB and SSLClientKeystash in connection string.

2) .openSync(connectionString [,options])

Synchronously open a connection to a database.

  • connectionString - The connection string for your database
  • options - OPTIONAL - Object type. Can be used to avoid multiple loading of native ODBC library for each call of .open. Also, can be used to pass connectTimeout value and systemNaming value for i5/OS server.
var ibmdb = require("ibm_db"),
	connString = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password;";

try {
      var option = { connectTimeout : 40, systemNaming : true };// Connection Timeout after 40 seconds.
      var conn = ibmdb.openSync(connString, option);
      conn.query("select * from customers fetch first 10 rows only", function (err, rows) {
		if (err) {
			console.log(err);
		} else {
		  console.log(rows);
		}
		conn.close();
      });
    } catch (e) {
      console.log(e.message);
    }

3) .query(sqlQuery [, bindingParameters], callback)

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

  • sqlQuery - The SQL query to be executed or an Object in the form {"sql": sqlQuery, "params":bindingParameters, "noResults": noResultValue, "ArraySize": n}. noResults accepts only true or false values. If true - query() will not return any result. "sql" field is mandatory in Object, others are OPTIONAL. For Array Insert, ArraySize must be passed and sqlQuery must be an object. Check test-array-insert.js for example.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in sqlQuery. bindingParameters in sqlQuery Object takes precedence over it.

  • callback - callback (err, rows, sqlca)

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.
    // Execute multiple query and get multiple result sets.
    // In case of multiple resultset, query will return an array of result sets.
    conn.query("select 1 from sysibm.sysdummy1;select 2 from sysibm.sysdummy1;" +
               "select 3 from sysibm.sysdummy1", function (err, rows, sqlca)
    {
        if (err) {
            console.log(err);
        } else {
            console.log(rows); // rows = [ [ { '1': 1 } ], [ { '1': 2 } ], [ { '1': 3 } ] ]
        }
    });
});

Example for Array Insert:

  var param1 = {ParamType:"ARRAY", DataType:1, Data:[4,5,6,7,8]};
  var param2 = {ParamType:"ARRAY", DataType:"DOUBLE", Data:[4.1,5.3,6.14,7,8.3]};
  var param3 = {ParamType:"ARRAY", DataType:1, Data:[0,1,false,true,0]};
  var namearr = ["Row 10", "Row 203456", "Row 30", "Row 40", "Last Row"];

  var param4 = {ParamType:"ARRAY", DataType:1, Data:namearr, Length:8};
  // *** Use "Length: <maxDataLen>" in param Object for unequal size of data.
  // Default value is the length of first member of Array.

  var queryOptions = {sql:"insert into arrtab values (?, ?, ?, ?)", 
                      params: [param1, param2, param3, param4],
                      ArraySize:5};

  conn.querySync("create table arrtab (c1 int, c2 double, c3 boolean, c4 varchar(10))");
  conn.query(queryOptions, function(err, result) {
    if(err) console.log(err);
    else {
      var data = conn.querySync("select * from arrtab");
      console.log("\nSelected data for table ARRTAB =\n", data);
    }
  });

4) .querySync(sqlQuery [, bindingParameters])

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

  • sqlQuery - The SQL query to be executed or an Object in the form {"sql": sqlQuery, "params":bindingParameters, "noResults": noResultValue, "ArraySize": n}. noResults accepts only true or false values. If true - query() will not return any result. If noResults is true for CALL statement, querySync returns only OutParams. "sql" field is mandatory in Object, others are optional. For Array Insert, ArraySize must be passed and sqlQuery must be an object. Check test-array-insert.js for example.

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

Example for Array Insert:

  var param1 = {ParamType:"ARRAY", DataType:1, Data:[4,5,6,7,8]};
  var param2 = {ParamType:"ARRAY", DataType:"DOUBLE", Data:[4.1,5.3,6.14,7,8.3]};
  var param3 = {ParamType:"ARRAY", DataType:1, Data:[0,1,false,true,0]};
  var namearr = ["Row 10", "Row 20", "Row 30", "Row 40", "Last Row"];

  var param4 = {ParamType:"ARRAY", DataType:1, Data:namearr, Length:8};
  // *** Use "Length: <maxDataLen>" in param Object for unequal size of data.
  // Default value is the length of first member of Array.

  var queryOptions = {sql:"insert into arrtab values (?, ?, ?, ?)", 
                      params: [param1, param2, param3, param4],
                      ArraySize:5};

  conn.querySync("create table arrtab (c1 int, c2 double, c3 boolean, c4 varchar(10))");
  conn.querySync(queryOptions);

5) .queryStream(sqlQuery [, bindingParameters])

Synchronously issue a SQL query to the database that is currently open and returns a Readable stream. Application can listen the events emmitted by returned stream and take action.

  • sqlQuery - The SQL query to be executed or an Object in the form {"sql": sqlQuery, "params":bindingParameters, "noResults": noResultValue, "ArraySize": n}. noResults accepts only true or false values. If true - query() will not return any result. "sql" field is mandatory in Object, others are optional. For Array Insert, ArraySize must be passed and sqlQuery must be an object. Check test-array-insert.js for example.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in sqlQuery.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn, function(err, conn)
{
    var stream = conn.queryStream("select 1 from sysibm.sysdummy1");

    stream.once('data', function (result) {
      console.log(result);
    }).once('error', function (err) {
      conn.closeSync();
      throw err;
    }).once('end', function () {
      conn.close(function(){ console.log("done.") });
    });
});

6) .queryResult(sqlQuery, [, bindingParameters], callback)

Issue an asynchronous SQL query to the database which is currently open and return (err, result, outparams) to callback function. result is ODBCResult object. Uisng result, call result.fetchAllSync() to retrieve all rows; call result.getColumnMetadataSync() to get meta data info or call result.fetchSync() to retrieve each row one by one and process. Execute result.closeSync() once done with the result object. query returns all the rows on call, but queryResult returns the result object and rows need to be fetched by the caller.

  • sqlQuery - The SQL query to be executed or an Object in the form {"sql": sqlQuery, "params":bindingParameters, "noResults": noResultValue, "ArraySize": n}. noResults accepts only true or false values. If true - queryResult() will not return any result object and value of result will be null. "sql" field is mandatory in Object, others are OPTIONAL. For Array Insert, ArraySize must be passed and sqlQuery must be an object. Check test-array-insert.js for example.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any ? characters (called parameter marker) in sqlQuery. bindingParameters in sqlQuery Object takes precedence over it.

  • callback - callback (err, result, outparams). outparams is returned only for CALL statement with OUT parameters. Any resultset expected from SP should get retrieved using result.fetch apis.

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);
    var query = 'select creator, name from sysibm.systables where 1 = ?';
    conn.queryResult(query, [1], function (err, result) {
        if(err) { console.log(err); }
        else {
          console.log("data = ", result.fetchAllSync());
          console.log("metadata = ", result.getColumnMetadataSync());
          result.closeSync(); // Must call in application.
          conn.closeSync();
          console.log("Executed ", ++loop, " times.");
        }
    });
});

Note: Once you are done with the result object, must close it to avoid error when garbage collector of javascript free it. Not calling the `result.closeSync() may cause invalid handle error in application or no data.

7) .queryResultSync(sqlQuery [, bindingParameters])

Synchronously issue a SQL query to the database that is currently open and return a result object to the callback function on success. In case of CALL statement with OUT parameters, it returns an array of [result, outparams]. result is an ODBCResult object that can be used to fetch rows.

querySyncAPI returns all the rows on call, but queryResultSync API returns the ODBCResult object using which application should call fetch APIs to get data.

  • sqlQuery - The SQL query to be executed or an Object in the form {"sql": sqlQuery, "params":bindingParameters, "noResults": noResultValue, "ArraySize": n}. noResults accepts only true or false values. If true - the value of result will be null. "sql" field is mandatory in Object, others are optional. For Array Insert, ArraySize must be passed and sqlQuery must be an object. Check test-array-insert.js for example.

  • 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){
  if (err) return console.log(err);
  var query = 'select creator, name from sysibm.systables';
  var result = conn.queryResultSync(query);
  console.log("data = ", result.fetchAllSync());
  console.log("metadata = ", result.getColumnMetadataSync());
  result.closeSync(); // Must call to free to avoid application error.
  conn.closeSync();
});

Note: Once you are done with the result object, must close it to avoid error when garbage collector of javascript free it. Not calling the `result.closeSync() may cause invalid handle error in application or no data.

In case of CALL statement with OUT params, check result[0] is an object or not.

8) .close(callback)

Close the currently opened database.

  • callback - callback (err)
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

9) .closeSync()

Synchronously close the currently opened database.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

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

var conn = ibmdb.openSync(connString, option);
conn.closeSync();

10) .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=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(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) {
      if( err ) console.log(err);
      else result.closeSync();

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

11) .prepareSync(sql)

Synchronously prepare a statement for execution.

  • sql - SQL string to prepare

Returns a Statement object

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  var stmt = conn.prepareSync("select * from employee where empid = ?");

  //Bind and Execute the statment asynchronously
  stmt.execute([142], function (err, result) {
    data = result.fetchAllSync();
    console.log(data);
    result.closeSync();
    stmt.closeSync();

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

12) .bind(bindingParameters, callback)

Binds the parameters for prepared statement.

  • bindingParameters - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail.

  • callback - callback (err)

  • For ARRAY INSERT - Each value should be an array of size passed as ArraySize in query() APIs or equal to the value of attribute SQL_ATTR_PARAMSET_SIZE set using setAttr() APIs for prepared statement.

13) .bindSync(bindingParameters)

Binds the parameters for prepared statement synchronously. If bindSync() is used, then no need to pass bindingParameters to next execute() or executeSync() statement.

  • bindingParameters - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail.

  • For ARRAY INSERT - Each value should be an array of size passed as ArraySize in query() APIs or equal to the value of attribute SQL_ATTR_PARAMSET_SIZE set using setAttr() APIs for prepared statement.

14) .execute([bindingParameters], callback)

Execute a prepared statement.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail.
  • callback - callback (err, result, outparams) outparams - will have result for INOUT and OUTPUT parameters of Stored Procedure.
  • For ARRAY INSERT - Statement attribute SQL_ATTR_PARAMSET_SIZE must be set before calling execute() API.

Returns a Statement object via the callback

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table mytab (id int, photo BLOB(30K))");
  conn.prepare("insert into mytab (id, photo) VALUES (?, ?)", function (err, stmt) {
    if (err) {
      //could not prepare for some reason
      console.log(err);
      return conn.closeSync();
    }

    // Create params object
    var img = {ParamType:"FILE", DataType: "BLOB", "Data": "smile.jpg"};

    //Bind and Execute the statment asynchronously
    stmt.execute([ 42, img ], function (err, result) {
      if( err ) console.log(err);
      else result.closeSync();

      //Close the connection
      stmt.close(function(err){
        if(err){
          console.log(err)
        }
        conn.close(function(err){});
      });
    });
  });
});

15) .executeSync([bindingParameters])

Execute a prepared statement synchronously.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail. Instead of passing bindingParameters to executeSync(), parameters can also be binded using bind() or bindSync() APIs.
  • For ARRAY INSERT - Statement attribute SQL_ATTR_PARAMSET_SIZE must be set before calling execute() API.

Returns a Statement object. If prepared statement is a stored procedure with INOUT or OUT parameter, executeSync() returns an array of two elements in the form [stmt, outparams]. The first element of such array is an Statement object and second element is an Array of INOUT and OUTPUT parameters in sequence.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  var stmt = conn.prepareSync("select empname from emptable where empid = ?");

  //Bind and Execute the statment asynchronously
  var result = stmt.executeSync([142]);
  var data = result.fetchAllSync({fetchMode:3}); // Fetch data in Array mode.
  console.log(data);
  result.closeSync();
  stmt.closeSync();

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

16) .executeNonQuery([bindingParameters], callback)

Execute a non query prepared statement and returns the number of rows affected in a table by the statement.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail.
  • callback - callback (err, affectedRowCount)
  • For ARRAY INSERT - Statement attribute SQL_ATTR_PARAMSET_SIZE must be set before calling execute() API.

It returns the number of rows in a table that were affected by an UPDATE, an INSERT, a DELETE, or a MERGE statement issued against the table, or a view based on the table. If no rows are affected, it returns -1 via the callback function.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table mytab (id int, text varchar(30))");
  conn.prepare("insert into mytab (id, text) VALUES (?, ?)", function (err, stmt) {
    if (err) {
      console.log(err);
      return conn.closeSync();
    }

    //Bind and Execute the statment asynchronously
    stmt.executeNonQuery([ 42, 'hello world' ], function (err, ret) {
      if( err ) console.log(err);
      else console.log("Affected rows = " + ret);

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

17) .executeNonQuerySync([bindingParameters])

Execute a non query prepared statement synchronously and returns the number of rows affected in a table by the statement.

  • bindingParameters - OPTIONAL - An array of values that will be bound to any '?' characters in prepared sql statement. Values can be array or object itself. Check bindingParameters doc for detail.
  • For ARRAY INSERT - Statement attribute SQL_ATTR_PARAMSET_SIZE must be set before calling execute() API.

It returns the number of rows in a table that were affected by an UPDATE, an INSERT, a DELETE, or a MERGE statement issued against the table, or a view based on the table. If no rows are affected, it returns -1.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table mytab (id int, text varchar(30))");
  conn.prepare("insert into mytab (id, text) VALUES (?, ?)", function (err, stmt) {
    if (err) {
      console.log(err);
      return conn.closeSync();
    }

    //Bind and Execute the statment asynchronously
    var rowCount = stmt.executeNonQuerySync([ 42, 'hello world' ]);
    console.log("Affected rows = " + rowCount);

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

18) stmt.close(callback)

Close the currently opened statement object and free resources.

  • callback - callback (err)
    stmt.close(function(err) {
        if(err) console.log(err);
    });
    //OR
    stmt.close();
    //OR
    await stmt.close();

19) stmt.closeSync()

Synchronously close the currently opened statement object and free resources.

    stmt.closeSync();

20) .fetch(option, callback)

Fetch a row of data from ODBCResult object asynchronously.

  • option - OPTIONAL - Object type.

    • fetchMode - Format of returned row data. By default row data get returned in object form. option = {fetchMode:3} or option = {fetchMode: ibmdb.FETCH_ARRAY} will return row in array form. Default value of fetchMode is ibmdb.FETCH_OBJECT.
    • When option = {fetchMode : 0} or {fetchMode: ibmdb.FETCH_NODATA} is used, fetch() API do not return any result and application need to call result.getData() or result.getDataSync() API to retrieve data for a column.

  • callback - callback (err, row). When no callback function is passed, fetch() will return Promise.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  conn.prepare("select * from hits", function (err, stmt) {
    if (err) {
      //could not prepare for some reason
      console.log(err);
      return conn.closeSync();
    }
    stmt.execute(function (err, result) {
      if( err ) console.log(err);
      result.fetch((err, row) => {
          if(err) { console.log(err); }
          else {
            console.log("Row1 = ", row);
            result.fetch({fetchMode:ibmdb.FETCH_ARRAY}).then(row => {
              console.log("Row2 = ", row);
              result.closeSync();
              conn.querySync("drop table hits");
              //Close the connection
              conn.close(function(err){console.log("Connection Closed.");});
            }).catch(err => console.log(err));
          }
      });
    });
  });
});

21) .fetchSync(option)

Fetch a row of data from ODBCResult object synchronously.

  • option - OPTIONAL - Object type.
    • fetchMode - Format of returned row data. By default row data get returned in object form. option = {fetchMode:3} or option = {fetchMode: ibmdb.FETCH_ARRAY} will return row in array form. Default value of fetchMode is ibmdb.FETCH_OBJECT.
    • When option = {fetchMode : 0} or {fetchMode: ibmdb.FETCH_NODATA} is used, fetch() API do not return any result and application need to call result.getData() or result.getDataSync() API to retrieve data for a column.
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  var stmt = conn.prepareSync("select * from hits");
  var result = stmt.executeSync();
  var data = 0;
  while( data = result.fetchSync({fetchMode:3}) ) {
    console.log(data);
  }
  result.closeSync();
  conn.querySync("drop table hits");
  conn.closeSync();
});

22) .fetchAll(option, callback)

Fetch all rows from ODBCResult object asynchronously for the executed statement.

  • option - OPTIONAL - Object type.

    • fetchMode - Format of returned row data. By default row data get returned in object form. option = {fetchMode:3} will return rows in array form. {fetchMode:4} - return rows in object form.

  • callback - callback (err, data, noOfColumns)

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  conn.prepare("select * from hits", function (err, stmt) {
    if (err) {
      //could not prepare for some reason
      console.log(err);
      return conn.closeSync();
    }
    stmt.execute(function (err, result) {
      if( err ) console.log(err);
      result.fetchAll({fetchMode:4}, function (err, data, colcount) {
          if(err) { console.log(err); }
          else {
            console.log("Data = ", data);
            console.log("No of columns = ", colcount);
          }
          result.closeSync();
          conn.querySync("drop table hits");
          //Close the connection
          conn.close(function(err){console.log("Connection Closed.");});
      });
    });
  });
});

23) .fetchAllSync(option)

Fetch all rows from ODBCResult object Synchronously for the executed statement.

  • option - Optional object to specify return type of data. By default row data get returned in object form. option = {fetchMode:3} will return row in array form.
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  var stmt = conn.prepareSync("select * from hits");
  var result = stmt.executeSync();
  var data = result.fetchAllSync();
  console.log("Fetched data = ", data);
  result.closeSync();
  conn.querySync("drop table hits");
  conn.closeSync();
});

For example of prepare once and execute many times with above fetch APIs, please see test file test-fetch-apis.js.

24) .getData(colNum, Size, callback)

Retrive data for colNum of specified size from ODBCResult object asynchronously.

  • colNum - Integer - Column Number in the resultset starting from 1.

  • Size - Integer - Size of the data being retrieved. For fixed length data, it get ignored.

  • callback - callback (err, row). When no callback function is passed, getData() will return Promise.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  var stmt = conn.prepareSync("select * from hits");
  var result = stmt.executeSync();
  result.fetch({fetchMode:0})
      .then(() => {
        return result.getData(1, 4);
      }).then(data => {
        console.log(data);
        return result.getData(1, 5);
      }).then(data => {
        console.log(data);
        return result.getData(2, 5);
      }).then(data => {
        console.log(data);
        return result.getData(3, 5);
      }).then(data => {
        console.log(data);
      }).catch(err => console.log(err));

See test file test-fetch-apis.js for detail example.

25) .getDataSync(colNum, Size)

Retrive data for colNum of specified size from ODBCResult object synchronously.

  • colNum - Integer - Column Number in the resultset starting from 1.

  • Size - Integer - Size of the data being retrieved. For fixed length data, it get ignored.

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn,function(err,conn){
  conn.querySync("create table hits (col1 varchar(40), col2 int)");
  conn.querySync("insert into hits values ('something', 42)");
  conn.querySync("insert into hits values ('für', 43)");
  var stmt = conn.prepareSync("select * from hits");
  var result = stmt.executeSync();
  console.log(result.fetchSync({fetchMode:0}));
  console.log("First Row Data = ");
  console.log(result.getDataSync(1, 4));
  console.log(result.getDataSync(1, 5));
  console.log(result.getDataSync(2, 5));
  console.log(result.getDataSync(3, 5));
  result.fetchSync({fetchMode:0});
  console.log("Second Row Data = ");
  console.log(result.getDataSync(1, 4));
  console.log(result.getDataSync(1, 5));
  console.log(result.getDataSync(2, 5));
  console.log(result.getDataSync(3, 5));
  result.closeSync();
  conn.closeSync();
}

See test file test-fetch-apis.js for detail example.

26) result.close(callback)

Close the currently opened ODBC Result object and free resources.

  • callback - callback (err)
    result.close(function(err) {
        if(err) console.log(err);
    });
    //OR
    result.close();
    //OR
    await result.close();

27) result.closeSync()

Synchronously close the currently opened ODBC Result object and free resources.

    result.closeSync();

28) .beginTransaction(callback)

Begin a transaction

  • callback - callback (err)

29) .beginTransactionSync()

Synchronously begin a transaction

30) .commitTransaction(callback)

Commit a transaction

  • callback - callback (err)
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

31) .commitTransactionSync()

Synchronously commit a transaction

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

32) .rollbackTransaction(callback)

Rollback a transaction

  • callback - callback (err)
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

33) .rollbackTransactionSync()

Synchronously rollback a transaction

var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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

34) .setIsolationLevel(isolationLevel)

Synchronously sets the default isolation level passed as argument. It is only applicable when the default isolation level is used. It will have no effect if the application has specifically set the isolation level for a transaction.

  • isolationLevel: An integer representing the isolation level to be set. Its value must be only - 1|2|4|8|32. For details check this doc.
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.open(cn, function(err,conn) {
  conn.setIsolationLevel(2);  // SQL_TXN_READ_COMMITTED
  conn.setIsolationLevel(4); // SQL_TXN_REPEATABLE_READ
  conn.querySync("create table mytab1 (c1 int, c2 varchar(10))");
});

35) .getColumnNamesSync()

Synchronously retrieve the name of columns returned by the resulset. It operates on ODBCResult object.

  conn.querySync("insert into mytab1 values ( 5, 'abc')");
  conn.prepare("select * from mytab1", function (err, stmt) {
    stmt.execute(function(err, result) {
      console.log("Column Names = ", result.getColumnNamesSync());
      result.closeSync(); conn.closeSync(); }); });

36) .getColumnMetadataSync()

Synchronously retrieve the metadata about columns returned by the resulset. It operates on ODBCResult object.

  conn.querySync("insert into mytab1 values ( 5, 'abc')");
  conn.prepare("select * from mytab1", function (err, stmt) {
    stmt.execute(function(err, result) {
      console.log("Column Names = ", result.getColumnNamesSync());
      console.log("Column Meta Data = ", result.getColumnMetadataSync());
      console.log("Fetched Data = ", result.fetchAllSync() );
      result.closeSync();
      conn.closeSync();
    });
  });

37) .getSQLErrorSync()

Synchronously retrieve the sqlerror message and codes for last instruction executed on a statement handle using SQLGetDiagRec ODBC API. It operates on ODBCResult object.

  conn.querySync("insert into mytab1 values ( 5, 'abc')");
  conn.prepare("select * from mytab1", function (err, stmt) {
    stmt.execute(function(err, result) {
      console.log("Fetched Data = ", result.fetchAllSync() );
      var problem = result.getSQLErrorSync();
      if (problem.sqlcode < 0) { // This sqlcode is negative and is therefore an error
        console.log("SQLError = ", problem);
      } else if (problem.sqlcode > 0) { // This sqlcode is positive and is therefore a warning
        console.log("SQLWarning = ", problem);
      }
      result.closeSync();
      conn.closeSync();
    });
  });

38) .debug(value)

Enable console logs. debug(true) do not log params that may have sensitive data. Support for debug(2) added to dump bind params.

  • value - true/false/2. Any truthy value enables debug mode.
var ibmdb = require("ibm_db")
  , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

ibmdb.debug(true);  // **==> ENABLE CONSOLE LOGS, but do not log params. <==**
ibmdb.debug(2);     // **==> ENABLE CONSOLE LOGS and log parameter values too if passed. <==**

ibmdb.open(cn, function (err, connection) {
    if (err)
    {
        console.log(err);
        return;
    }
    connection.query("select 1 from sysibm.sysdummy1", function (err1, rows) {
        if (err1) console.log(err1);
        else console.log(rows);

        ibmdb.debug(false);  // Disable console logs.

        connection.close(function(err2) {
            if(err2) console.log(err2);
        });
    });
});

39) .executeFileSync(sqlFile,[delimiter],[outputFile])

Synchronously issue multiple SQL query from the file to the database that is currently open.

  • sqlFile - sqlFile input should be Full Path of the file. sqlFile can be an Object in the form { "sql": sqlFile, "delimiter": delimiter, "outputfile": outputfile }. "sql" field is mandatory in Object.

  • delimiter - (OPTIONAL only incase of default delimiter ;) - If the sqlFile contains other delimiters it is mandatory to mention delimiter. Delimiter splits mutliple query in the sqlFile.

  • outputfile - OPTIONAL - Outputfile should be Full Path of the file and only select queries data will be copied to outputfile splitted by the delimiter. If the outputfile already exists it will be overwritten. If the outputfile is not mentioned the result will be returned splitted by the delimiter.

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.executeFileSync('sample2.txt', '%','out.txt');
    var rows = conn.executeFileSync('sample2.txt', '%');
    console.log(rows)
});

40) .executeFile(sqlFile,[delimiter],[outputFile])

Asynchronously issue multiple SQL query from the file to the database that is currently open.

  • sqlFile - sqlFile input should be Full Path of the file. sqlFile can be an Object in the form { "sql": sqlFile, "delimiter": delimiter, "outputfile": outputfile }. "sql" field is mandatory in Object.

  • delimiter - (OPTIONAL only incase of default delimiter ;) - If the sqlFile contains other delimiters it is mandatory to mention delimiter. Delimiter splits mutliple query in the sqlFile.

  • outputfile - OPTIONAL - Outputfile should be Full Path of the file and only select queries data will be copied to outputfile splitted by the delimiter. If the outputfile already exists it will be overwritten. If the outputfile is not mentioned the result will be returned splitted by the delimiter.

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.executeFile('sample3.txt', '@', 'out.txt', function (err, rows) {
        if (err) {
            console.log(err);
        } else {
            console.log(rows);
        }
    });
     conn.executeFile('sample3.txt', '@', function (err, rows) {
        if (err) {
            console.log(err);
        } else {
            console.log(rows);
        }
    });
});

41) .setAttr(attributeName, value, callback)

Set connection and statement level attributes asynchronously. It requires attributeName and corresponding value. conn.setAttr() - sets connection level attributes post connection. stmt.setAttr() - sets statement level attributes post creation of statement handle.

await conn.setAttr("SQL_ATTR_INFO_USERID", 'appuser');

stmt.setAttr(ibmdb.SQL_ATTR_PARAMSET_SIZE, 4, function(err, result) {
    if(err) { console.log(err); stmt.closeSync(); }
    else { ... }
});

42) .setAttrSync(attributeName, value)

Set connection and statement level attributes synchronously. It requires attributeName and corresponding value. conn.setAttrSync() - sets connection level attributes post connection. stmt.setAttrSync() - sets statement level attributes post creation of statement handle.

conn.setAttrSync(ibmdb.SQL_ATTR_INFO_APPLNAME, 'mynodeApp');

var err = stmt.setAttrSync(ibmdb.SQL_ATTR_PARAMSET_SIZE, 5);
err = stmt.setAttrSync(ibmdb.SQL_ATTR_QUERY_TIMEOUT, 50);
err = stmt.setAttrSync(3, 2); //SQL_ATTR_MAX_LENGTH = 3

43) .getInfo(infoType, [infoLength], callback)

Asynchronously retrieve the general information about the database management system (DBMS) that the application is connected to. It also retrives the information about ODBC driver used for connection.

  • infoType - The type of information that is required. The possible values for this argument are described in Information returned by SQLGetInfo(). The value for this argument should be an integer value if macro is not defined in ibm_db/lib/climacros.js file.

  • infoLength - OPTIONAL - Length of the string value to be retrieved. If not provided, getInfo() can return a string value of maximum size 255 bytes.

  • callback - callback (error, value)

    Depending on the type of information that is being retrieved, 2 types of information can be returned:

    • String value
    • Number value
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.getInfo(ibmdb.SQL_DBMS_NAME, function(error, data) {
      if(error) console.log(error);
      else console.log("SQL_DBMS_NAME(Server Type) = ", data);
      conn.closeSync();
    });
});

44) .getInfoSync(infoType, [infoLength])

Synchronously retrieve the general information about the database management system (DBMS) that the application is connected to. It also retrives the information about ODBC driver used for connection.

  • infoType - The type of information that is required. The possible values for this argument are described in Information returned by SQLGetInfo(). The value for this argument should be an integer value if macro is not defined in ibm_db/lib/climacros.js file.

  • infoLength - OPTIONAL - Length of the string value to be retrieved. If not provided, getInfo() can return a string value of maximum size 255 bytes.

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

ibmdb.open(cn, function(err, conn)
{
    let serverVersion = conn.getInfoSync(ibmdb.SQL_DBMS_VER);
    console.log("SQL_DBMS_VER(Server Version) = ", serverVersion);
    conn.closeSync();
});

45) .getTypeInfo(dataType, callback)

Asynchronously retrieve the information about the SQL data types that are supported by the connected database server. If ibmdb.SQL_ALL_TYPES is specified, information about all supported data types would be returned in ascending order by TYPE_NAME. All unsupported data types would be absent from the result set.

  • dataType - The SQL data type being queried. The supported values for this argument are described in SQLGetTypeInfo function (CLI) - Get data type information. The value for this argument should be an integer value if macro is not defined in ibm_db/lib/climacros.js file.

  • callback - callback (error, result)

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.getTypeInfo(ibmdb.SQL_BLOB, function(error, result) {
      if(error) console.log(error);
      else console.log("SQL_BLOB Data Type Info = ", result);
      conn.closeSync();
    });
});

async function main()
{
    let conn = await ibmdb.open(cn);
    let data = await conn.getTypeInfo(ibmdb.SQL_ALL_TYPES);
    console.log("All supported data types info = ", data);
    await conn.close();
}

46) .getTypeInfoSync(dataType)

Synchronously retrieve the information about the SQL data types that are supported by the connected database server. If ibmdb.SQL_ALL_TYPES is specified, information about all supported data types would be returned in ascending order by TYPE_NAME. All unsupported data types would be absent from the result set.

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

ibmdb.open(cn, function(err, conn) {
    let result = conn.getTypeInfoSync(ibmdb.SQL_BIGINT);
    console.log("SQL_BIGINT Data Type Info = ", result);
    conn.closeSync();
});

47) .getFunctions(functionId, callback)

Asynchronously determines whether a specific CLI or ODBC function is supported. This allows applications to adapt to varying levels of support when connecting to different database servers.

  • functionId - The value for a function being queried. The value for this argument should be an integer value if macro is not defined in ibm_db/lib/climacros.js file.

  • callback - callback (error, value)

  • value will have only two values: true or false if a valid function id is passed. For ibmdb.ALLFUNCTIONS or 0, it returns an object of all supported functions with true/false value.
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.getFunctions(ibmdb.SQLCONNECT, function(error, value) {
      if(error) console.log(error);
      else console.log("Is SQLConnect supported : ", value);
      conn.closeSync();
    });
});

48) .getFunctionsSync(functionId)

Synchronously determines whether a specific CLI or ODBC function is supported. This allows applications to adapt to varying levels of support when connecting to different database servers.

  • functionId - The value for a function being queried. The value for this argument should be an integer value if macro is not defined in ibm_db/lib/climacros.js file.
var ibmdb = require("ibm_db")
  , cn = "DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password";

ibmdb.open(cn, function(err, conn)
{
    let fExists = conn.getFunctionsSync(ibmdb.SQLFREECONNECT);
    console.log("Function SQLFreeConnect Exist : ", fExists);
    conn.closeSync();
});

Create and Drop Database APIs

.createDbSync(dbName, connectionString, [options])

To create a database (dbName) through Node.js application.

  • dbName - The database name.
  • connectionString - The connection string for your database instance.
  • options - OPTIONAL - Object type.
    • codeSet - Database code set information.
    • mode - Database logging mode (applicable only to "IDS data servers").
var ibmdb = require("ibm_db");
// Connection string without "DATABASE" keyword and value.
var cn = "HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password";

var DB_NAME = "TESTDB";

var createDB = ibmdb.createDbSync(DB_NAME, cn);

if(createDB) {
  console.log("Database created successfully.");
  // Connection string with newly created "DATABASE" name.
	var conStr = cn + ";" + "DATABASE=" + DB_NAME;

	ibmdb.open(conStr, function(err, conn) {
		if(err) console.log(err);
		else console.log("Database connection opened.");
	});
}

Note: This API is not supported for Db2 on z/OS servers. Given that connection to Db2 on z/OS is to a specific subsystem, this API is not applicable.

.dropDbSync(dbName, connectionString)

To drop a database (dbName) through node.js application.

  • dbName - The database name.
  • connectionString - The connection string for your database instance.
var ibmdb = require("ibm_db");
// Connection string without "DATABASE" keyword and value.
var cn = "HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password";

var DB_NAME = "TESTDB";

var dropDB = ibmdb.dropDbSync(DB_NAME, cn);

if(dropDB) {
  console.log("Database dropped successfully.");
}

Note: This API is not supported for Db2 on z/OS servers. Given that connection to Db2 on z/OS is to a specific subsystem, this API is not applicable.

Connection Pooling APIs

node-ibm_db reuses its own connection pooling mechanism. The ibm_db 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 get added to the list of available connections immediately. Such connection will be used the next time you call Pool.open() for the same connection string.

For applications using multiple connections simultaneously, it is recommended to use Pool.open instead of ibmdb.open.

  1. .open(connectionString, callback)
  2. .openSync(connectionString)
  3. .close(callback)
  4. .closeSync()
  5. .init(N, connStr)
  6. .initAsync(N, connStr, callback)
  7. .setMaxPoolSize(N)

1) .open(connectionString, callback)

Get a Database instance which is already connected to connectionString

  • connectionString - The connection string for your database
  • callback - callback (err, db)
var Pool = require("ibm_db").Pool
	, pool = new Pool()
    , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

pool.open(cn, function (err, db) {
	if (err) {
		return console.log(err);
	}
    console.log("Connection opened successfully.");
    console.log("Data = ", conn.querySync("select 1 as c1 from sysibm.sysdummy1"));
    conn.close(function (error) { // RETURN CONNECTION TO POOL
        if (error) {
          console.log("Error while closing connection,", error);
          return;
        }
    });
});

2) .openSync(connectionString)

Get a Database connection synchronously which is already connected to connectionString

  • connectionString - The connection string for your database

Check test-pool-close.js for example.

var Pool = require("ibm_db").Pool
	, pool = new Pool()
    , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

try {
    var conn = pool.openSync(connectionString);
} catch(error) {
    console.log("Unable to open connection,", error);
    return;
}
console.log("Connection opened successfully.");
console.log("Data = ", conn.querySync("select 1 as c1 from sysibm.sysdummy1"));
var err = conn.closeSync(); // RETURN DB CONNECTION TO POOL.
if (err) {
    console.log("Error while closing connection,", err);
    return;
}

3) .close(callback)

Close all connections in the Pool instance asynchronously.

  • callback - callback (err)
var Pool = require("ibm_db").Pool
	, pool = new Pool()
    , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

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 connection.
    //but all we will do now is close the whole pool using close() API.
    //Use conn.close() to return the connection back to pool for next use.

    pool.close(function () {
        console.log("All connections in the pool are closed.");
    });
});

4) .closeSync()

Close all connections in the Pool instance synchronously. Check test-pool-close.js for example.

var Pool = require("ibm_db").Pool
	, pool = new Pool()
    , cn = "DATABASE=dbname;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=dbuser;PWD=xxx";

try {
    var conn = pool.openSync(connectionString);
} catch(error) {
    console.log("Unable to open connection,", error);
    return;
}
console.log("Connection opened successfully.");

//conn is now an open database connection and can be used like normal connection.
//but all we will do now is close the whole pool using closeSync() API.
//Use conn.closeSync() to return the connection back to pool for next use.

var error = pool.closeSync();
if (error) { console.log("Error while closing pool,", error); return; }
console.log("All connections in the pool are closed.");

5) .init(N, connStr)

Initialize Pool with N no of active connections using supplied connection string. It is a synchronous API.

  • N - No of connections to be initialized.
  • connStr - The connection string for your database
var ret = pool.init(5, connStr);
if(ret != true)
{
    console.log(ret);
    return false;
}

pool.open(connStr, function(err, db) { ...

Check test-max-pool-size.js for example.

6) .initAsync(N, connStr, callback)

Initialize Pool with N no of active connections using supplied connection string. It is an asynchronous API.

  • N - No of connections to be initialized.
  • connStr - The connection string for your database or a JSON Object with connection information
  • callback - callback (err)
pool.initAsync(5, connStr, function(err) {
  if(err) {
    console.log(err);
    return false;
  }
  pool.open(connStr, function(err, db) { ... });
});

try {
  await pool.initAsync(1, cn);
  let conn = await pool.open(cn);
  let data = await conn.query("select 1 from sysibm.sysdummy1");
  console.log("data = ", data);
} catch(err) {console.log(err);}

Check test-asyc-await.js for example.

7) .setMaxPoolSize(N)

Number of maximum connection to database supported by current pool.

  • N - No of maximum connections in the pool. If we call pool.open() or openSync() API and N connections are already in use, subsequent connection request will be queued and wait till connection timeout, so that if any connection get closed before timeout, the open request will be served. Check test-max-pool-size.js for example.
pool.setMaxPoolSize(20);
pool.open(connStr, function(err, db) { ...

bindingParameters

Bind arguments for each parameter marker(?) in SQL query. These parameters can be used with query(), querySync, bind(), execute() APIs. bindingParameters is an array of Values like: [val1, val2, ...] Each value in itself can be an array or Object holing multiple bind options. If parameters are not an integer or string, it is recomended to pass an Object with different bind options. The object can have following keys:

{"ParamType":"INOUT", CType:"BINARY", SQLType:"BLOB",DataType: "BLOB", Data:imgfile, Length:50}

Either SQLType or DataType must be used. If SQLType is used, DataType will be ignored.

  • ParmType: Type of the Parameter. Supported Values are:
  • INPUT - Bind the parameter using SQL_PARAM_INPUT(defined in ibm_db/installer/clidriver/include/sqlext.h file). It is used as input value and it is the default value, if you don't use this key in object.
  • OUTPUT - Bind the parameter using SQL_PARAM_OUTPUT. It is basically used for Stored Procedure call which has output parameters.
  • INOUT - Bind the parameter using SQL_PARAM_INPUT_OUTPUT. It is also used for Stored Procedure call.
  • FILE - It tells the Data is a filename that contains actual data to load. If you want to load an image to database, use this input type along with DataType as BLOB for binary file. f.e. {ParamType: "FILE", DataType: "BLOB", Data: "mypic.jpg"}
  • ARRAY - It tells the Data is an Array of same type and size. It must be used for Array Insert i.e. to insert data for multiple rows using single execute. If one parameter is of type ARRAY, all parameters passed to an API must be of type ARRAY and of equal size. Check test/test-array-insert.js for example. Use "Length: " in param Object for unequal size of data. Default value is the length of first member of Array.

  • CType: C Data type of the parameter to be bound. Default value is CHAR.

  • SQLType: Data type of the parameter on Server. It is actually the column Type of the parameter. Default value is CHAR

  • DataType: Same as SQLType. Use either SQLType or DataType. Added for simple name. Default Value is CHAR.

  • Data: Its value is actuall data for the parameter. For binary data, it should represent the full buffer containing binary data. For ParamType:"FILE", it must have the filename on disc that contains data. It is mandatory key in the data Object.

  • Length: It denotes the buffer length in byte to store the OUT Pamameter value when ParamType is INOUT or OUTPUT in a Stored Procedure call..

  • Few example of bidningParameters that we can use in node.js program:

[18, 'string']
[3, 5, 3.8, 'string', 9.1]
[18, [1, 1, 1, 'string']]
[[1, 1, 1, 18], [1, 1, 1, 'string']]
[18, {ParamType:"INPUT", "Data": "string"}]
[18, {ParamType:"INPUT", CType: "CHAR", SQLType: "CHAR", "Data": "string"}]
[38, {ParamType:"INPUT", SQLType: "CHAR", "Data": "string"}]
[38, {ParamType:"INPUT", DataType: "CHAR", "Data": "string"}]
[[1,1,1,38], {"Data": "string"}]
[38, {ParamType:"INPUT", DataType: "CLOB", "Data": var1}] - here var1 contains full CLOB data to be inserted.
[38, {ParamType:"FILE", DataType: "CLOB", "Data": filename}] - here filename is the name of file which has large character data.
[{ParamType:"ARRAY", DataType:1, Data:[4,5,6,7,8]}, {ParamType:"ARRAY", DataType:"DOUBLE", Data:[4.1,5.3,6.14,7,8.3]}] - for Array insert.

The values in array parameters used in above example is not recommened to use as it is dificult to understand. These values are macro values from ODBC specification and we can directly use those values. To understand it, see the SQLBindParameter documentation for DB2.

Pass bind parameters as Object if you want to insert an Array or BLOB or CLOB data to DB2. Check below test files to know how to insert a BLOB and CLOB data from buffer and file:

  • test-blob-insert.js - To insert a BLOB and CLOB data using memory buffer. Application need to read the file contents and then use as bind parameter.
  • test-blob-file.js - To insert an image file and large text file directly to database without reading it by application.
  • test-array-insert.js - For Array Insert.

CALL Statement

  • If stored procedure has any OUT or INOUT parameter, always call it with parmeter markers only. i.e. pass the input values using bind params.

  • Pass the Bind Params as objects only.

  • If SP has result set to return, it will be returned in the array after out params. f.e. if SP has 2 out params and it returns 2 result set too, the result returned by query() or querySync() would be in the form [outValue1, outValue2, resultSet1, resultSet2]. Each resultset would be an array of row objects.

  • test-call-stmt.js - Example using conn.querySync().

  • test-call-async.js - Example using conn.query().

  • test-sp-resultset.js - Example using Out Params and Result Set using query() and querySync() APIs.

  • test-sp-resultset-execute.js - Example using Out Params and Result Set using prepare() and execute() APIs.