- edited description
Node.js and DB2: More than ten bindParam causes unpredictable results.
Issue #9
new
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.]
Comments (2)
-
reporter -
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 thisFull 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
- Log in to comment