search

OpenInformix / IfxPy

Informix native Python driver for scalable enterprise and IoT solutions.
Other
46 stars 22 forks source link

Examples/DbAPI_Sample_Params.py fails with Exception: [Informix][Informix ODBC Driver]Driver not capable. SQLCODE=-11092 #3

Closed cypres-it closed 6 years ago

cypres-it commented 6 years ago

I tried to run the Examples/DbAPI_Sample_Params.py on ubuntu 16.04 with python2.7 as well as python3. With both I get the following Exception:

[Informix][Informix ODBC Driver]Driver not capable. SQLCODE=-11092

However python2 fails at UPDATE t1 SET c2 = 'Wednesday!' WHERE c1 = (?), whereas python3 fails at INSERT INTO t1 VALUES (?,?,?,?) days.

Bellow lines starting with //// are print statements to see where it fails.

python2:

$ python2 DbAPI_Sample_Params.py
//// create table t1 ( c1 int, c2 char(20), c3 int, c4 int )
//// INSERT INTO t1 VALUES (?,?,?,?) days
//// UPDATE t1 SET c2 = 'Wednesday!' WHERE c1 = 4
//// UPDATE t1 SET c2 = 'Wednesday!' WHERE c1 = (?)
Traceback (most recent call last):
  File "DbAPI_Sample_Params.py", line 43, in <module>
    cur.execute("UPDATE t1 SET c2 = 'Wednesday!' WHERE c1 = (?)", (c1_val,))
  File "/usr/local/lib/python2.7/dist-packages/IfxPyDbi.py", line 1219, in execute
    self._execute_helper(parameters)
  File "/usr/local/lib/python2.7/dist-packages/IfxPyDbi.py", line 1115, in _execute_helper
    for param in parameters:
Exception: [Informix][Informix ODBC Driver]Driver not capable. SQLCODE=-11092

python3:

$ python3 DbAPI_Sample_Params.py
//// create table t1 ( c1 int, c2 char(20), c3 int, c4 int )
//// INSERT INTO t1 VALUES (?,?,?,?) days
Exception: [Informix][Informix ODBC Driver]Driver not capable. SQLCODE=-11092

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/test/.local/lib/python3.5/site-packages/IfxPyDbi.py", line 576, in commit
    return_value = IfxPy.commit(self.conn_handler)
SystemError: <built-in function commit> returned a result with an error set

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "DbAPI_Sample_Params.py", line 35, in <module>
    conn.commit ()
  File "/home/test/.local/lib/python3.5/site-packages/IfxPyDbi.py", line 578, in commit
    raise _get_exception(inst)
IfxPyDbi.Error: ifx_pydb::Error: SystemError('<built-in function commit> returned a result with an error set',)

Here are some details about the Informix version we use:

Informix 12.10fc11
IBM Informix Dynamic Server Version 12.10.FC11WE 
IBM/Informix C++ API 4.10.FC11 
IBM/Informix EMBEDDED SQL for C Version 4.10.FC11 
IBM/Informix LIBASF LIBRARY Version 4.10.FC11 
IBM/Informix LIBDMI LIBRARY Version 4.10.FC11 
IBM/Informix LIBGEN LIBRARY Version 4.10.FC11 
IBM/Informix LIBOS LIBRARY Version 4.10.FC11 
IBM/Informix LIBSQL LIBRARY Version 4.10.FC11 
IBM/Informix ODBC LIBRARY Version 4.10.FC11 
IBM/Informix-Client SDK Version 4.10.FC11 
INFORMIX LIBGLS LIBRARY Version 6.00.FC12

Any idea what causes this Error or how to fix it?

cypres-it commented 6 years ago

It turns out the error is not produced by the cur.execute statements, but by the conn.commit and conn.close statements. When I comment out those, then the script seems to work fine.

deokershesh commented 6 years ago

Thanks for reporting the problem. I will reproduce and update accordingly.

deokershesh commented 6 years ago

Hi Cypres-it,

I am able to run the example(DbAPI_Sample_Params.py) by just changing the connection string (pointing to correct Informix server/database instance). Following is the output. This I tried on Windows. Will check on Linux/Ubuntu platform.

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>python -V Python 3.6.6rc1

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>python DbAPI_Sample_Params.py value = (4, 'Wednesday! ', 1004, 204) Wednesday! 1004 204 Row 0 value = (1, 'Sunday ', 101, 201) Row 1 value = (2, 'Monday ', 102, 202) Row 2 value = (3, 'Tuesday ', 103, 203) Row 3 value = (4, 'Wednesday! ', 1004, 204) Row 4 value = (5, 'Thursday ', 105, 205) Row 5 value = (6, 'Friday ', 106, 206) Row 6 value = (7, 'Saturday ', 1027, 207) Done

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>

Marc176 commented 6 years ago

Maybe re-try deokershesh with turn off logging for a database.

deokershesh commented 6 years ago

