ibm/nodejs-idb-connector

A JavaScript (Node.js) library for communicating with Db2 for IBM i, with support for queries, procedures, and much more. Uses traditional callback-style syntax

Node.js iDB Connector for IBM i

The Node.js iDB Connector is an IBM i Node.js Db2 driver open source project from IBM

Installation

    npm i idb-connector

NOTE This package only installs on IBM i systems.

Then you can require in your code, as shown below.

    const db = require('idb-connector');

Quick Example

Example 1: Fetching data using the exec() API

    const {dbconn, dbstmt} = require('idb-connector');

    const sSql = 'SELECT STATE FROM QIWS.QCUSTCDT';
    const connection = new dbconn();
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    statement.exec(sSql, (x) => {
      console.log(JSON.stringify(x));
      statement.close();
      connection.disconn();
      connection.close();
    });

Example 2: Fetching data using the fetchAll() API

    const {dbconn, dbstmt} = require('idb-connector');

    const sSql = 'SELECT STATE FROM QIWS.QCUSTCDT';
    const connection = new dbconn();
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    statement.prepare(sSql, () => {
      statement.execute(() => {
        statement.fetchAll((x) => {
          console.log(`Result is : ${JSON.stringify(x)}`);
          statement.close();
        });
      });
    });

Example 3: Call stored procedures

    const {dbconn, dbstmt} = require('idb-connector');

    const sql = 'CALL QXMLSERV.iPLUG512K(?,?,?,?)';
    const connection = new dbconn();
    connection.conn('*LOCAL');
    const statement = new dbstmt(connection);

    const ipc = '*NA';
    const ctl = '*here';
    const xmlIn = `<xmlservice><sh>system 'wrksbs'</sh></xmlservice>`;
    const xmlOut = '';

    statement.prepare(sql, () => {
      statement.bindParameters([ipc, ctl, xmlIn, xmlOut], () => {
        statement.execute((out) => {
          for (let i = 0; i < out.length; i += 1) {
            console.log(out[i]);
          }
          statement.close();
          connection.disconn();
          connection.close();
        });
      });
    });

API Reference

Db2 for i Access APIs

Change Log

View CHANGELOG.md file.

Build

Note that building isn't necessary for end-users and is more for developers looking to compile the native Node.js extensions (C code).

    git clone [email protected]:IBM/nodejs-idb-connector.git
    cd nodejs-idb-connector
    npm install --build-from-source

Build Dependencies

Note: sqlcli header files, GCC, and Python are required to compile the code.

    yum install sqlcli-devel
    yum group install 'Development tools' 
    yum install python2

License

MIT

Contributing

Please read the contribution guidelines.

nodejs-idb-connector's Issues

Retrieving diagnostics

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

Currently you can retrieve diagnostics via DbStmt::StmtError. I am wondering if this API should be renamed to convey that it can be used for retrieving diagnostics even when there isn't an error. Or maybe retain DbStmt::StmtError as an alias function to a new function named DBStmt::getStmtDiag.

Thoughts?

Updating docs/README.md

Original report by me.

---

This issue is a follow up to Issue #24

Currently this is a straight port from developerWorks.

I'm creating this issue to list what I think should be updated & for others to also make suggestions.

Examples

1. replace require('/QOpenSys/QIBM/ProdData/OPS/Node4/os400/db2i/lib/db2a') with
   require('idb-connector')

2. replace var declarations with let and const as appropriate.

3. use single quotes instead of double quotes

4. Remove concurrent query example - documented here that this can be bad!

#Docs

1. update execute() syntax to show execute(out, error)

2. Some of the methods have duplicates for their syntaxes:

For example:

prepare(string SQL, function Callback())

prepare(string SQL, function Callback(Error))

Would change to only show: prepare(string SQL, function Callback(Error)).

Any CCSID other than 1208 fails to connect to database

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

I can connect just fine to the database with CCSID 1208 but can't insert data due to character set mismatch.

I set the CCSID via

process.env.DB2CCSID = '273';

but then the idb-connector won't even connect to my database printing out an unreadable error:

Error: SQLSTATE=����� SQLCODE=-950 م���������@ā�������@*LOCAL@�����@��@兙��������@���@������������@ā�������K
    at Error (native)
    at connectToDb (/home/GUENEY/node/ibm.js:30:8)
    at Object.<anonymous> (/home/GUENEY/node/ibm.js:22:1)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)
    at Module.runMain (module.js:604:10)
    at run (bootstrap_node.js:394:7)

Cannot convert between code set (CCSID 819) and code set (CCSID 37)

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

Working on a customer's machine today and came across an error I have not seen before.

Anyone know what I can check?

**Scenario: **

• Fresh install of yum.
• Running in a chroot
• Fresh install of Node.js v8.x.
• Fresh install of idb-connector.
• DSPSYSVAL QCCSID is 37
• They've used Node.js v6.x with the original database connector successfully.

Node REPL:

$ node

