Connecting to Your Database
Using Embedded SQL with Microsoft SQL Server

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:

// 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.