I tried multiple times running one after another (if that's what I understood by your above comment!), every time it ran fine on Windows.

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>python DbAPI_Sample_Params.py value = (4, 'Wednesday! ', 1004, 204) Wednesday! 1004 204 Row 0 value = (1, 'Sunday ', 101, 201) Row 1 value = (2, 'Monday ', 102, 202) Row 2 value = (3, 'Tuesday ', 103, 203) Row 3 value = (4, 'Wednesday! ', 1004, 204) Row 4 value = (5, 'Thursday ', 105, 205) Row 5 value = (6, 'Friday ', 106, 206) Row 6 value = (7, 'Saturday ', 1027, 207) Done

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>python DbAPI_Sample_Params.py value = (4, 'Wednesday! ', 1004, 204) Wednesday! 1004 204 Row 0 value = (1, 'Sunday ', 101, 201) Row 1 value = (2, 'Monday ', 102, 202) Row 2 value = (3, 'Tuesday ', 103, 203) Row 3 value = (4, 'Wednesday! ', 1004, 204) Row 4 value = (5, 'Thursday ', 105, 205) Row 5 value = (6, 'Friday ', 106, 206) Row 6 value = (7, 'Saturday ', 1027, 207) Done

D:\shesh\media\IfxPy-master\IfxPy-master\Examples>

Marc176 commented 6 years ago

Have you tried on ubuntu already ?

deokershesh commented 6 years ago

Not yet, Currently I don't have Ubuntu environment readily available with me. Trying to setup one... Meanwhile, if you have Windows environment available and if you could try would be great. What's the CSDK/ODBC version you are using?

jsagrera commented 6 years ago

I made a quick test in my Ubuntu box and the DbAPI_Sample_Params appears to works fine (similar to Windows)

informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ uname -a
Linux irk 4.13.0-39-generic #44~16.04.1-Ubuntu SMP Thu Apr 5 16:43:10 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ esql -V
IBM Informix CSDK Version 4.10, IBM Informix-ESQL Version 4.10.FC10 
Software Serial Number AAA#B000000
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ python DbAPI_Sample_Params.py 
('value = ', (4, u'Wednesday!          ', 1004, 204))
Wednesday!          
1004
204
('Row', 0, 'value = ', (1, u'Sunday              ', 101, 201))
('Row', 1, 'value = ', (2, u'Monday              ', 102, 202))
('Row', 2, 'value = ', (3, u'Tuesday             ', 103, 203))
('Row', 3, 'value = ', (4, u'Wednesday!          ', 1004, 204))
('Row', 4, 'value = ', (5, u'Thursday            ', 105, 205))
('Row', 5, 'value = ', (6, u'Friday              ', 106, 206))
('Row', 6, 'value = ', (7, u'Saturday            ', 1027, 207))
Done
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$

You are using a logged database right? A quick test against a non logged db shows the 11092.

informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ python DbAPI_Sample_Params.py 
FYI: drop table failed
Traceback (most recent call last):
  File "DbAPI_Sample_Params.py", line 31, in <module>
    cur.execute("UPDATE t1 SET c2 = 'Wednesday!' WHERE c1 = (?)", (c1_val,))
  File "build/bdist.linux-x86_64/egg/IfxPyDbi.py", line 1224, in execute
  File "build/bdist.linux-x86_64/egg/IfxPyDbi.py", line 1120, in _execute_helper
Exception: [Informix][Informix ODBC Driver]Driver not capable. SQLCODE=-11092
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$

if your DB is logged, maybe it would be a good idea to enable ODBC Tracing to see what the driver does:

informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ grep -i Trace $ODBCINI
; Trace file Section
Trace=1
TraceFile=/tmp/odbctrace.out
TRACEDLL=idmrs09a.so
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$ ls -la /tmp/odbctrace.out 
-rw-rw-r-- 1 informix informix 506459 Aug  8 11:00 /tmp/odbctrace.out
informix@irk:/data/informix/IBM/OpenInformix/IfxPy/Examples$

It may help throw us some clues of why is failing on your box ;)

Marc176 commented 6 years ago

Yes, we´re using non logged DBs

jsagrera commented 6 years ago

Umm... And is that by choice? You can't have transactions in a non-logged db, and that's something the driver assumes it can do.

Marc176 commented 6 years ago

How can we force the driver not to use transactions ?

jsagrera commented 6 years ago

you can't, you need transactions. You can configure IfxPy to use manual commits, or autocommit, but both need to connect to a transnational database.

jsagrera commented 6 years ago

Just did a minor checkin which will allow you to connect to a non-logged database. You will need to switch off the driver AUTOCOMMIT feature (which is enabled by default)

D:\Infx\OpenInformix\IfxPy\Examples>grep -e nolog -e AUTO -e commit DbAPI_Sample_Params.py
ConStr = "SERVER=ids1210;DATABASE=nolog;"
conn = dbapi2.connect( ConStr, "", "",conn_options={ dbapi2.SQL_ATTR_AUTOCOMMIT : dbapi2.SQL_AUTOCOMMIT_OFF })
#conn.commit ()
#conn.commit()
#conn.commit()

D:\Infx\OpenInformix\IfxPy\Examples>python DbAPI_Sample_Params.py
value =  (4, 'Wednesday!          ', 104, 204)
Wednesday!
104
204
Row 0 value =  (1, 'Sunday              ', 101, 201)
Row 1 value =  (2, 'Monday              ', 102, 202)
Row 2 value =  (3, 'Tuesday             ', 103, 203)
Row 3 value =  (4, 'Wednesday!          ', 104, 204)
Row 4 value =  (5, 'Thursday            ', 105, 205)
Row 5 value =  (6, 'Friday              ', 106, 206)
Row 6 value =  (7, 'Saturday            ', 1027, 207)
Done

D:\Infx\OpenInformix\IfxPy\Examples>

Still, it would be a lot better if you can use a logging database (either buffer or non-buffer)

jsagrera commented 6 years ago

closed