>     var db = require('idb-connector');

    var sSql = 'SELECT STATE FROM QIWS.QCUSTCDT';

    var dbconn = new db.dbconn();

    dbconn.conn("*LOCAL");

    var stmt = new db.dbstmt(dbconn);

    stmt.exec(sSql, (x) => {
      console.log("%s", JSON.stringify(x));
      stmt.cloCannot convert between code set  (CCSID 1208) and code set  (CCSID 37)
seCannot convert between code set  (CCSID 819) and code set  (CCSID 37)
ERROR: SQLALLOCENV(-1)();Cannot convert between code set  (CCSID 819) and code set  (CCSID 37)
  undefined
> (node:2850) Warning: N-API is an experimental feature and could change at any time.
    var sSql = 'SELECT STATE FROM QIWS.QCUSTCDT';
undefined
>     var dbconn = new db.dbconn();
Cannot convert between code set  (CCSID 819) and code set  (CCSID 37)
bconn.disconnundefined
>     dbconn.conn("*LOCAL");
undefined
>     var stmt = new db.dbstmt(dbconn);
Error: SQLSTATE=PAERR SQLCODE=8013 The Dbconn Object is not connected
> 
>     stmt.exec(sSql, (x) => {
...       console.log("%s", JSON.stringify(x));
...       stmt.close();
...       dbconn.disconn();
...       dbconn.close();
...     });

utf-8 problem?

Original report by António Ramos (Bitbucket: ramstein74, GitHub: ramstein74).

---

when reading this string "PORCELANA PARA MAÇARICO DE IGNIÇ." from a table column in my iseries

i get this from a nodejs script

PORCELANA P/MAÇARICO DE IGNIèX"

can you help understand what is wrong with my code ?

var db2i = require("idb-connector");
var clc = require("colors-cli");
const express = require('express')
var bodyParser = require('body-parser');
const app = express()
const port = 8000
const error = clc.red;
const warn = clc.yellow;
const notice = clc.green;
app.use(bodyParser.json()); // support json encoded bodies
app.use(bodyParser.urlencoded({
extended: true
})); // support encoded bodies
app.post('/db2query', (req, res) => {
try {
var dbconn = new db2i.dbconn();
dbconn.setConnAttr(db2i.SQL_ATTR_DBC_SYS_NAMING, db2i.SQL_TRUE);
dbconn.conn("*LOCAL", "xxx", "xxx"); // connect to local database
let query = req.body.query;
let key = req.body.key;
if (key == "xxxxx") {
var stm = new db2i.dbstmt(dbconn);
stm.exec(query, function (result) {
res.send(result)
stm.close()
console.log(notice(new Date()+" Executada query\n"), (result));
})
} else {
res.send("error")
console.log(error(new Date()+" Tentativa ilegal de executar \n"), (query));
}
res.end
} catch (err) {
console.log(error(new Date()+" Erro no script"));
res.end("erro")
}
})
app.listen(port, () => console.log(NodeJs proxy listening on port ${port}!))

thank you

Using npm with QSECOFR

Original report by christophe LALEVEE (Bitbucket: lalevee, GitHub: Unknown).

---

Trying to use npm (node.js 6.9.1, npm 3.10.8) with QSECOFR user profile (remote ssh), I get following error

#!shell

# id
uid=0(qsecofr) gid=0

# npm root -g
Error: EPERM, Not owner
    at /QOpenSys/QIBM/ProdData/OPS/Node6/lib/node_modules/npm/node_modules/uid-number/uid-number.js:49:16
    at ChildProcess.exithandler (child_process.js:197:7)
    at emitTwo (events.js:106:13)
    at ChildProcess.emit (events.js:191:7)
    at maybeClose (internal/child_process.js:877:16)
    at Socket.<anonymous> (internal/child_process.js:334:11)
    at emitOne (events.js:96:13)
    at Socket.emit (events.js:188:7)
    at Pipe._handle.close [as _onclose] (net.js:498:12)

Doing the same with another user profile (*SECOFR class) works fine:

#!shell

$ npm root -g
/QOpenSys/QIBM/ProdData/OPS/Node6/lib/node_modules

how call sql procedure that returns result set?

Original report by Steve_Richter (Bitbucket: Steve_Richter, GitHub: Unknown).

---

the dbstmt exec method is not return a result set from an sql procedure. I am using it wrong?

When the stmt passed to the exec method is an SQL SELECT, I get back a result set. But when that statement is an SQL CALL, I do not get back a result set.

Here is the code that fails ( is just a modification of IBM sample code )

#!js

var db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');

var sql = "call qgpl.sample101 " ;

var dbconn = new db.dbconn();  // Create a connection object.
dbconn.conn("*LOCAL");  // Connect to a database.
var stmt = new db.dbstmt(dbconn);  // Create a statement object of the connection.
stmt.exec(sql, function(result)
{
  console.log("Result: %s", JSON.stringify(result));
  var fieldNum = stmt.numFields();
  console.log("There are %d fields in each row.", fieldNum);
  console.log("Name | Length | Type | Precise | Scale | Null");
  for(var i = 0; i < fieldNum; i++)
  {
    console.log("%s | %d | %d | %d | %d | %d", 
    stmt.fieldName(i), stmt.fieldWidth(i), stmt.fieldType(i), stmt.fieldPrecise(i), 
    stmt.fieldScale(i), stmt.fieldNullable(i));
  }

  delete stmt ;
  dbconn.disconn();  // Disconnect from the database.
  delete dbconn ;
});

and here is the code of the SQL procedure.

#!sql

create OR REPLACE procedure   qgpl.sample101       
(                                                  
)                                                  
                                                   
language sql                                       
DYNAMIC RESULT SETS 1                              
BEGIN atomic                                       
                                                   
DECLARE     C1 CURSOR FOR                          
select  decimal(2,3,0) linn, char('abc',80) text   
from       sysibm.sysdummy1                        
union all                                          
select  decimal(1,3,0) linn, char('efg',80) text   
from       sysibm.sysdummy1                        
order by   1 ;                                     
                                                   
  OPEN C1;                                         
                                                   
 SET RESULT SETS WITH RETURN TO CLIENT CURSOR C1 ; 
                                                   
END                                                

Integer values >= 10000 is mostly corrupted

Original report by Christian Jorgensen (Bitbucket: chrjorgensen, GitHub: chrjorgensen).

---

Sometimes the integer values returned by a SQL statement is corrupted. Some simple testing suggests, that the problem occurs when the value is 5 digits or more.

Running the attached sample node program will produce the following output:

ExecAsync().
SQLExecDirect(0): select * from (values int( 10000 )) as x (int_val)
SQLDescribeCol(1).
[0]sqlType[4]   colScale[0]     colPrecise[4]
SQLBindCol(1).
Select results: [{"INT_VAL":"10000/\u0019@"}]
SQLFreeStmt: stmth 3 [SQL_DROP]
SQLDisconnect: conn obj [1802f1870] handler [2]
SQLFreeConnect: conn obj [1802f1870] handler [2]

Run the sample program multiple times, and the result will sometimes be correct, but most of the times it will be corrupted.

I have tried several versions of idb-connector, but all versions have the same problem.

SELECT statement does not return BLOB string

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

Hey all,

I have a very simple table with four columns, one of the columns is of type BLOB(10485760). I populated the table with a couple sample data and when querying it via the ACS' SQL Editor, I get the following output:

7	FOO		S	FFD8FFEE0E0...(snipped)

the same statement called via the idb-connector returns the following object:

{ 
    ID: '7',
    COMMENT: 'FOO',
    TYPE: 'S',
    IMG: '' 
}

Is this is a bug or do I need to extract the blob string in a different manner?

Case-insensitive searching

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

Is there an equivalent to "exec sql SET OPTION SRTSEQ=*LANGIDSHR" as in an RPGLE program? This tells the program to perform case-insensitive searches when executing SQL. Could we run this before running a statement, or by setting a statement option? If this was a setting on a statement, not a connection that would be great. We can always use the upper() and lower() sql functions if necessary. Any help is appreciated.

Connection pooling

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

You can learn more of the need for connection pools by looking at this thread which includes the eventual solution. I also wrote an article about the topic in MCPressOnline.

This Issue is to discuss:

• Whether there is agreement to add connection pool capabilities to this repo (I believe it should be included).
• What features should be in a connection pool.
• Whether the code in this article is "good enough" for an initial implementation.
• Implications for the Node.js iToolkit if any.

UPDATE 2017-08-30*
There are discussions in this issue that will effect this issue.

Is it possible to build the idb-connector on Windows/Linux?

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

When I do

npm install idb-connector

on my Windows machine, node-gyp fails to build it.

Performance degrades with larger result sets.

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

Whether running SQL statements, or SP calls, performance degrades significantly with this connector as the number of rows being returned increases. We have a stored procedure that returns a user-specified number of rows. When calling the same Stored Procedure with Node.js and JDBC on Tomcat, 100 rows or fewer results in Node.js is faster. But, when calling with 1000 rows, Node.js response times were averaging 650 ms, and JDBC on Tomcat average 250 ms. Nearly all time for these https requests is spent on the stmt.execute() and stmt.fetchAll() for the stored procedure call. The performance monitor indicated very similar response times from the SQL engine between these two methods. This indicates that something may need to be tweaked in the connector, or perhaps there is a connection or statement attribute that I need to set. Please advise.

Open Sourcing the documentation

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

Documentation is often neglected by developers and open sourcing them in a repo like this one allows for people (like me) who enjoy documenting software to contribute to the project :-)

SQL_PARAM_INPUT not defined

Original report by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Several of the examples reference constants like db.SQL_PARAM_INPUT that are not actually defined.

Performance is slow with larger result sets

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

I've noticed that this connector is slower than jdbc in terms of handling the result sets and I have an idea. The idb connector is converting the result set to json, where as jdbc is not. I'm thinking that this may be the major difference in execution time that I'm seeing. I've tried turning debugging on for the connection but didn't get what I was hoping for.

I'd like someone (I'm willing to help with assistance) to modify dbconn.cc to accumulate time spent performing various functions. Specifically, how much time is being spent converting the odbc result to json. Then, add debug() statements to report these times to the console.

