Node.js and DB2: More than ten bindParam causes unpredictable results.

[Edwin Davidson]

When using IBM i Node.JS v4.4.3 with the DB2i Add-on, the use of more than 10 binding parameters causes unexpected results.

A very simple example showing this issue is attached. When 10 or less parameters are in use, the results are ok. When more than 10 parameters are in use, it is unknown what will happen. I have seen DB2i not fail with an error, yet assert columns with values from an incorrect binding parameter.

The attached simple sample code will produce this output when it fails:

Bind Parameter [1] = A
Bind Parameter [2] = B
Bind Parameter [3] = C
Bind Parameter [4] = D
Bind Parameter [5] = E
Bind Parameter [6] = F
Bind Parameter [7] = G
Bind Parameter [8] = H
Bind Parameter [9] = I 
Bind Parameter [10] = J
Bind Parameter [11] = J
Bind Parameter [12] = J
Bind Parameter [13] = J
SQLExecute() rc=-1.    
 **** ERROR *****                                                                                   
SQLSTATE: 22001                                                                                     
Native Error Code: -404                                                                             
Value for column or variable K too long.                                                            
[Error: SQLSTATE=22001 SQLCODE=-404 Value for column or variable K too long.]   

[Aaron Bartell]

I don't get the same error message as you, but I do have the odd 'J' repeated multiple times in the debug log. I also am up to date with PTFs (as of today: SI60692). Will ping IBM to see if they know about this

Full REPL:

% node -v
v0.12.13
% node
> const db = require('/QOpenSys/QIBM/ProdData/Node/os400/db2i/lib/db2');
undefined
> const DBname = '*LOCAL';
undefined
> db.debug(true);
undefined
>
undefined
> db.init(function (err) {
...     if (err) {
.....         return err
.....     }
...     db.serverMode(true);
...     console.log('db.init complete');
... });
SetEnvAttr() attr = 10004, value = 1, rc = 0
db.init complete
undefined
> db.conn(DBname, function (err) {
...     if (err) {
.....         return err
.....     }
...     db.autoCommit(db.SQL_TRUE);
...     console.log('db.autoCommit should be true now.  Current value is %s.  True appears to be %s.', db.autoCommit(), db.SQL_TRUE);
... });
Connect to *LOCAL
SetConnAttr() attr = 10003, value = 1, rc = 0
GetConnAttr() attr = 10003, value = 1, rc = 0
db.autoCommit should be true now.  Current value is 1.  True appears to be 1.
Connected to *LOCAL with ID .
undefined
> const sql = 'INSERT INTO AARONLIB.TESTXYZ (A,B,C,D,E,F,G,H,I,J,K,L,M) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) with none ';
undefined
> db.prepare(sql);
SQL: INSERT INTO AARONLIB.TESTXYZ (A,B,C,D,E,F,G,H,I,J,K,L,M) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?) with none
undefined
> db.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],
...     ['E', db.SQL_PARAM_INPUT, 1],
...     ['F', db.SQL_PARAM_INPUT, 1],
...     ['G', db.SQL_PARAM_INPUT, 1],
...     ['H', db.SQL_PARAM_INPUT, 1],
...     ['I', db.SQL_PARAM_INPUT, 1],
...     ['J', db.SQL_PARAM_INPUT, 1],
...     ['K', db.SQL_PARAM_INPUT, 1],
...     ['L', db.SQL_PARAM_INPUT, 1],
...     ['M', db.SQL_PARAM_INPUT, 1]]);
Bind Parameter [1] = A
Bind Parameter [2] = B
Bind Parameter [3] = C
Bind Parameter [4] = D
Bind Parameter [5] = E
Bind Parameter [6] = F
Bind Parameter [7] = G
Bind Parameter [8] = H
Bind Parameter [9] = I
Bind Parameter [10] = J
Bind Parameter [11] = J
Bind Parameter [12] = J
Bind Parameter [13] = J
undefined
>
undefined
> try {
...     var rc1 = db.execute();
... } catch (e) {
...     console.log('more than 10 param:', e);
...     //throw ('error in SQL Insert:' + e.message);
... }
SQLExecute() rc=-1.

 **** ERROR *****
SQLSTATE: 22504
Native Error Code: -191
Mixed data or UTF-8 data not properly formed.
more than 10 param: [Error: SQLSTATE=22504 SQLCODE=-191 Mixed data or UTF-8 data not properly formed.]
undefined
> const sql2 = 'INSERT INTO AARONLIB.TESTXYZ (A,B,C,D,E,F,G,H,I,J) VALUES(?,?,?,?,?,?,?,?,?,?) with none ';
undefined
> db.prepare(sql2);
SQL: INSERT INTO AARONLIB.TESTXYZ (A,B,C,D,E,F,G,H,I,J) VALUES(?,?,?,?,?,?,?,?,?,?) with none
undefined
> db.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],
...     ['E', db.SQL_PARAM_INPUT, 1],
...     ['F', db.SQL_PARAM_INPUT, 1],
...     ['G', db.SQL_PARAM_INPUT, 1],
...     ['H', db.SQL_PARAM_INPUT, 1],
...     ['I', db.SQL_PARAM_INPUT, 1],
...     ['J', db.SQL_PARAM_INPUT, 1]
...     ]);
Bind Parameter [1] = A
Bind Parameter [2] = B
Bind Parameter [3] = C
Bind Parameter [4] = D
Bind Parameter [5] = E
Bind Parameter [6] = F
Bind Parameter [7] = G
Bind Parameter [8] = H
Bind Parameter [9] = I
Bind Parameter [10] = J
undefined
>
undefined
> try {
...     var rc2 = db.execute();
... } catch (e) {
...     console.log('10 param:', e);
...     //throw ('error in SQL Insert:' + e.message);
... }
SQLExecute() rc=-1.

 **** ERROR *****
SQLSTATE: 07001
Native Error Code: -313
Number of host variables not valid.
10 param: [Error: SQLSTATE=07001 SQLCODE=-313 Number of host variables not valid.]
undefined