Handling Transactions (Using Dynamic SQL)
Go Up to DSQL Limitations (Using Dynamic SQL)
InterBase requires that all transaction names be declared when an application is preprocessed with gpre
. Once fixed at precompile time, transaction handles cannot be changed at run time, nor can new handles be declared dynamically at run time.
SQL statements such as PREPARE
, DESCRIBE
, EXECUTE
, and EXECUTE
IMMEDIATE
, can be coded at precompile time to include an optional TRANSACTION
clause specifying which transaction controls statement execution. The following code declares, initializes, and uses a transaction handle in a statement that processes a run-time DSQL statement:
#include "ibase.h" isc_tr_handle t1; . . . t1 = 0L; EXEC SQL SET TRANSACTION NAME t1; EXEC SQL PREPARE TRANSACTION t1 Q FROM :sql_buf;
DSQL statements that are processed with PREPARE
, DESCRIBE
, EXECUTE
, and EXECUTE IMMEDIATE
cannot use a TRANSACTION
clause, even if it is permitted in standard, embedded SQL.
The SET TRANSACTION
statement cannot be prepared, but it can be processed with EXECUTE IMMEDIATE
if:
- Previous transactions are first committed or rolled back.
- The transaction handle is set to
NULL
.
For example, the following statements commit the previous default transaction, then start a new one with EXECUTE IMMEDIATE
:
EXEC SQL COMMIT; /* set default transaction name to NULL */ gds__trans = NULL; EXEC SQL EXECUTE IMMEDIATE 'SET TRANSACTION READ ONLY';