If we find that the json conversion is where a large majority of time is being spent, I would like the maintainers to explore alternate solutions.

Thanks!

Hapijs and iToolkit async issue

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

The following is a complete debug test of an issue encountered when trying to use Hapijs and iToolkit in async fashion. Using iToolkit in sync fashion works, and async doesn't.

Environment:

• IBM i v7.2
• Node v6.9.1 with latest SI64400 PTF installed.

Steps to recreate the issue:

$ export PATH=/QOpenSys/QIBM/ProdData/OPS/Node6/bin:$PATH
$ mkdir hapijs_itoolkit_bug && cd hapijs_itoolkit_bug
$ npm init
$ npm install hapi --save

Put the following in hapijs_itoolkit_bug/server.js

'use strict';

const Hapi = require('hapi');
const server = new Hapi.Server();
server.connection({ 
  host: '0.0.0.0', 
  port: process.env.PORT || 8000
});

server.route([
  {
    method: 'GET',
    path:'/itoolkit', 
    handler: function (request, reply) {
      var xt = require("/QOpenSys/QIBM/ProdData/OPS/Node6/os400/xstoolkit/lib/itoolkit");
      var conn = new xt.iConn("*LOCAL");
      var sysVal = "QCCSID";
      var outBuf = [
             [0, "10i0"],
             [0, "10i0"],
             ["", "10A"],
             ["", "1A"],
             ["", "1A"],
             [0, "10i0"],
             [0, "10i0"]
      ];
      var errno = [
             [0, "10i0"],
             [0, "10i0", {"setlen":"rec2"}],
             ["", "7A"],
             ["", "1A"]
      ];
      var pgm = new xt.iPgm("QWCRSVAL", {"lib":"QSYS"});
      pgm.addParam(outBuf, {"io":"out", "len":"rec1"});
      pgm.addParam(0, "10i0", {"setlen":"rec1"});
      pgm.addParam(1, "10i0");
      pgm.addParam(sysVal, "10A");
      pgm.addParam(errno, {"io":"both", "len" : "rec2"});
      conn.add(pgm);
      conn.run(function(str) {
        var results = xt.xmlToJson(str)
        reply({ results: results })
      });      
    }
  },
  {
    method: 'GET',
    path:'/clear', 
    handler: function (request, reply) {
      reply({name:'value'})
    }
  }
])

server.start((err) => {
  if (err) 
    throw err;
  console.log('Server running at:', server.info.uri);
});

Start the web server.

$ npm start                               
                                                                                
> [email protected] start /home/USR707MS/hapijs_itoolkit_bug            
> node server.js                                                                
                                                                                
Server running at: http://0.0.0.0:8000 

Watch this video to see how I work to prove that hapijs hangs when iToolkit is involved. It stops hanging if I make an additional call to a different endpoint that doesn't involve the iToolkit.

In the video I show that the async DB2 driver isn't working as expected. I then change it to use the sync approach with the following code change. After making this change it works; it doesn't hang.

      conn.run(function(str) {
        var results = xt.xmlToJson(str)
        reply({ results: results })
      }, true);      // <------ true means set to synchronous

Below is an excerpt from istoredp.js (the part of nodejs-itoolkit that talks to DB2).

      var stmt = new db.dbstmt(conn);
      if(sync == true) {  // Sync Mode
        stmt.prepareSync(sql);
        stmt.bindParamSync([
          [xipc, db.SQL_PARAM_INPUT, 1],
          [xctl, db.SQL_PARAM_INPUT, 1],
          [xml_input_script, db.SQL_PARAM_INPUT, 0],
          [xmlOut, db.SQL_PARAM_OUTPUT, 0],
        ]);
        stmt.executeSync(function(outArray) {  //out is an array of the output parameters.
          if(outArray.length == 1)
            callback(outArray[0]);  // For XML service, there is always only one return XML output. So handle it directly.
          else
            callback(outArray);  // For multiple return result, caller should handle it as an array.
          stmt.close();
          conn.disconn();
          conn.close();
        });
      } else {  // Async Mode
        stmt.prepare(sql, function(){
          stmt.bindParam([
            [xipc, db.SQL_PARAM_INPUT, 1],
            [xctl, db.SQL_PARAM_INPUT, 1],
            [xml_input_script, db.SQL_PARAM_INPUT, 0],
            [xmlOut, db.SQL_PARAM_OUTPUT, 0],
          ], function(){
            stmt.execute(function(outArray) {  //out is an array of the output parameters.
              if(outArray.length == 1)
                callback(outArray[0]);  // For XML service, there is always only one return XML output. So handle it directly.
              else
                callback(outArray);  // For multiple return result, caller should handle it as an array.
              stmt.close();
              conn.disconn();
              conn.close();
            });
          });
        });
      }

After reviewing the code it appears the issue is somewhere within the closed source components of the stmt object, so I can't debug further.

Please remove '-g' from install instructions

Original report by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Global installs are a bad practice, since upgrades will impact all applications running on the system, including those in production and test. Please remove this from the documentation

