Accessing firebird from Python, can't send new commands after commit

Question

I'm trying to work with a Firebird's SQL with Python, but once I make the first "COMMIT" line, everything else requires a new transaction, but I can't seem to find how do I start one.

fbsql.ShowEverythingInTable("Cakes", con) #Works
fbsql.DropFullTable('dummy',con) #Works
fbsql.DropFullTable('Cakes',con) #Works
fbsql.CommitTransaction(con) #Works
fbsql.ShowEverythingInTable('Objects',con) #YOU SHALL NOT PASS
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\firebirdSQL.py", line 61, in ShowEverythingInTable
    cur.execute(SQLSelectEverything + tableName +";")
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 3688, in execute
    self._ps = PreparedStatement(operation, self, True)
  File "C:\Users\graciele.davince\PycharmProjects\helloworld\venv\lib\site-packages\fdb\fbcore.py", line 2306, in __init__
    raise exception_from_status(DatabaseError, self._isc_status,
fdb.fbcore.DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -901\n- invalid transaction handle (expecting explicit transaction start)', -901, 335544332)

Please post a [mre] and specify which version of FDB you're using. As far as I can tell, `fbsql.ShowEverythingInTable(..)` and `fbsql.CommitTransaction(..)` is your own code, so without knowing what it does and how, it is hard to tell what might be going wrong. The problem itself is that no transaction is active, but as far as I know, normally FDB will automatically start a new transaction if none is present. — Mark Rotteveel, Jan 18 '20 at 10:48
If I had to guess, you are probably executing a `COMMIT` statement yourself instead of using the `commit()` function on the connection object. Executing a commit statement yourself will bring the driver in an inconsistent state. — Mark Rotteveel, Jan 18 '20 at 10:50
Hmm, That's true - I am indeed manually making the commits. I did it backwards - I thought that using the command would bring an inconsistent state. Thanks a lot! — , Jan 19 '20 at 14:07
The problem with using the commit statement is that FDB doesn't know about it, so client-side it thinks it still has a transaction, while server-side your transaction ends and no longer exists. When FDB subsequently executes another statements, it sends the old transaction handle, causing the server to report an error as that transaction no longer exists. — Mark Rotteveel, Jan 19 '20 at 14:55

score 2 · Accepted Answer · answered Jan 19 '20 at 14:57

This problem can occur if you execute COMMIT as a statement, instead of using the commit() function of the connection object. Doing this will bring the driver in an inconsistent state.

The problem with using the commit statement is that FDB doesn't know about it, so client-side it thinks it still has a transaction, while server-side your transaction ends and no longer exists. When FDB subsequently executes another statements, it sends the old transaction handle, causing the server to report an error as that transaction no longer exists.

In short: you need to use con.commit() (where con is the FDB connection object).

Accessing firebird from Python, can't send new commands after commit

1 Answers1