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

 

.fetchone()

Fetch the next row of a query result set, returning a single sequence, or None when no more data is available. [6]

An Error (or subclass) exception is raised if the previous call to .execute*() 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).

   Is it possible that a patch like this could be integrated to fdb?

Thank you,

Josef