stmt_error no information returned from executeMany failure

[scottdickerson]

when we get a DB2 error from the executeMany command, we get no details about the error from ibm_db

Here's the code change that should have logged the SQL error when we get it, but nothing comes back:

https://github.com/ibm-watson-iot/functions/pull/428/files

Here are the empty logs

2021-09-09 03:04:59 PM [PID 33239] [DEBUG ] iotfunctions.stages.store_derived_metrics : SQLSTATE =
Error : Logging the exception returns nothing either 2021-09-09 03:05:36 PM [PID 33239] [ERROR ] analytics_service.api._log_exception : The function PersistColumns failed to execute. 2021-09-09 03:05:36 PM [PID 33239] [ERROR ] analytics_service.api._log_exception : Traceback (most recent call last): File "/Users/shraddha.singh@ibm.com/watson_iot/functions/iotfunctions/stages.py", line 215, in store_derived_metrics res = ibm_db.execute_many(stmt, tuple(valueList)) Exception

[amukherjee28]

Hi @scottdickerson

Let me understand the problem statement here.

Are you suggesting to have some mechanism in executeMany API where, while inserting multiple rows into the table for each BAD row encountered during the insert operation, instead of blocking the execution you want to log the error for the bad row into a log file and continue with the Insert for the other GOOD rows till the end of the tuple to be inserted.

Currently while inserting in case some error is encountered during the insert operating the whole application is terminated in case error not handled.

An example :

When trying to insert a value of more that 10 characters in a varchar(10) column following error is seen and the whole application get terminated.

TABLE : create table execute_many_example (c1 INTEGER,C2 VARCHAR(10))

Insert tuple : ((10, 'arnab0'), (11, 'arnab1'), (12, 'arnab2'), (13, 'arnabarnab12dsfasdfasd'), (14, 'arnab4'), (15, 'arnab5'), (16, 'arnab6'), (17, 'arnab7'), (18, 'arnab8'), (19, 'arnab9'))

here row 4 had more that 10 characters for the 2nd column.

Following error is seen while running this execute many :

=> creating table
=> Table created
((10, 'arnab0'), (11, 'arnab1'), (12, 'arnab2'), (13, 'arnabarnab12dsfasdfasd'), (14, 'arnab4'), (15, 'arnab5'), (16, 'arnab6'), (17, 'arnab7'), (18, 'arnab8'), (19, 'arnab9'))
Traceback (most recent call last):
  File "C:\Users\amukherjee\open_source_work\python_ibm_db\samples\executeMany1.py", line 31, in <module>
    ibm_db.execute_many(stmt_insert,params)
Exception: Error 1: [IBM][CLI Driver][DB2] SQL0302N  The value of a host variable in the EXECUTE or OPEN statement is ou SQLCODE=-302r its corresponding use.  SQLSTATE=22001

Please let me know if you are suggesting a change in the same direction that I have explain.

Thanks.

[scottdickerson]

I’m fine with the first exception being reported and the batch failing. I’m just not getting as good as an exception as you are.

[bimalkjha]

@scottdickerson Are you still facing this issue or can be close it? It seem the log entries 2021-09-09 03:05:36 PM [PID 33239] [ERROR ] is not by ibm_db driver but your application code. So, ibm_db api is returning the Exception with error details, but your application code is either unable to catch it or log it properly. So, please check your application and correct it. Thanks.

[scottdickerson]

We did log those fields that were provided but they were empty. On the other hand out db2 instance stopped failing these statements so we can close this