Using Transaction Names in Data Statements
Go Up to Working with Transactions
Once named transactions are started, use their names in INSERT
, UPDATE
, DELETE
, and OPEN
statements to specify which transaction controls the statement. For example, the following C code fragment declares two transaction handles, mytrans1
, and mytrans2
, initializes them to zero, starts the transactions, and then uses the transaction names to qualify the data manipulation statements that follow:
. . .
EXEC SQL
BEGIN DECLARE SECTION;
long *mytrans1, *mytrans2;
char city[26];
EXEC SQL
END DECLARE SECTION;
mytrans1 = 0L;
mytrans2 = 0L;
. . .
EXEC SQL
SET DATABASE ATLAS = 'atlas.ib';
EXEC SQL
CONNECT;
EXEC SQL
DECLARE CITYLIST CURSOR FOR
SELECT CITY FROM CITIES
WHERE COUNTRY = 'Mexico';
EXEC SQL
SET TRANSACTION NAME mytrans1;
EXEC SQL
SET TRANSACTION mytrans2 READ ONLY READ COMMITTED;
. . .
printf('Mexican city to add to database: ');
gets(city);
EXEC SQL
INSERT TRANSACTION mytrans1 INTO CITIES (CITY, COUNTRY)
VALUES :city, 'Mexico';
EXEC SQL
COMMIT mytrans1;
EXEC SQL
OPEN TRANSACTION mytrans2 CITYLIST;
EXEC SQL
FETCH CITYLIST INTO :city;
while (!SQLCODE)
{
printf("%s\n", city);
EXEC SQL
FETCH CITYLIST INTO :city;
}
EXEC SQL
CLOSE CITYLIST;
EXEC SQL
COMMIT;
EXEC SQL
DISCONNECT;
. . .
As this example illustrates, a transaction name cannot appear in a DECLARE CURSOR
statement. To use a name with a cursor declaration, include the transaction name in the cursor’s OPEN
statement. The transaction name is not required in subsequent FETCH
and CLOSE
statements for that cursor.
The DSQL
EXECUTE
and EXECUTE IMMEDIATE
statements also support transaction names.For more information about using transaction names with data manipulation statements, see Working with Data. For more information about transaction names and the COMMIT
statement, see Using COMMIT. For more information about using transaction names with DSQL statements, see Working with Multiple Transactions in DSQL.