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';