Support for Row Wise Array Input in ibm_db

[monmallonga]

Hi, I've been trying to work out this problem. I have a list coming from my front end application. To save it to the database, I would be using a user defined type. The input parameter to my stored procedure is an array type based on row type. Kindly see sample test code below:

var common = require("./common")
  , ibmdb = require("../")
  , schema = common.connectionObject.CURRENTSCHEMA
  ;

ibmdb.debug(true);
ibmdb.open(common.connectionString, function(err, conn) {
  if(err) {
    console.log(err);
    return;
  }

  conn.querySync("create table arrtab (c2 decimal(19,2), c3 decimal(19,2), c4 varchar(10))");
  conn.querySync("CREATE OR REPLACE TYPE SAMPLE_TYPE AS ROW ( C2 DECIMAL(19,2), C3 DECIMAL(19,2), C4 VARCHAR(4))");
  conn.querySync("CREATE OR REPLACE TYPE SAMPLE_ARR AS SAMPLE_TYPE ARRAY[100]");
  conn.querySync("CREATE OR REPLACE PROCEDURE TEST_TYPE ( IN P_ARRAY SAMPLE_ARR) BEGIN INSERT INTO ARRTAB (C2,C3,C4) SELECT * FROM UNNEST(P_ARRAY) AS (C2, C3, C4); END")

  var paramC2 = {ParamType:"INPUT", DataType:"DOUBLE", Data:[19.2,20.4,18.5]}
  var paramC3 = {ParamType:"INPUT", DataType:"DOUBLE", Data:[8,77.1,23.4]}
  var paramC4 = {ParamType:"INPUT", DataType:"VARCHAR", Data:['BMW','TESLA','TOYOTA']}

--- i want to re-create the data for SAMPLE_ARR, which is an array of the row type SAMPLE_TYPE
  var test = {
      c2: paramC2,
      c3: paramC3,
      c4: paramC4
  }
  console.log(test)
  var query = "call TEST_TYPE(?)";
  stmt = conn.prepareSync(query);
  stmt.bindSync([test])
  stmt.setAttrSync(ibmdb.SQL_ATTR_PARAMSET_SIZE, 3);
  result = stmt.executeSync();
  stmt.closeSync()
  console.log('res',result)
});

After execution I get the following error:

C:\Users\r\Documents\Github\test\node_modules\ibm_db\lib\odbc.js:1472
  return self._executeSync();
              ^
Error: [IBM][CLI Driver] CLI0100E  Wrong number of parameters. SQLSTATE=07001
    at ODBCStatement.odbc.ODBCStatement.executeSync (C:\Users\r\Documents\Github\test\node_modules\ibm_db\lib\odbc.js:1472:15)     
    at C:\Users\r\Documents\Github\test\node_modules\ibm_db\test\test-array-sp.js:34:17
    at C:\Users\r\Documents\Github\test\node_modules\ibm_db\lib\odbc.js:111:11
    at C:\Users\r\Documents\Github\test\node_modules\ibm_db\lib\odbc.js:333:11 {
  error: '[node-odbc] Error in ODBCStatement::ExecuteSync',
  sqlcode: -99999,
  state: '07001'
}

I hope someone can point me to where im getting this wrong.

[bimalkjha]

@monmallonga The ParamType for array insert parameters should be ARRAY and not INPUT. Please check the test-array-insert.js for example. So, you should modify your params as below and then try:

  var paramC2 = {ParamType:"ARRAY", DataType:"DOUBLE", Data:[19.2,20.4,18.5]}
  var paramC3 = {ParamType:"ARRAY", DataType:"DOUBLE", Data:[8,77.1,23.4]}
  var paramC4 = {ParamType:"ARRAY", DataType:"VARCHAR", Data:['BMW','TESLA','TOYOTA']}

  var test = [paramC2, paramC3,paramC4 ];
  console.log(test)
  var query = "call TEST_TYPE(?)";
  stmt = conn.prepareSync(query);
  stmt.bindSync(test);

Thanks.

[monmallonga]

@bimalkjha I tried doing the querySync API version, I think what im trying to do might not be possible using ibm_db, unless im doing something wrong. I've followed the samples and the api documentations, but still it doesnt seem to work.

Would it be possible for ibm_db to support calling stored procedures with complex user defined types as parameter? In our case we define a user defined type row with different data types, then we create another user defined type array anchored to the first user defined type. This would simulate the use case of passing a tabled data to a stored procedure. With mybatis and java, we can handle this particular use case.

Error: [IBM][CLI Driver][DB2/NT64] SQL0301N The value of input variable, expression or parameter number "1" cannot be used because of its data type. SQLSTATE=07006

My sample code

var common = require("./common")
  , ibmdb = require("../")
  , schema = common.connectionObject.CURRENTSCHEMA
  , insertCount = 0;
  ;

ibmdb.debug(true);
ibmdb.open(common.connectionString, function(err, conn) {
  if(err) {
    console.log(err);
    return;
  }

  conn.querySync("create table arrtab (c1 INTEGER, c2 INTEGER, c3 char(1), c4 varchar(10))");
  conn.querySync("CREATE OR REPLACE TYPE SAMPLE_TYPE AS ROW ( C1 INTEGER, C2 INTEGER, C3 char(1), C4 VARCHAR(10))");
  conn.querySync("CREATE OR REPLACE TYPE SAMPLE_ARR AS SAMPLE_TYPE ARRAY[100]");

  err = conn.querySync("CREATE OR REPLACE PROCEDURE TEST_TYPE ( IN P_ARRAY SAMPLE_ARR) BEGIN INSERT INTO ARRTAB (C1,C2,C3,C4) SELECT C1,C2,C3,C4 FROM UNNEST(P_ARRAY) AS (C1,C2, C3, C4); END")
  if(err.length) { console.log(err); return; }

  var paramC1 = {ParamType:"ARRAY", DataType:1, Data:[0,1,2]}
  var paramC2 = {ParamType:"ARRAY", DataType:1, Data:[19,20,18]}
  var paramC3 = {ParamType:"ARRAY", DataType:1, Data:['Y','N','Y']}
  var paramC4 = {ParamType:"ARRAY", DataType:"VARCHAR", Data:['BMWX','TESL','TOYO'], Length:4} 
  var final = {ParamType:"ARRAY", DataType:1, Data: [paramC1,paramC2,paramC3,paramC4]}

  conn.querySync("insert into arrtab values (9, 4, 'Y', 'rock')");
  if(err.length) { console.log(err); return; }

  var data = conn.querySync("select * from arrtab");
  console.log("\nSelected data for table ARRTAB =\n", data);

 var queryOptions = { sql: "call test_type(?)",
                      params: [final],
                      ArraySize:3}
//   var queryOptions = {sql: "insert into arrtab values (?,?,?,?)",
//                       params: [paramC1, paramC2, paramC3, paramC4],
//                       ArraySize: 3}
  err = conn.querySync(queryOptions);
  console.log(err)

  var data = conn.querySync("select * from arrtab");
  console.log("\nSelected data for table ARRTAB =\n", data);
  conn.querySync("drop table arrtab");

});

[monmallonga]

Hi @bimalkjha i browsed through one of the issues here in github #233 , you mentioned there that only column-wise array input binding is implemented in ibm_db, I think what i am looking for is the row-wise array input which currently not supported. Would appreciate, if you could confirm if my understanding is correct.

[bimalkjha]

@monmallonga Yes, your understanding is correct. Only colum-wise array input is supported by ibm_db as of now. Thanks.