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 |
// Declare the emp_curs.<br>DECLARE emp_curs CURSOR FOR<br> SELECT emp_name FROM EMPLOYEE<br> WHERE emp_state = :sle_1.text; |
|
1 |
// Declare a destination variable for employee<br>// names.<br>string    emp_name_var |
|
1 |
// Execute the SELECT statement with the current<br>// value of sle_1.text.<br>OPEN emp_curs; |
|
1 |
// Execute an INSERT statement.<br>INSERT INTO office ( office_id, office_city )<br> VALUES ( 1234, 'Boston' ); |
|
1 |
// This INSERT statement would fail because of<br>// the pending result set from the emp_curs<br>// cursor. If we had never opened the cursor, or<br>// if we had completed processing of the cursor<br>// and then closed it, the INSERT statement<br>// would work. |