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.

Note: 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.