[BUG] Unable to Call Stored Procedure with CLOB Parameters

[DavidRusso]

Describe your system

• odbc Package Version: 2.3.5
• ODBC Driver: IBM i Access ODBC Driver
• Database Name: Db2 for i
• Database Version: 7.2
• Database OS: IBM i 7.2
• Node.js Version: 12.18.4
• Node.js OS: IBM i

Describe the bug
Calling a stored procedure with CLOB parameters doesn't work. When called using connection.query() the call succeeds, but the output parameter data is not returned. When called using connection.callProcedure() the call fails with an error:

[Error: [odbc] Error retrieving the result set containing information about the columns in the procedure] {
  odbcErrors: [
    {
      state: '',
      code: 30160,
      message: '[IBM][System i Access ODBC Driver]Column 15: Numeric value out of range.'
    },
    {
      state: '',
      code: 30160,
      message: '[IBM][System i Access ODBC Driver]Column 15: Numeric value out of range.'
    }
  ]
}

Expected behavior
CLOB parameters should work the same as any other.

To Reproduce
To reproduce, create a simple stored procedure with CLOB parameters, like this:

create procedure yourlib/testclob (
  in a clob(128K),
  out b clob(128K)
)
language sql
set b = a

Then try calling, like this:

const odbc = require("odbc");

(async () => {
  
  try {

    const connection = await odbc.connect(`Driver=IBM i Access ODBC Driver;System=localhost;uid=YOU;password=YOURS`);
    let result = await connection.query("call yourlib.testclob(?, ?)", ["Hello, world", ""]);
    console.log(result);
    result = await connection.callProcedure(null, "YOURLIB", "TESTCLOB", ["Hello, world", ""]);
    console.log(result);
    await connection.close();
    
  }
  catch (error) {

    console.error(error);
    process.exit(1);
    
  }
  
})();

[markirish]

Two things:

1. That warning has nothing to do with CLOB support, it actually is because ParameterType is bound to an unsigned smallint, and CLOB indicator is -99, so it complains. This will be fixed in the next version.

2. Even when that is fixed, CLOB support currently doesn't work with the IBM i ODBC driver. See: CLOB support #129 . It checks an allowlist of data types for binding parameters, and that list doesn't include CLOBs. When I add CLOBs to the list it just works, but that issue won't be available until the changes get merged into the driver proper. I believe @worksofliam opened an RFE about it.

Since this isn't an issue odbc the NPM package, but instead with the driver, I am going to close this as "wontfix". I will try to get those changes in the driver soon!