search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

Bug Report - Unable to bind null value for BIGINT INOUT param #940

Closed rklbgiw closed 10 months ago

rklbgiw commented 10 months ago

Please provide below information while opening an issue to understand your problem

For non-Windows system, output of below commands from terminal:

uname : Darwin uname -m : x86_64 node -v : v18.14.0 npm ls ibm_db : /Users/RadhaKrishnana.Balakrishnan └── (empty) db2level : NA echo $IBM_DB_HOME : /Applications/dsdriver echo $PATH: NA echo $LD_LIBRARY_PATH $DYLD_LIBRARY_PATH :

For Windows system, output of below commands from windows command prompt:

node -v npm ls ibm_db db2level echo %IBM_DB_HOME% echo %PATH% echo %LIB%

Please provide below problem specific info:

=========================================

For Installation related issue

For Connection related issue

  1. Are you trying SSL connection or non-SSL connection?
  2. For SSL Connection, please read and follow this documentation
  3. For SSL connection, do you have ssl certificate from server?
  4. If you have certificate, are you using SSLServerCertificate keyword in connection string or using your own keystore db?
  5. Share the connection string used for connection by masking password.
  6. update database connection info in ibm_db/test/config.testConnectionStrings.json file and share complete output of below commands:
    • cd .../ibm_db
    • npm install
    • node test/test-basic-test.js
  7. For non-SSL connection, update connection info for db2cli validate command in file ibm_db/installer/testODBCConnection.bat for windows or ibm_db/installer/testODBCConnection.sh for non-Windows. Then execute testODBCConnection.bat from Administrator command prompt on Windows or testODBCConnection.sh script from terminal on non-Windows and share complete output of script along will all generated 1.* files in zip file.
  8. Complete output of db2cli validate command.

For SQL1598N Error

For other issues

  1. for one of our use case we need to use an INOUT param with BIGINT data type, and the stored procedure logic is to check if the incoming value of INOUT param is null there create new Reference ID otherwise validate the incoming Reference ID, we are able to call the otherwise scenario with valid Reference ID but when we pass null value for first scenario then the driver code fail in odbc.js at the line result = self.conn.querySync(sql, params); give below is the way we call the stored proc

    const inparam = { ParamType: 'INPUT', DataType: 'XML', Data: doc, Length: 100000 };

    var mrf = null; const inoutparam = { ParamType: 'INOUT', DataType: 'BIGINT', Data: mrf };

    const outparam = { ParamType: 'OUTPUT', DataType: 'BIGINT', Data: 0 };

    const db = await initDB(); const result = db.querySync(query, [inparam, inoutparam, outparam]); db.close(); return result;

We found that similar issue happened in ibm_db lib for CLOB and Array insert as well for other users and you have fixed it, can you fix the issue for BIGINT as well, it happens with both INOUT and OUT params when we bind null value for Data

Steps to Reproduce:

  1. Create a dummy DB2 store proc to have INOUT, and OUT param
  2. Bind the null values for Data with DataType BIGINT and call the stored proc through ibm_db driver
  3. You will see the program is crashing and not ever throwing any error
  4. Read the use case and refer code snippet mentioned above for more understanding
rklbgiw commented 10 months ago

Hello guys @bbigras @theduderog @Sannis @mhamann @bimalkjha Can anyone advice on this?

bimalkjha commented 10 months ago

@rklbgiw I am trying to write test program to repro this problem. It would be easy if you could have shared your Create Procedure SQL along with create table if any and CALL statement. Actually, if can write small test program to repro, it would much help so that we can just execute it and see the error. Thanks.

bimalkjha commented 10 months ago

@rklbgiw I am able to repro the issue, but for null value of BIGINT, I got Segmentation Fault (core dump) as below when installed ibm_db using npm install ibm_db --debug command:

ODBC::GetOutputParameter - String: paramtype=2 c_type=1 type=1 buf_len=0 len=8 val=(null)
Segmentation fault (core dumped)

Please clarify why you are passing null for a BIGINT column. I think null is not a valid value for BIGINT, right? And when you pass null as INPUT, what value you expect should be inserted in Db2 table? A 0? Thanks.

rklbgiw commented 10 months ago

Hi @bimalkjha, thanks much for having a look at the issue and coming back.

Here is the create procedure signature: CREATE OR REPLACE PROCEDURE SP_NAME (in doc xml, inout refno bigint, out docid bigint)

As I mentioned in the original issue content, the stored proc is expecting a NULL value for the INOUT param as it is written to validate the input to find matching value in a DB table for any value(including 0) except NULL and it looks the NULL value for BIGINT is allowed in the DB and the existing JDBC based consumer, so the existing call through JDBC works fine for a NULL value, we are new consumer to this stored procedure trying to consume it through ibm_db javascript driver and facing this segmentation fault error when we set null for the INOUT param, apparently the stored procedure returns validation error saying no match found for the given input when we set numeric default value( which is 0) to the INOUT param.

Can you advise, is this something can be addressed in the ibm_db lib to fix the segmentation fault error and accept NULL value binding for a BIGINT INOUT param?

Let me know for any other questions. thanks.

bimalkjha commented 10 months ago

@rklbgiw We have delivered fix for this issue and it should be available with next release of ibm_db. Hence, closing the issue now. Thanks.

rklbgiw commented 10 months ago

That's great and thanks much @bimalkjha and one last Q, can you advise any tentative date for the next release please? it would be great if you could advise the possible rel / version number?

bimalkjha commented 10 months ago

@rklbgiw We have plan to release new version before 30th September. Thanks.

rklbgiw commented 10 months ago

Great thanks!

rklbgiw commented 10 months ago

Hi @bimalkjha can I bother you again? I have tried downloading the changed files from you Git master repo and replaced it for the respective file on the node modules installed on my Mac, but I am still facing the Segmentation fault error, Am I doing something wrong here?

To reconfirm - here is the create procedure signature of our SP: CREATE OR REPLACE PROCEDURE SP_NAME (in doc xml, inout refno bigint, out docid bigint) and we are trying bind null for this field "inout refno bigint"

here is the binding..

const refno = { ParamType: 'INOUT', DataType: 'BIGINT', Data: null }

bimalkjha commented 10 months ago

@rklbgiw After copying the latest files under ibm_db directory, you need to run below commands from terminal:

cd ...\node_modules\ibm_db
npm install

Now, you can run your test program to verify. You can update your database connection info in file ibm_db\test\config.testConnectionStrings.json and then execute node test-sp-resultset.js to verify it. If issue still exist for your, please edit Test 4 of test-sp-resultset.js file and run it to reproduce your issue. If you are able to reproduce, share the updated test file here so that I can look into it. Thanks.

rklbgiw commented 10 months ago

Hi @bimalkjha followed the steps you have given above and it called the SP with NULL value binding for BIGINT inout param, there is no segmentation fault this time and my test SP works okay on my local(i.e dev laptop), we are now looking forward the fix to be published on npmjs.org for us to get the lib with fix installed without any manual intervention and promote the fix to the project environments. It will be great if the fix published ASAP. thanks.

bimalkjha commented 9 months ago

@rklbgiw The fix has been publish on npm. You can install latest version of ibm_db and verify it. Thanks.

rklbgiw commented 9 months ago

great thanks for the message @bimalkjha