Microsoft
SQL Server Closing the cursor
The CLOSE statement terminates processing for the specified cursor.
CLOSE releases resources associated with the cursor, and subsequent
references to that cursor are allowed only if another OPEN is executed.
Although you can have multiple cursors open at the same time, you should
close the cursors as soon as possible for efficiency reasons.
In SQL Server, there is an additional reason to close cursors as
soon as possible. When an OPEN statement completes successfully, there is
a result pending for the current connection. FETCH statements can be
executed as long as there are rows in the result set to be processed.
However, as long as the result set is pending, no other commands can be
executed using the connection. To execute other commands using the
connection, you must release the result set by closing the cursor.
Internally, PowerBuilder issues a DB-Lib dbcancel statement when the
cursor is closed. After the CLOSE has been executed, the connection can be
used for other SQL statements.
Example
This example illustrates the pending result set problem in SQL
Server. These statements use the cursor emp_curs to retrieve rows from the
employee table, then attempt to execute another SQL statement while the
cursor is open:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
// Declare the emp_curs. DECLARE emp_curs CURSOR FOR SELECT emp_name FROM EMPLOYEE WHERE emp_state = :sle_1.text; // Declare a destination variable for employee // names. string emp_name_var // Execute the SELECT statement with the current // value of sle_1.text. OPEN emp_curs; // Execute an INSERT statement. INSERT INTO office ( office_id, office_city ) VALUES ( 1234, 'Boston' ); // This INSERT statement would fail because of // the pending result set from the emp_curs // cursor. If we had never opened the cursor, or // if we had completed processing of the cursor // and then closed it, the INSERT statement // would work. |