How to insert a null value?

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

I have a database table that has nullable columns and I want to multiple rows in one go. The data contains null values in various places. As in, I cannot just leave out an entire column in my insert statement but rather need an explicit way to insert null.

Is that currently possible? If yes, how would I do that?

execute() documentation

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

The execute() function in the API Reference documentation appears to be out-dated. It's missing the array of output parameters, which is parameter 1. Thanks!

Inserting over ~3000 rows concurrently causes SQLSTATE=57011 SQLCODE=-904

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

I am attempting to insert a few thousand rows of data concurrently with the following code:

#!javascript

function writeDb2(data) { 
  // data is an array of arrays of rows to be inserted
  // each row consists of roughly 60 elements
  data.forEach(function(row) {
    let stmt = new db2a.dbstmt(db);

    // helper function to build the param array
    let bindingParams = prepareBindingParams(row);

    // definition below
    bindAndExecute(stmt, sqlInsert, bindingParams);
  });
}

function bindAndExecute(stmt, sqlQuery, bindingParams) {
  stmt.prepare(sqlInsert, function(out, err) {
    if (err) throw err;

    stmt.bindParam(bindingParams, function(err) {
      if (err) throw err;

      stmt.execute(function(out, err) {
         if (err) throw err;

        stmt.close();
      });
    });
  });
}

when I do, I get

SQLSTATE=57011 SQLCODE=-904 RESOURCE UNAVAILABLE

When I checked the job log, it said that it was resource number 14.

How can I avoid that? Should I insert all values with a single insert statement?

dbconn.conn() Fails When 4 Arguments are Passed

Original report by David Russo (Bitbucket: DavidRusso, GitHub: DavidRusso).

---

In recent versions, calls like this:

dbconn.conn("*LOCAL", "YOUR_USRPRF", "YOUR_PASSWORD", function() {});

Fail with an error like this:

Error: An object was expected
at Object. (/home/drusso/idbtest/index.js:5:8)
at Module._compile (module.js:653:30)
at Object.Module._extensions..js (module.js:664:10)
at Module.load (module.js:566:32)
at tryModuleLoad (module.js:506:12)
at Function.Module._load (module.js:498:3)
at Function.Module.runMain (module.js:694:10)
at startup (bootstrap_node.js:204:16)
at bootstrap_node.js:625:3

I've attached a simple example that reproduces the error. The code works correctly with version 1.0.13 but fails with any later version, including 1.1.2.

Would like fetchAll that returns one row at a time

Original report by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

Would like to register a callback, and invoke a variant of fetchAll that invokes my callback once for each row returned. This would make it much easier to process all rows without the dangerous memory implications of the current fetchAll

executeSync callback called twice, v6.11.5

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

I believe a bug was introduced in v6.11.5 that wasn't there in v6.9.1. This issue was found when calling RPG from iToolkit.js using the synchronous approach.

Vanilla sample program:

let db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a')
let conn = new db.dbconn();
conn.setConnAttr(db.SQL_ATTR_DBC_SYS_NAMING , db.SQL_FALSE);
conn.conn("*LOCAL");

let stmt = new db.dbstmt(conn);
stmt.prepareSync("call QXMLSERV.iPLUG512K(?,?,?,?)");
let xmlOut;
stmt.bindParamSync([
  ["*NA", db.SQL_PARAM_INPUT, 1],
  ["*here", db.SQL_PARAM_INPUT, 1],
  ["<?xml version='1.0'?><myscript><cmd exec='rexx' error='fast'>RTVJOBA USER(?)</cmd></myscript>", db.SQL_PARAM_INPUT, 0],
  [xmlOut, db.SQL_PARAM_OUTPUT, 0],
]);
stmt.executeSync(outArray => {    
  delete stmt;
  conn.disconn();
  delete conn;
  console.log('outArray: ' + outArray)
});

Output:

outArray: <?xml version='1.0'?><myscript><cmd exec='rexx' error='fast'><success>+++ success RTVJOBA USER(?)</success>
<row>
<data desc='USER'>QUSER</data>
</row>
</cmd>
</myscript>
outArray: undefined          <--------- the second callback that should not happen

This same code works with v6.9.1.

I reviewed dbstmt.cc and see two callbacks in Execute(...), but I believe only one can be reached. This leads me to believe I don't have the correct version of source.

execute docs incorrect

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

The developerWork docs declare that both the result and the error can't both be declared as parameters on the callback.

But the code appears to support both result and error as parms to the callback. Here and here.

I propose the developerWorks docs be updated to reflect what the code currently supports. I don't have access to edit otherwise I would update it.

Rewrite code with the NAN interface

Original report by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

In the migration from Node.js v0.12.x to Node.js v4.x, the Node.js C++ addon interface has changed a lot. So I rewrote the DB2 connector using the new interface.
Once again, in the new version Node.js v8, the C++ interface has changed again.
To resolve the problem, we need to rewrite the code with the NAN interface.

https://github.com/nodejs/nan

Binary Download and Fallback to Build Fail on Node 8

Original report by David Russo (Bitbucket: DavidRusso, GitHub: DavidRusso).

---

I'm having problems installing idb-connector 1.1.3 with Node 8. If I try to install into a new project with no other dependencies I get this output:

#!
node-pre-gyp WARN Using needle for node-pre-gyp https download
node-pre-gyp WARN Pre-built binaries not installable for [email protected] and [email protected] (node-v57 ABI, unknown) (falling back to source compile with node-gyp)
node-pre-gyp WARN Hit error Remote end closed socket abruptly.
node-pre-gyp WARN Pre-built binaries not installable for [email protected] and [email protected] (node-v57 ABI, unknown) (falling back to source compile with node-gyp)
node-pre-gyp WARN Hit error bad download

Looks like 'needle' is unable to download the pre-built binary for some reason. Furthermore, the build fails when it falls back to trying to compile from source code with this:

#!

gyp: /home/drusso/idbtest/node_modules/idb-connector/build/config.gypi not found (cwd: /home/drusso/idbtest/node_modules/idb-connector) while reading includes of binding.gyp while trying to load binding.gyp

An interesting thing I noticed is that if I first install 'request' in my project, the idb-connector installation succeeds. Apparently 'node-pre-gyp' uses 'request' to do the download instead of 'needle";

#!

node-pre-gyp WARN Using request for node-pre-gyp https download
[idb-connector] Success: "/home/drusso/idbtest/node_modules/idb-connector/lib/binding/Release/node-v57-ibmi-ppc64/db2ia.node" is installed via remote

I'm getting this result with Node.js 8.12.0 on IBM i 7.2. The problem does not occur if I use Node.js 10.11.0 on the same system. In that case, 'needle' is able to download the binary and install w/o problems.

