Question: Is the CLOB data type supported?

[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.17.1
• Node.js OS: IBM i

It looks to me as though the CLOB data type is still not supported but I wanted to check this for sure.

I created a simple SQL stored procedure with two parameters; one an input CLOB and the second an output CLOB. When I run my test Node.js App I get the following message.

Error: [odbc] Error binding parameters to the procedure

I've looked at the following similar issues, which (along with my test results) suggest that CLOB isn't currently supported.

• #11: Calling Stored Procedures Issue
• #129: CLOB support
• #137: [BUG] Unable to Call Stored Procedure with CLOB Parameters
• #342: Revisit CLOB support

I noticed in issue #137 this suggested an RFE had been raised. I checked the Ideas portal and I couldn't find one.

Is the CLOB data type supported?

[brandonp42]

Hi @richardm90! Apologies I didn't see your issue until now, I use CLOB fields with node-odbc connecting to my IBMi at work without any issues on 7.4. If you could share a simplified example I'd be happy to take a look at what you are trying to do.

[richardm90]

Hi @brandonp42 and no problem, this is the test I was using.

My stored procedure.

CREATE OR REPLACE PROCEDURE RMTEMP.TEST_CLOB (
  IN    PARAM1 CLOB(1K) ,
  INOUT PARAM2 CLOB(1K) ,
  OUT   PARAM3 CLOB(1K)
)
BEGIN
  SET PARAM1 = 'Out1';
  SET PARAM2 = 'Out2';
  SET PARAM3 = 'Out3';
END;

-- CALL RMTEMP.TEST_CLOB('In1', 'In2', 'In3');

My Node.js app. This uses a DSN called MYIBMI to connect to my IBM i server.

const odbc = require('odbc');

odbc.connect('DSN=MYIBMI', (error, connection) => {
  if (error) { throw error; }
  let param1='In1';
  let param2='In2';
  let param3='In3';
  connection.callProcedure(null, 'RMTEMP', 'TEST_CLOB', [param1, param2, param3], (error, result) => {
    if (error) { throw error; }

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

Running the Node.js app gives me the following error.

Error: [odbc] Error binding parameters to the procedure

If I change my stored procedure from using CLOBs to VARCHARs it works as expected.

CREATE OR REPLACE PROCEDURE RMTEMP.TEST_CLOB (
  IN    PARAM1 VARCHAR(50) ,
  INOUT PARAM2 VARCHAR(50) ,
  OUT   PARAM3 VARCHAR(50)
)
BEGIN
  SET PARAM1 = 'Out1';
  SET PARAM2 = 'Out2';
  SET PARAM3 = 'Out3';
END;

-- CALL RMTEMP.TEST_CLOB('In1', 'In2', 'In3');

[brandonp42]

Okay, I looked into this a little more today and you are right that calling stored procedures with CLOB parameters using the .callProcedure() function does not currently work. However if you return a result set with your variables using the .query() function it does work, here's an example:

CREATE OR REPLACE PROCEDURE RMTEMP.TEST_CLOB (
  IN    PARAM1 CLOB(1K) ,
  INOUT PARAM2 CLOB(1K) ,
  OUT   PARAM3 CLOB(1K)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
  declare c1 cursor with return for
    with params (param1, param2, param3) as (values (param1, param2, param3))
    select * from params;

  SET PARAM1 = 'Out1';
  SET PARAM2 = 'Out2';
  SET PARAM3 = 'Out3';

  open c1;
  return;
END;

-- CALL RMTEMP.TEST_CLOB('In1', 'In2', 'In3');

The javascript code (I'm using async/await but you can still use callback functions):

import odbc from 'odbc';

let conn = await odbc.connect('DSN=*LOCAL');

let param1='In1';
let param2='In2';
let param3='In3';

let result = await conn.query('call RMTEMP.TEST_CLOB(?, ?, ?)', [param1, param2, param3]);

console.log(result);

[richardm90]

Thanks @brandonp42 , I can get that to work. Is that how you need to work with CLOBs? Should you be able to use callProcedure with CLOBS?

[brandonp42]

My opinion is that in an ideal world you should be able to use callProcedure with CLOBS but it seems like there needs to be a code update to support that. I haven't worked on this codebase before so I am not sure how easy/hard it would be. I was hoping that @markdirish would chime in - I'm willing to give it a try but he knows this codebase the best.

Most of my efforts recently have been digging into the XMLSERVICE codebase and working on that - it uses CLOBS with stored procedures so I was really surprised initially when I saw your issue here. However, (when called from the nodejs toolkit) we're not doing the callProcedure function, it's using a call with a result set; that's why I suggested the workaround I gave you. It's maybe not the best solution but it seems to work as long as you don't need multiple result sets. I did a quick test with multiple result sets and it didn't work - when I get a chance to re-confirm and put a full example together I will open a new issue for that.

[richardm90]

Thanks for the update and this does answer my original question of "Are CLOBs supported?", which they are using query and a result set but not using callProcedure.

[richardm90]

I've been looking at how I can adapt my stored procedures to return a result set as the work around to the CLOB parameter problem and it's a bit of a faff. My stored procedures are all external stored procedures (call RPGLE program) so I'm thinking I'd need to create an additional SQL stored procedure that calls my external stored procedure and then returned the parameters as a result set. It just sounds like a lot of messing around.

Should you be able to use callProcedure with CLOBS? Is this a bug?

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

[brandonp42]

Still an open question