Using Cursors
Go Up to Working with Multiple Transactions
DECLARE CURSOR does not support transaction names. Instead, to associate a named transaction with a cursor, include the transaction name as an optional parameter in the cursor’s OPEN statement. A cursor can only be associated with a single transaction. For example, the following statements declare a cursor, and open it, associating it with the transaction, T1:
. . . EXEC SQL DECLARE S CURSOR FOR SELECT COUNTRY, CUST_NO, SUM(QTY_ORDERED) FROM SALES GROUP BY CUST_NO WHERE COUNTRY = 'Mexico'; EXEC SQL SET TRANSACTION T1 READ ONLY READ COMMITTED; . . . EXEC SQL OPEN TRANSACTION T1 S; . . .
An OPEN statement without the optional transaction name parameter operates under control of the default transaction, GDS__TRANS.
Once a named transaction is associated with a cursor, subsequent cursor statements automatically operate under control of that transaction. Therefore, it does not support a transaction name parameter. For example, the following statements illustrate a FETCH and CLOSE for the S cursor after it is associated with the named transaction, t2:
. . .
EXEC SQL
OPEN TRANSACTION t2 S;
EXEC SQL
FETCH S INTO :country, :cust_no, :qty;
while (!SQLCODE)
{
printf("%s %d %d\n", country, cust_no, qty);
EXEC SQL
FETCH S INTO :country, :cust_no, :qty;
}
EXEC SQL
CLOSE S;
. . .
Multiple cursors can be controlled by a single transaction, or each transaction can control a single cursor according to a program’s needs.