Hi,

You're right, fetch should report correct error for statements that
doesn't return any result. However, your proposed fix is not correct.

Bug logged (PYFB-35), fixed and fix & test committed.

best regards
Pavel Cisar
IBPhoenix

Dne 6.5.2013 16:55, josef_gschwendtner napsal(a):

>
> 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
>
>