We depend on a third party framework which uses one Cursor Instance for
the execution of many queries. There are some select queries and also
ddl statements which get executed with one Cursor instance.
After each execution of a statement the fetch method gets called. Also
for the ddl statements which produces no result. But in fdb this leads
to an exception "WindowsError: exception: access violation reading
0x0000000000000000". After this exception the cursor is no longer
usable. The next call of cur.execute() with a select statement blocks
and never returns.
This situation can be simulated with the following script. Instead of a
ddl statement it is also possible to use an update statement.
import fdb
import traceback
def execute(cur, stmt, parameters = None):
print "-----------------------------------"
print "Execute ", stmt
cur.execute(stmt, parameters)
try:
for row in cur.fetchall():
print row
except Exception, e:
print traceback.format_exc()
def main():
con = fdb.connect(dsn='localhost/3050:employee', user='sysdba',
password='masterkey')
cur = con.cursor()
execute(cur, "select * from COUNTRY")
#execute(cur, "UPDATE COUNTRY SET CURRENCY = 'Euro' where COUNTRY =
'Germany'")
execute(cur, "CREATE SEQUENCE TEST_SEQ_1")
execute(cur, "select * from COUNTRY")
con.commit()
con.close()
print "Finished"
main()
We got the following output:
-----------------------------------
Execute select * from COUNTRY
('USA', 'Dollar')
('England', 'Pound')
('Canada', 'CdnDlr')
('Switzerland', 'SFranc')
('Japan', 'Yen')
('Italy', 'Lira')
('France', 'FFranc')
('Germany', 'Euro')
('Australia', 'ADollar')
('Hong Kong', 'HKDollar')
('Netherlands', 'Guilder')
('Belgium', 'BFranc')
('Austria', 'Schilling')
('Fiji', 'FDollar')
-----------------------------------
Execute CREATE SEQUENCE TEST_SEQ_1
Traceback (most recent call last):
File "C:\Development\Workspaces\PySnippets\fdb_fetch_problem.py", line
10, in execute
for row in cur.fetchall():
File
"C:\Development\VirtualEnvs\web\lib\site-packages\fdb-1.1-py2.7.egg\fdb\\
fbcore.py", line 3188, in fetchall
return [row for row in self]
File
"C:\Development\VirtualEnvs\web\lib\site-packages\fdb-1.1-py2.7.egg\fdb\\
fbcore.py", line 2951, in next
row = self.fetchone()
File
"C:\Development\VirtualEnvs\web\lib\site-packages\fdb-1.1-py2.7.egg\fdb\\
fbcore.py", line 3148, in fetchone
return self._ps._fetchone()
File
"C:\Development\VirtualEnvs\web\lib\site-packages\fdb-1.1-py2.7.egg\fdb\\
fbcore.py", line 2820, in _fetchone
ctypes.cast(ctypes.pointer(self.out_sqlda), XSQLDA_PTR))
WindowsError: exception: access violation reading 0x0000000000000000
-----------------------------------
Execute select * from COUNTRY
The execution of the second select statement never returns. The
application is still running.
We think that a call to fetch after a sql statement without a result
should not lead to an access violation.
The same test with kinterbasdb leads to an exception like this:
Error (0L, 'Attempt to fetch row of results after statement that does
not produce result set. That statement was: CREATE SEQUENCE
TEST_SEQ_1')
After this exception the cursor from kinterbasdb is still usable.
This behavior seems to be conform to PEP 249 -- Python Database API
Specification v2.0
From: http://www.python.org/dev/peps/pep-0249/#id19
<http://www.python.org/dev/peps/pep-0249/#id19>
.fetchone <http://www.python.org/dev/peps/pep-0249/#fetchone> ()
Fetch the next row of a query result set, returning a single sequence,
or None when no more data is available. [6]
<http://www.python.org/dev/peps/pep-0249/#id45>
An Error <http://www.python.org/dev/peps/pep-0249/#error> (or subclass)
exception is raised if the previous call to .execute*()
<http://www.python.org/dev/peps/pep-0249/#id14> did not produce any
result set or no call was issued yet.
One solution could be, that inside the fetchone operation of the Cursor
is a check like this:
if not self._ps.statement_type in [isc_info_sql_stmt_select,
isc_info_sql_stmt_select_for_upd,
isc_info_sql_stmt_exec_procedure]:
raise Database.Error("Attempt to fetch row of results after
statement that does not
produce result set.")
We found a similar check inside the C source of kinterbasdb (see
function *_Cursor_fetchtuple from
http://python-kinterbasdb.sourcearchive.com/documentation/3.2/__kicore__\
cursor_8c-source.html
<http://python-kinterbasdb.sourcearchive.com/documentation/3.2/__kicore_\
_cursor_8c-source.html> ).
Is it possible that a patch like this could be integrated to fdb?
Thank you,
Josef