Is there some way to correct this problem for Node.js 8 so that it's not necessary to install 'request' first? Maybe request can be included in the idb-connector dependencies?

utf-8 problem?

Original report by António Ramos (Bitbucket: ramstein74, GitHub: ramstein74).

---

Example 1 fails

Original report by Andy Youens (Bitbucket: formaserve, GitHub: Unknown).

---

Example 1 fails with error:

internal/modules/cjs/loader.js:583
throw err;
^

Error: Cannot find module '/node/node_modules/idb-connector/lib/binding/Release/node-v64-ibmi-ppc64/db2ia.node'

PATH=/QOpenSys/pkgs/bin:/QOpenSys/usr/bin:/usr/ccs/bin:/QOpenSys/usr/bin/X11:/usr/sbin:.:/usr/bin

any help appreciated

README examples inconsistent syntax

Original report by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

The use of arrow functions and var seem alien. Can the examples be updated to use all ES6?

Proposal to conform to new N-API interface

Original report by Jesse G (Bitbucket: ThePrez, GitHub: ThePrez).

---

While this interface is still "experimental," it is being championed by IBM as the strategic future for native add-ons to the language. For API specifications, see https://nodejs.org/api/n-api.html

I suspect @worksofliam, @dmabupt, @aaronbartell, or others could tackle this, so opening the issue so we can have a public discussion on how to best proceed. Perhaps some other folks from the IBM i community would also be interested in helping.

Transition docs from developerWorks to this repo

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

I would like to propose transitioning the developerWorks Node.js DB Connector docs to this repo's wiki so we can have both the code and the documentation in the same place.

Is the existing developerWorks documentation in Markdown format?

I am assigning to @ThePrez to make the decision and then we can have community members work to port it over.

idb-connector not able to be found

Original report by Mike Bohlen (Bitbucket: mikebohlen, GitHub: mikebohlen).

---

Having an issue with our code where we have the idb-connector installed globally but when we require it , it can not be found. To get around the issue we have been including the idb-connector modules in our application. We would like to not have to do this. Any thoughts?

Problem installing idb-connector

Original report by Andreas Binder (Bitbucket: doan23, GitHub: Unknown).

---

Hi,

I'm facing a problem trying to install idb-connector through npm 6.4.1 and node 10.11.0 on IBM i V7R3M0. The output from calling npm i idb-connector and the resulting debug log are attached to this ticket. Is there anything I'm missing or doing wrong?

Thanks for your help and best regards,
Andreas

INOUT Parameter Binding is not working properly

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

When calling an RPG Stored Procedure from Node.js with an in/out parameter the call does not contain the value I'm trying to pass. Changing the call parameter to input results in the value being passed correctly. The Stored Procedure is being created with "PARAMETER STYLE GENERAL."

