The exception at line 12 is exactly what should happen when using Firebird. The statement was prepared using the transaction 'trans' at line 6 and then committed at line 9. When you commit thr transaction the prepare context is lost, and you will get an exception if you try and execute the statement without re-preparing the transaction. The exception is telling you explicitly that you have lost your transaction context.
Don't agree, line 12, not a Firebird exception, this is fbintf raise exception. re-preparing is not needed to execute. In my code this check/raise is //commented and everything works fine. Other Fb libs don't raise exception.
1. Prepare the statement again after line 10, or
This will kill performance
2. Use Commit Retaining at line 9, or
ZeosLib use CommitRetaining and me and other users complained by running 24x7 server and FB garbage collection, old active transaction and etc. This is one goal and inspiration for me to write mormot IBX driver with hard Commit.
3. Use a different transaction to execute the query from the one used to prepare the query. In this case, the transaction used to prepare the query is kept active as long as you need access to the statement, The transaction used to execute the query can be committed and restarted (e.g.) after every execute.
Not true, first call of statement start transaction, prepare, execute and commit transaction. Next call start transaction, execute and commit.
In mormot db driver you can explicitly start transaction BeginTransaction and every executed statement will be executed in this transaction context. At the end Commit or Rollback. In other case, if you don't call BeginTransaction every statement is atomic and I start and commit at end of execution. In IBX mormot db driver I have two transaction objects. One main transaction owned by connection object used for explicit BeginTransaction, and second internal transaction owned by Statement object used for software emulated auto commit when no explicit transaction is started. Same Statement can be executed with any of this two transactions. First call of statement execute Prepare and Execute in any of this two transaction, next calls only Execute this already prepared statement.
Default mormot server use 32 thread connection pool and per thread DB connection/attachment. Monitoring Fb for mvc-blog example, after some time "select * from MON$ATTACHMENTS" show 32 active connections.
"select * from MON$TRANSACTIONS" shows 0 transactions (ZeosLib 32 active transaction).
"select * from MON$STATEMENTS" shows <100 cached statements, column MON$STATE is 0 - idle. This idle statements exists on server as long as IStatement object exists and can be used to execute. MON$STATE can be: 0 - idle, 1 - active, 2 - stalled.
As I note early, everything work nice without this check/raise. Just ask for solution for users to not have to patch next versions of fbintf.