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, orThis will kill performance
2. Use Commit Retaining at line 9, orZeosLib 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.
The problem with your approach is that you run the risk of the statement BLR becoming out-of-date. If you are keeping a statement prepared over a long time - which you seem to suggest that you are - then this could be a serious problem.Creating web services with Db is not a easy task, where Db layer is main а bottleneck for performance.
My recommended approach in this situation is to have a separate transaction for the prepare and the execute phases. The former you keep open for as long as you want to keep using the same statement BLR and it acts as a guard against a ddl change. The cost of do this is at most one extra transaction start/commit and I don't see why that should be a big problem.DDL change on working production system is no option to resolve at runtime. Usually mormot server on startup in main thread/connection do all ddl changes, before start of connection pull.