Stored Procedure definition:
CREATE OR REPLACE PROCEDURE &LIB/WORMLSTSP (
INOUT gact CHAR(2)
, IN cusr CHAR(10)

The First Node.js Stored Proc call:
getAction = 'RE';
stmt.bindParam([
[getAction, connection.db.SQL_PARAM_INPUT_OUTPUT, 1],
[currentUser, connection.db.SQL_PARAM_INPUT, 1],
...

Result: The first Parameter's value is received by RPG as X'F02F'
The stored procedure intentionally changed the value of the getAction parameter to 'OX' during the first call.

All Subsequent calls now send in OX, even though I'm explicitly setting the getAction to 'RE' before binding.

Changing the call to this works, but I don't get the new getAction value back:
getAction = 'RE';
stmt.bindParam([
[getAction, connection.db.SQL_PARAM_INPUT, 1],
[currentUser, connection.db.SQL_PARAM_INPUT, 1],

Please advise.

Pre-built binaries not found for [email protected] and [email protected]

Original report by Christian Fornara (Bitbucket: cfornara, GitHub: Unknown).

---

Hello,

i try to test your project.
https://bitbucket.org/litmis/nodejs-itoolkit/src/master/

I cloned the project, and made the npm install.
I tried to start the project but it gives me an error:

Error: Can not find module 'idb-connector'

So I thought that the npm had not installed everything, I did:
npm install --save idb-connector

but the result was:

#!javascript

node-pre-gyp ERR! Pre-built binaries not found for [email protected] and [email protected] (node-v57 ABI, unknown) (falling back to source compile with node-gyp)

C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector>if not defined npm_config_node_gyp (node "C:\Users\cfornara\AppData\Roaming\nvm\v8.1.2\node_modules\npm\bin\node-gyp-bin\\..\..\node_modules\node-gyp\bin\node-gyp.js" clean )  else (node ""
 clean )

C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector>if not defined npm_config_node_gyp (node "C:\Users\cfornara\AppData\Roaming\nvm\v8.1.2\node_modules\npm\bin\node-gyp-bin\\..\..\node_modules\node-gyp\bin\node-gyp.js" configure --fallback-t
o-build --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Re
lease\node-v57-ibmi-x64 --python=C:\Users\cfornara\.windows-build-tools\python27\python.exe --msvs_version=2015 )  else (node "" configure --fallback-to-build --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\n
ode-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64 --python=C:\Users\cfornara\.windows-build-tools\python27\python.exe --msvs_version=2015 )


C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector>if not defined npm_config_node_gyp (node "C:\Users\cfornara\AppData\Roaming\nvm\v8.1.2\node_modules\npm\bin\node-gyp-bin\\..\..\node_modules\node-gyp\bin\node-gyp.js" build --fallback-to-bu
ild --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Releas
e\node-v57-ibmi-x64 )  else (node "" build --fallback-to-build --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users\cfornara\Progetti\nodejs-
itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64 )
Compilazione dei progetti nella soluzione uno alla volta. Per abilitare la compilazione parallela, aggiungere l'opzione "/m".
  db2ia.cc
  dbconn.cc
  dbstmt.cc
  win_delay_load_hook.cc
c:\users\cfornara\progetti\nodejs-itoolkit\test\node_modules\idb-connector\src\db2ia\dbconn.h(11): fatal error C1083: Cannot open include file: 'sqlcli.h': No such file or directory (compiling source file ..\src\db2ia\db2ia.cc) [C:\Users\cfornara\Progetti\nodejs-
itoolkit\test\node_modules\idb-connector\build\db2ia.vcxproj]
c:\users\cfornara\progetti\nodejs-itoolkit\test\node_modules\idb-connector\src\db2ia\dbconn.h(11): fatal error C1083: Cannot open include file: 'sqlcli.h': No such file or directory (compiling source file ..\src\db2ia\dbstmt.cc) [C:\Users\cfornara\Progetti\nodejs
-itoolkit\test\node_modules\idb-connector\build\db2ia.vcxproj]
c:\users\cfornara\progetti\nodejs-itoolkit\test\node_modules\idb-connector\src\db2ia\dbconn.h(11): fatal error C1083: Cannot open include file: 'sqlcli.h': No such file or directory (compiling source file ..\src\db2ia\dbconn.cc) [C:\Users\cfornara\Progetti\nodejs
-itoolkit\test\node_modules\idb-connector\build\db2ia.vcxproj]
gyp ERR! build error
gyp ERR! stack Error: `C:\Program Files (x86)\MSBuild\14.0\bin\msbuild.exe` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onExit (C:\Users\cfornara\AppData\Roaming\nvm\v8.1.2\node_modules\npm\node_modules\node-gyp\lib\build.js:258:23)
gyp ERR! stack     at emitTwo (events.js:125:13)
gyp ERR! stack     at ChildProcess.emit (events.js:213:7)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:197:12)
gyp ERR! System Windows_NT 10.0.16299
gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\cfornara\\AppData\\Roaming\\nvm\\v8.1.2\\node_modules\\npm\\node_modules\\node-gyp\\bin\\node-gyp.js" "build" "--fallback-to-build" "--module=C:\\Users\\cfornara\\Progetti\\nodejs-itoolkit\\test\\n
ode_modules\\idb-connector\\lib\\binding\\Release\\node-v57-ibmi-x64\\db2ia.node" "--module_name=db2ia" "--module_path=C:\\Users\\cfornara\\Progetti\\nodejs-itoolkit\\test\\node_modules\\idb-connector\\lib\\binding\\Release\\node-v57-ibmi-x64"
gyp ERR! cwd C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector
gyp ERR! node -v v8.1.2
gyp ERR! node-gyp -v v3.6.2
gyp ERR! not ok
node-pre-gyp ERR! build error
node-pre-gyp ERR! stack Error: Failed to execute 'node-gyp.cmd build --fallback-to-build --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users
\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64' (1)
node-pre-gyp ERR! stack     at ChildProcess.<anonymous> (C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\node_modules\node-pre-gyp\lib\util\compile.js:83:29)
node-pre-gyp ERR! stack     at emitTwo (events.js:125:13)
node-pre-gyp ERR! stack     at ChildProcess.emit (events.js:213:7)
node-pre-gyp ERR! stack     at maybeClose (internal/child_process.js:897:16)
node-pre-gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:208:5)
node-pre-gyp ERR! System Windows_NT 10.0.16299
node-pre-gyp ERR! command "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\cfornara\\Progetti\\nodejs-itoolkit\\test\\node_modules\\idb-connector\\node_modules\\node-pre-gyp\\bin\\node-pre-gyp" "install" "--fallback-to-build"
node-pre-gyp ERR! cwd C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector
node-pre-gyp ERR! node -v v8.1.2
node-pre-gyp ERR! node-pre-gyp -v v0.6.39
node-pre-gyp ERR! not ok
Failed to execute 'node-gyp.cmd build --fallback-to-build --module=C:\Users\cfornara\Progetti\nodejs-itoolkit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64\db2ia.node --module_name=db2ia --module_path=C:\Users\cfornara\Progetti\nodejs-itool
kit\test\node_modules\idb-connector\lib\binding\Release\node-v57-ibmi-x64' (1)
npm WARN [email protected] No description

npm ERR! code ELIFECYCLE
npm ERR! errno 1
npm ERR! [email protected] install: `node-pre-gyp install --fallback-to-build`
npm ERR! Exit status 1
npm ERR!
npm ERR! Failed at the [email protected] install script.
npm ERR! This is probably not a problem with npm. There is likely additional logging output above.

npm ERR! A complete log of this run can be found in:
npm ERR!     C:\Users\cfornara\AppData\Roaming\npm-cache\_logs\2018-05-02T13_15_47_121Z-debug.log

it looks like idb-connector can not be installed with the latest versions of node.js

You have some idea about it?

Regards

Simple Insert leads to SQLSTATE: 22504 Native Error Code: -191 Mixed data or UTF-8 data not properly formed.

Original report by Kerim Gueney (Bitbucket: KerimG, GitHub: KerimG).

---

I'm trying to do a fairly simlpe insert query but when executing the prepared statement, I receive:

SQLSTATE: 22504 Native Error Code: -191 Mixed data or UTF-8 data not properly formed.

I can't figure out how to fix this, my code is:

var sqlInsert = "INSERT INTO MY.PERSONS (LASTNAME, FIRSTNAME, ADDRESS, CITY) VALUES (?, ?, ?, ?) WITH NONE";

stmt.prepare(sqlInsert, function () {
    stmt.bindParam([

            ['a', db.SQL_PARAM_INPUT,1],

            ['b', db.SQL_PARAM_INPUT, 1],

            ['c', db.SQL_PARAM_INPUT, 1],

            ['d', db.SQL_PARAM_INPUT, 1],



        ], function () {
            stmt.execute();
        }

}

Any help would be appreciated

Example of using stmtError()

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

Are there any examples available for calling stmtError()? Thanks!

Output contains \n characters

Original report by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

I'm not sure if this is the connector or xmlservice, but I'm getting \n characters after every and tag. It's adding unnecessary output byte/iPLUG size. Does anyone know more about this? Is there a way to remove it?

['<pgm name=\'...\' lib=\'...\' error=\'fast\'>\n<parm io=\'out\'>\n<data type=\'100a\' io=\'out\' name=\'...\'>...</data>\n...']

Replace IBM copyright headers with MIT copyright headers

Original report by Kevin Adler (Bitbucket: kadler, GitHub: kadler).

---

The code still has internal IBM copyright headers. These need to be replaced with MIT copyright headers to match the actual license of the code. Especially phrases like this are incongruent with being "open source":

/* The Source code for this program is not published  or otherwise  */
/* divested of its trade secrets,  irrespective of what has been    */
/* deposited with the U.S. Copyright Office.                        */

node-gyp as a dependency

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

Given we are delivering the pre-compiled binary via npm install is it possible to remove node-gyp (and maybe others) from the dependencies section?

This surfaced because of a vulnerability warning in the latest version of npm. Recreate the issue...

First upgrade npm if you're not on the latest version:

┌─[aaron @ KT4001]─[~]
└─[$]› npm i -g npm
/QOpenSys/pkgs/lib/nodejs8/bin/npx -> /QOpenSys/pkgs/lib/nodejs8/lib/node_modules/npm/bin/npx-cli.js
/QOpenSys/pkgs/lib/nodejs8/bin/npm -> /QOpenSys/pkgs/lib/nodejs8/lib/node_modules/npm/bin/npm-cli.js
+ [email protected]
added 283 packages, removed 363 packages and updated 41 packages in 315.849s


   ╭─────────────────────────────────────╮
   │                                     │
   │   Update available 5.6.0 → 6.3.0    │
   │     Run npm i -g npm to update      │
   │                                     │
   ╰─────────────────────────────────────╯


┌─[aaron @ KT4001]─[~]
└─[$]› npm -v
6.3.0

Create a project to test. The npm audit command requires a package.json file.

$]› mkdir idb-connector-audit

┌─[aaron @ KT4001]─[~/git]
└─[$]› cd idb-connector-audit/

┌─[aaron @ KT4001]─[~/git/idb-connector-audit]
└─[$]› npm -y init
Wrote to /home/aaron/git/idb-connector-audit/package.json:

{
  "name": "idb-connector-audit",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Do an install to see warnings.

┌─[aaron @ KT4001]─[~/git/idb-connector-audit]
└─[$]› npm install idb-connector

> [email protected] install /home/aaron/git/idb-connector-audit/node_modules/idb-connector
> node-pre-gyp install --fallback-to-build

node-pre-gyp WARN Using request for node-pre-gyp https download
[idb-connector] Success: "/home/aaron/git/idb-connector-audit/node_modules/idb-connector/lib/binding/Release/node-v57-ibmi-ppc64/db2ia.node" is installed via remote
npm notice created a lockfile as package-lock.json. You should commit this file.
npm WARN [email protected] No description
npm WARN [email protected] No repository field.

+ [email protected]
added 173 packages from 109 contributors and audited 287 packages in 118.04s
found 4 moderate severity vulnerabilities
  run `npm audit fix` to fix them, or `npm audit` for details

Run npm audit as recommended.

┌─[aaron @ KT4001]─[~/git/idb-connector-audit]
└─[$]› npm audit

                       === npm audit security report ===


                                 Manual Review
             Some vulnerabilities require your attention to resolve

          Visit https://go.npm.me/audit-guide for additional guidance


  Moderate        Prototype pollution

  Package         hoek

  Patched in      > 4.2.0 < 5.0.0 || >= 5.0.3

  Dependency of   idb-connector

  Path            idb-connector > node-gyp > request > hawk > boom > hoek

  More info       https://nodesecurity.io/advisories/566

dbstmt.fetch() Ignores Orientation SQL_FETCH_RELATIVE and Offset

Original report by David Russo (Bitbucket: DavidRusso, GitHub: DavidRusso).

---

The fetch() call seems to ignore orientation == SQL_FETCH_RELATIVE and offset. See attached example script. The first fetch() call should move the cursor 10 rows from the start of the result set.

Run the script and compare the results vs. running the same query interactively. The results are identical, meaning the fetch() call with SQL_FETCH_RELATIVE did not move the cursor 10 rows.

Access to NumRows() -> SQLCODE=8014 There is no result set to be queried.

Original report by christophe LALEVEE (Bitbucket: lalevee, GitHub: Unknown).

---

I cannot call NumRows() function with UPDATE or INSERT SQL statement..

My code:

#!Node.js

var sql = "UPDATE TABLE  SET...  WHERE ";
 
const db2a = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a')
const dbconn = new db2a.dbconn() ;
dbconn.conn("*LOCAL");
const stmt = new db2a.dbstmt(dbconn);
 
stmt.exec(sql, function (result, err) {
  if (err) console.log(err);
  else {
          console.log(result);
          stmt.stmtError(db2a.SQL_HANDLE_STMT, 1, function (ErrMsg) {
               console.log("Error retrieved");
               console.log(ErrMsg);
           });
           console.log("numrow updated " + stmt.numRows());
        }
}

Result :

console.log("numrow updated " + stmt.numRows());
                                     ^
Error: SQLSTATE=PAERR SQLCODE=8014 There is no result set to be queried. Please execute a SQL command first.
    at Error (native)
    at Object.db.query (/home/storebot2/TestNewDriverUpdate.js:51:44)
    at Object.<anonymous> (/home/storebot2/TestNewDriverUpdate.js:90:4)
    at Module._compile (module.js:570:32)
    at Object.Module._extensions..js (module.js:579:10)
    at Module.load (module.js:487:32)
    at tryModuleLoad (module.js:446:12)
    at Function.Module._load (module.js:438:3)
    at Module.runMain (module.js:604:10)
    at run (bootstrap_node.js:394:7)

I tried with exec and execSync.. same...
I kept "auto commit =true", table is journalised ....
With a SELECT statement, it works and return -1, that is good.
An idea ?
System: v7r3, PTF Group SF99225 is Level 3, SF99703 Level 4, SF99722 Level 7)

Thx

db2sock and node db2 impacts

Original report by Tony Cairns (Bitbucket: rangercairns, GitHub: rangercairns).

---

I suspect db2sock (libdb400.a replacement) could have significant impacts on design thinking for node db2 connector and toolkit. I am opening this 'enhancement' issue to discuss a few ideas. In fact, some are radical in thought, so probably best to discuss openly with people who are interested in the long term health of node on IBM i.

Unable to get Result Set from Stored Procedure call

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

I've updated to version 1.0.9, and SQL Statements are executing correctly (although I'm not binding parms), but when calling a stored procedure the result set is empty, even though the stored procedure is returning a row. I've also noticed that stmt.execute() is returning the in/out params as an array in the first parm, which seems to have been a recent change. Maybe I'm using the connector incorrectly...

    let stmt: any = new connection.db.dbstmt(connection.conn);
    stmt.prepare(sql, function(dbError) {
        if(dbError !== undefined) {
            stmt.close();
            pool.release(connection);
            return respondSqlError(res, sql, 'Prepare Error: ' + dbError);
        }
		
    ...
        
        stmt.bindParam([
            [getAction, connection.db.SQL_PARAM_INPUT_OUTPUT, 1],
            [numRows, connection.db.SQL_PARAM_INPUT, 2],
            [lastName, connection.db.SQL_PARAM_INPUT, 1]
        ], function(dbError) {
            if(dbError !== undefined){
                stmt.close();
                pool.release(connection);
                return respondSqlError(res, sql, 'Bind Error: ' + dbError);
            }
            stmt.execute(function(inOutParms, dbError) {
                if(dbError !== undefined){
                    stmt.close();
                    pool.release(connection);
                    return respondSqlError(res, sql, 'Execute Error: ' + dbError);
                }
                stmt.fetchAll( function(rs, dbError) {
                	logger.info('rs is...');
                	logger.info(JSON.stringify(rs));
                	logger.info('dbError is...');
                	logger.info(JSON.stringify(dbError));
                    if(dbError !== undefined) {
                        stmt.close();
                        pool.release(connection);
                        return respondSqlError(res, sql, 'FetchAll Error: ' + dbError);
                    }

How do set the library/schema list

Original report by Kristopher Baehr (Bitbucket: krisbaehr, GitHub: krisbaehr).

---

How can we set the library/schema list for a particular connection or statement? I'm having difficulty finding examples or documentation. To clarify, this is not the default library when one is not provided, but the full list ie. "LIB1, LIB2, LIB3." Thanks!

building project

Original report by Aaron Bartell (Bitbucket: aaronbartell, GitHub: aaronbartell).

---

I am trying to build this project and am curious about the instructions in the README.md.

Here is a mv command that is being done from what I assume is the root of the project.

mv src $Node_Install_Path/lib/node_modules/npm/bin/node-gyp-bin

The ./src folder contains .h and .cc files. Here is what I see when I display the contents of the destination directory:

ls /QOpenSys/QIBM/ProdData/OPS/Node6/lib/node_modules/npm/bin/node-gyp-bin
node-gyp      node-gyp.cmd

Are those instructions correct? I could just try it, but it is requiring me to "hose" my base install of Node.js (which I try not to touch).

Profound JS idb-connector installation error

Original report by Iftikhar Ahmed (Bitbucket: sheikhifti, GitHub: Unknown).

---

We have install Node JS 8 on IBM i offline system and now we are trying to install profound js but facing the below mention error please help to resolve the issue. Thanks in advance.

[37;40mnode-pre-gyp [0m [0m [31;40mERR! [0m [0m [35mTried to download(undefined): https://bitbucket.org/litmis/nodejs-idb-conn
ector/downloads/db2ia-v1.0.13-node-v57-ibmi-ppc64.tar.gz [0m
[0m [37;40mnode-pre-gyp [0m [0m [31;40mERR! [0m [0m [35mPre-built binaries not found for [email protected] and [email protected]
(node-v57 ABI, unknown) (falling back to source compile with node-gyp) [0m

Thanks
Iftikhar Ahmed

db.dbconn is not a constructor

Original report by Brian Jerome (Bitbucket: bjerome, GitHub: brianmjerome).

---

Since the beginning of the month I've had an issue with creating a db connection. The latest level 6 patches/open source group PTFs are installed. Currently, I'm running Node v6.12.2 with the shipped db2a.js and db2ia.node. I'm wondering if one of the newer patches caused something to break..

JS Code:

#!js
const db2a = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');

let conn = new db2a.dbconn(),
    stmt,
    sql = 'SELECT ...';

conn.conn('*LOCAL');

stmt = new db2a.dbstmt(conn);

stmt.exec(sql, (result) => {
    //handle result
    stmt.close();
    conn.disconn();
    conn.close();
});

Trying to run this has been giving me this error.

db2a.dbstmt also has the same error.

Is there a newer patch that exists or is being worked on that would fix this? Has anyone else run into this issue?

I've tried a few ways of recompiling the db2ia.node file but haven't gotten gmake installed yet. I'm not sure even if recompiling would fix it -- my other option I'm considering is a clean re-install of Node v6.

Segmentation fault in fetchAll()

Original report by Xu Meng (Bitbucket: mengxumx, GitHub: dmabupt).

---

Some optimization options in gcc -O2 lead to memory segmentation fault in fetchAll().

#!javascript

const db = require('/QOpenSys/QIBM/ProdData/OPS/Node6/os400/db2i/lib/db2a');
let dbConn = new db.dbconn();
dbConn.debug(true);
dbConn.conn('*LOCAL');

for(let i=0; i<1000; i++) {
       let stmt = new db.dbstmt(dbConn);
       stmt.prepare('SELECT * FROM QIWS.QCUSTCDT WHERE CDTLMT > ?', err => {
              let id = Math.floor(Math.random() * 1000);
              // console.log('Random ID:', id);
              let params = [
                     [id, db.SQL_PARAM_INPUT, 2]
              ];
              stmt.bindParam(params, err => {
                     stmt.execute(err => {
                           stmt.fetchAll(results => {
                                  // console.log('Iteration', i, id, results.length);
                                  // stmt.close();
                           });
                     });
              });
       });
}

#!shell

SQLExecute(0):
FetchAllAsync().
Can not allocate rowData[9] 
Segmentation fault (core dumped) 

02:42:28: Segmentation fault in . at 0xf014
0x0000f014 (dbx) where
.() at 0xf014
unnamed block in eh_alloc._GLOBAL__I_65535_0_.._.._.._.._.._gcc_4.8.2_libstdc___v3_libsupc___eh_alloc.cc_34457AA0_0(??), line 223 in "eh_alloc.cc"
unnamed block in eh_alloc._GLOBAL__I_65535_0_.._.._.._.._.._gcc_4.8.2_libstdc___v3_libsupc___eh_alloc.cc_34457AA0_0(??), line 223 in "eh_alloc.cc"
unnamed block in eh_alloc._GLOBAL__I_65535_0_.._.._.._.._.._gcc_4.8.2_libstdc___v3_libsupc___eh_alloc.cc_34457AA0_0(??), line 223 in "eh_alloc.cc"
eh_alloc._GLOBAL__I_65535_0_.._.._.._.._.._gcc_4.8.2_libstdc___v3_libsupc___eh_alloc.cc_34457AA0_0(??), line 223 in "eh_alloc.cc"
uv__queue_work(w = ??), line 244 in "threadpool.c"
worker(arg = ??), line 95 in "threadpool.c" 

#!shell

(dbx) 0xD39D2188 /10 i
0xd39d2188 ($b43280)      60000000         ori   r0,r0,0x0
0xd39d218c ($b43279+0x4)  a93b0a70         lha   r9,0xa70(r27)
0xd39d2190 ($b43279+0x8)  7f89e000         cmp   cr7,0x0,r9,r28
0xd39d2194 ($b43279+0xc)  419dff98         bgt   cr7,0xd39d212c (_ZN6DbStmt16FetchAllAsyncRunEP9uv_work_s+0xa0)
0xd39d2198 ($b43279+0x10) 813b0a80         lwz   r9,0xa80(r27)
0xd39d219c ($b43279+0x14) 815b0a84         lwz   r10,0xa84(r27)
0xd39d21a0 ($b43279+0x18) 7f895000         cmp   cr7,0x0,r9,r10
0xd39d21a4 ($b43279+0x1c) 419e00d8         beq   cr7,0xd39d227c ($b43279+0xf4)
0xd39d21a8 ($b43279+0x20) 2f890000        cmpi   cr7,0x0,r9,0x0
0xd39d21ac ($b43279+0x24) 815f0038         lwz   r10,0x38(r31)