[BUG] callProcedure returns parameter values of incorrect datatype

[richardm90]

• odbc Package Version: 2.4.8
• ODBC Driver: unixODBC 2.3.9
• Database Name: DB2 for i
• Database Version: V7R5
• Database OS: IBM i
• Node.js Version: v18.20.2
• Node.js OS: IBM i

When using callProcedure I'm finding the returned results don't respect the datatype of the procedure parameters but instead appear to respect the datatype of the Node.js fields.

For example, if I create the following procedure.

DROP PROCEDURE RMTEMP.MY_PROC ;

CREATE PROCEDURE RMTEMP.MY_PROC (
  INOUT PARAM1 DECIMAL(11,2) ,
  INOUT PARAM2 DECIMAL(11,2) ,
  INOUT PARAM3 DECIMAL(11,2) ,
  INOUT PARAM4 DECIMAL(11,2)
)
BEGIN
  SET PARAM1 = 123.45;
  SET PARAM2 = 123.45;
  SET PARAM3 = 123.45;
  SET PARAM4 = 123.45;
END;

-- CALL RMTEMP.MY_PROC(99.99, 0, '99.99', null);

With the following Node.js code.

const odbc = require('odbc');

odbc.connect('DSN=MYIBMI', (error, connection) => {
  if (error) { throw error; }

  let param1=99.99;
  let param2=0;
  let param3='99.99';
  let param4=null;

  connection.callProcedure(null, 'RMTEMP', 'MY_PROC', [param1, param2, param3, param4], (error, result) => {
    if (error) { throw error; }

    connection.close();
    console.log(result);
  });
});

When I call this from Node I'm expecting all four parameters to have a return value of 123.45 (as numbers) but instead I get this. Of particular concern is the second parameter, which is returned as an integer, though I would expect all values to be returned as numbers rather than strings.

[
  statement: '{ CALL RMTEMP.MY_PROC (?,?,?,?) }',
  parameters: [ 123.45, 123, '123.45', '123.45' ],
  return: undefined,
  count: 0,
  columns: []
]

If I change the procedure to all OUT parameters I get a different result.

DROP PROCEDURE RMTEMP.MY_PROC ;

CREATE PROCEDURE RMTEMP.MY_PROC (
  OUT PARAM1 DECIMAL(11,2) ,
  OUT PARAM2 DECIMAL(11,2) ,
  OUT PARAM3 DECIMAL(11,2) ,
  OUT PARAM4 DECIMAL(11,2)
)
BEGIN
  SET PARAM1 = 123.45;
  SET PARAM2 = 123.45;
  SET PARAM3 = 123.45;
  SET PARAM4 = 123.45;
END;

Running the same Node.js code I get the following. At least I'm getting the correct values but all of the parameters are returned as strings rather than numbers.

[
  statement: '{ CALL RMTEMP.MY_PROC (?,?,?,?) }',
  parameters: [ '123.45', '123.45', '123.45', '123.45' ],
  return: undefined,
  count: 0,
  columns: []
]

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[kansasturnpike]

I'm seeing a similar issue when using stored procedures. However, have a StoredProceudre with a column of INOUT DECIMAL. The expected value is 2.50, but what I get back is 2.

[kansasturnpike]

@markdirish Any idea what's going on?