Modifying Transaction Behavior with “?”
Go Up to Working with Multiple Transactions in DSQL
The number and name of transactions available to a DSQL program is fixed when the program is preprocessed with gpre
, the InterBase preprocessor. The programmer determines both the named transactions that control each DSQL statement in a program, and the default behavior of those transactions. A user can change a named transaction’s behavior at run time.
In DSQL programs, a user enters a SQL statement into a host-language string variable, and then the host variable is processed in a PREPARE
statement or EXECUTE IMMEDIATE
statement.
PREPARE
:
- Checks the statement in the variable for errors.
- Loads the statement into an
XSQLDA
for a subsequentEXECUTE
statement.
EXECUTE IMMEDIATE
:
- Checks the statement for errors.
- Loads the statement into the
XSQLDA
. - Executes the statement.
Both EXECUTE
and EXECUTE IMMEDIATE
operate within the context of a programmer-specified transaction, which can be a named transaction. If the transaction name is omitted, these statements are controlled by the default transaction, GDS__TRANS
.
You can modify the transaction behavior for an EXECUTE
and EXECUTE IMMEDIATE
statement by:
- Enabling a user to enter a
SET TRANSACTION
statement into a host variable. - Executing the
SET TRANSACTION
statement before theEXECUTE
orEXECUTE IMMEDIATE
whose transaction context should be modified.
In this context, a SET TRANSACTION
statement changes the behavior of the next named or default transaction until another SET TRANSACTION
occurs.
The following C code fragment provides the user the option of specifying a new transaction behavior, applies the behavior change, executes the next user statement in the context of that changed transaction, then restores the original behavior of the trabnsaction.
. . . EXEC SQL BEGIN DECLARE SECTION; char usertrans[512], query[1024]; char deftrans[] = {"SET TRANSACTION READ WRITE WAIT SNAPSHOT"}; EXEC SQL END DECLARE SECTION; . . . printf("\nEnter SQL statement: "); gets(query); printf("\nChange transaction behavior (Y/N)? "); gets(usertrans); if (usertrans[0] == "Y" || usertrans[0] == "y") { printf("\nEnter \"SET TRANSACTION\" and desired behavior: "); gets(usertrans); EXEC SQL COMMIT usertrans; EXEC SQL EXECUTE IMMEDIATE usertrans; } else { EXEC SQL EXECUTE IMMEDIATE deftrans; } EXEC SQL EXECUTE IMMEDIATE query; EXEC SQL EXECUTE IMMEDIATE deftrans; . . .
As this example illustrates, you must commit or roll back any previous transactions before you can execute
SET TRANSACTION
.