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 subsequent EXECUTE 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 the EXECUTE or EXECUTE 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;
. . .

Important:
As this example illustrates, you must commit or roll back any previous transactions before you can execute SET TRANSACTION.

Advance To:

Embedded SQL Guide