Skip to content

Support for Row Wise Array Input in ibm_db #698

Open
@monmallonga

Description

@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.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions