Errors -413 or -802 ignored by ibm_db_dbi

[GoogleCodeExporter]

What steps will reproduce the problem?

for error -802 execute the following from the command line

db2-dev:~# python
Python 2.7 (r27:82500, Aug 07 2010, 16:54:59) [GCC] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibm_db_dbi; conn =ibm_db_dbi.connect("DB2"); cursor = conn.cursor()
>>> res=cursor.execute("SELECT CAST (DAYS(CAST('0001-01-01 00:00:00' AS 
TIMESTAMP)) - DAYS('1970-01-01') AS INTEGER) * 86400 FROM SYSIBM.SYSDUMMY1")
>>> rs=cursor.fetchone()
>>> print res,rs
True None
>>>

What is the expected output?
Exception thrown and a message similar to or containing the following:
SQLCODE=-802, SQLSTATE=22003

for error -413 execute the following from the command line

>>> res=cursor.execute("SELECT CAST('2147483649' AS INTEGER) FROM 
SYSIBM.SYSDUMMY1")
>>> rs=cursor.fetchone()
>>> print res,rs
True None

Expected output: Exception and traceback.

odbc trace
-----------

[ Process: 20150, Thread: 140737353971456 ]
[ Date & Time:               01/30/2012 09:03:10.594982 ]
[ Product:                   QDB2/LINUXX8664 DB2 v9.7.0.4 ]
[ Level Identifier:          08050107 ]
[ CLI Driver Version:        09.02.0000 ]
[ Informational Tokens:      "DB2 v9.7.0.4","s110330","IP23243","Fixpack 4" ]
[ Install Path:              /opt/ibm/db2/V9.7 ]
[ db2cli.ini Location:       /home/db2inst1/sqllib/cfg/db2cli.ini ]
[ CLI Driver Type:           IBM Data Server Runtime Client ]


[01/30/2012 09:03:10.710389] ( Row=1, iPar=1, fCType=SQL_C_CHAR, 
rgbValue="TEST" - x'4353544154494F4E', pcbValue=8, piIndicatorPtr=8 )
[01/30/2012 09:03:10.710672] 

[01/30/2012 09:03:15.817640] SQLExecute( pszSqlStr="SELECT CAST('2147483649' AS 
INTEGER) FROM SYSIBM.SYSDUMMY1" )
[01/30/2012 09:03:15.818231]     ---> Time elapsed - 0 seconds

[01/30/2012 09:03:15.819779] SQLExecute( )
[01/30/2012 09:03:15.820019]     <--- SQL_SUCCESS   Time elapsed - 
+2.379000E-003 seconds

[01/30/2012 09:03:38.594484] SQLExecute( pszSqlStr="SELECT CAST 
(DAYS(CAST('0001-01-01 00:00:00' AS TIMESTAMP)) - DAYS('1970-01-01') AS 
INTEGER) * 86400 FROM SYSIBM.SYSDUMMY1" )
[01/30/2012 09:03:38.594879]     ---> Time elapsed - +2.277446E+001 seconds

[01/30/2012 09:03:38.596242] SQLExecute( )
[01/30/2012 09:03:38.596591]     <--- SQL_SUCCESS   Time elapsed - 
+2.107000E-003 seconds

Expected result from command line
-----------------------------------

db2inst1@db2-dev:~> db2 "SELECT CAST (DAYS(CAST('0001-01-01 00:00:00' AS 
TIMESTAMP)) - DAYS('1970-01-01') AS INTEGER) * 86400 FROM SYSIBM.SYSDUMMY1"

1          
-----------
SQL0802N  Arithmetic overflow or other arithmetic exception occurred.  
SQLSTATE=22003

db2inst1@db2-dev:~> db2 "SELECT CAST('2147483649' AS INTEGER) FROM 
SYSIBM.SYSDUMMY1"

1          
-----------
SQL0413N  Overflow occurred during numeric data type conversion.  
SQLSTATE=22003

Original issue reported on code.google.com by south.mi...@gmail.com on 30 Jan 2012 at 5:06

[GoogleCodeExporter]

Hi,

I see the problem.

You could apply the patch below in ibm_db_dbi.py and the problem would be 
solved.

@1351
--return row_list
++raise  _get_exception(inst)

The exception was not seen because in the fetch_helper function we returned the 
rowset retrieved until the error time. In this case given that this is a single 
row a None was returned. I checked the DBI spec [ 
http://www.python.org/dev/peps/pep-0249/ ] to check what is the correct 
behaviour in cases where there was an error during fetch, should the partial 
result set retrieved be returned or throw an exception and discard the 
retrieved result set. However the spec did not describe about such a scenario.

Do you have any references that talk about what should be done in the scenarios 
described above? If yes kindly share, so that we can have the wrapper adhere 
accordingly.

In my opinion I would discard the partial result set on error (hence raising an 
exception will be the right thing to do). However if spec tells the other way 
then we would need to adhere to it.

P.S: The patch above should solve the other issue of select over REORG table 
that you have raised earlier [ 
http://code.google.com/p/ibm-db/issues/detail?id=91 ]

Thanks
Praveen

Original comment by praveen...@in.ibm.com on 31 Jan 2012 at 8:05

• Changed state: Started

[GoogleCodeExporter]

Hi Praveen,
thanks for your fast answer and for the patch.

Do you have any references that talk about what should be done in the

SQLFetch ODBC API reference is the closest reference to the issue I could
find and as it seems it returns partial results if possible (look at 'Error
Handling')
http://msdn.microsoft.com/en-us/library/windows/desktop/ms712424%28v=vs.85%29.as
px

In my opinion I would discard the partial result set on error (hence

I would discard partial results too but in some cases this would mean that
data inconsistency is completely unacceptable. In such cases not returning
partial results would mean to interrupt application execution.

Original comment by south.mi...@gmail.com on 31 Jan 2012 at 12:47

[GoogleCodeExporter]

Hi,

>> I would discard partial results too but in some cases this would mean that
data inconsistency is completely unacceptable. In such cases not returning
partial results would mean to interrupt application execution. <<

I will start a discussion with Mario and Rahul and check if we could handle 
throwing exceptions in cases where the row being fetched is the first row and 
return the partial list in cases where there is partial list available. In this 
way we will be able to throw errors in scenario as that of yours and also not 
interrupt execution in cases where an error occurs in between of a multi row 
fetch.

Feel free to flow in your suggestions too.

Thanks

Praveen

Original comment by praveen...@in.ibm.com on 1 Feb 2012 at 9:55

[GoogleCodeExporter]

Hi Praveen,
Looking at the odbc trace I noticed that SQL_SUCCESS is returned, shouldn't
it be SQL_ERROR?
Regarding the discussion you propose: returning errors row by row may mean
to have control over the cursor holding the results.
Best
Luis

Original comment by south.mi...@gmail.com on 1 Feb 2012 at 1:04

[GoogleCodeExporter]

Hi Luis,

SQL_ERROR will be seen on the SQLFetch call not SQLExecute. If you take to 
match this on command line (CLP), CLP executes and displays the output in one 
shot, hence you will not be able to make out if error was during execute or 
fetch. While in clients like ODBC you have a finer control and hence will know 
the exact error point.

>> returning errors row by row may mean
to have control over the cursor holding the results<<

Not sure what you meant by control over cursor. Check the fetch_helper method, 
in the ibm_db wrapper, it fetches row one by one calling the driver method 
fetch_tuple. Hence we know which row we are fetching at any point of time. In 
the proposal I meant, if we encounter an error on the fetch of first row itself 
we will throw an exception, else we will return the partial list retrieved.

Thanks
Praveen

Original comment by praveen...@in.ibm.com on 1 Feb 2012 at 1:27

[GoogleCodeExporter]

Hi Praveen,

Not sure what you meant by control over cursor. Check the fetch_helper

I see what you mean, but how to return a partial result and tell the user
an error has happened at the same time?
PEP 249 allows to define an optional "Cursor/Connection Attribute
.errorhandler", this could be used to control errors at row level.
Let's say the module has a rowLevelError property, if true then the
errorhandler can be used.

Original comment by south.mi...@gmail.com on 1 Feb 2012 at 3:30

[GoogleCodeExporter]

Hi Luis,

Will have a look at it.

Thanks
Praveen

Original comment by praveen...@in.ibm.com on 2 Feb 2012 at 8:10

[GoogleCodeExporter]

Hi Luis,
 We have decided to raise the exception if error occur in retrieving 1st row itself, else we will append the Exception in messages attribute of cursor and return the partial list retrieved.

you can find the modified ibm_db_dbi.py file in the attachment.

Please give a try to this and let us know how it works for you.   

Original comment by rahul.pr...@in.ibm.com on 21 May 2012 at 10:31

Attachments:

• ibm_db_dbi.py

[GoogleCodeExporter]

Hi Rahul,
The fix worked as you described, looks good to me.
Is this fix included in 1.0.6 release?
Thanks again for your work
Luis

PS: 
test scenario 
* test table with one integer column and this values

COL1       
-----------
          2
          3
 2147483647

execute the following queries with a combination of cursor.fetchone() and 
cursor.fetchall(), 
SELECT CAST((COL1 +1) AS INTEGER) FROM TEST_TABLE'
SELECT CAST((COL1 +1) AS INTEGER) FROM TEST_TABLE ORDER BY COL1 DESC'

checked cursor.messages[0].messages

'Fetch Failure: [IBM][CLI Driver][DB2/LINUXX8664] SQL0802N  Arithmetic overflow 
or other arithmetic exception occurred.  SQLSTATE=22003 SQLCODE=-802'

Original comment by south.mi...@gmail.com on 25 May 2012 at 11:45

[GoogleCodeExporter]

Hi Luis,
 Thanks for confirming the fix. This fixed has been released with 1.0.6 release.

Original comment by rahul.pr...@in.ibm.com on 26 May 2012 at 6:06

• Changed state: Fixed