User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To open a previously declared cursor to access information from the database.

Syntax

     OPEN cursor-name
          ... [    |USING DESCRIPTOR sqlda-name| ]
              | USING host-variable, ...    |
          ... [ WITH HOLD ]
          ... [ ISOLATION LEVEL n ]
          ... [ BLOCK n ]

Parameters

     cursor-name:    identifier, or host-variable

     sqlda-name:    identifier

Usage

Embedded SQL, procedures, triggers, and batches.

The USING DESCRIPTOR sqlda-name, host-variable and BLOCK n formats are for Embedded SQL only.

Permissions

Must have SELECT permission on all tables in a SELECT statement or EXECUTE permission on the procedure in a CALL statement.

When the cursor is on a CALL statement, OPEN causes the procedure to execute until the first result set (SELECT statement with no INTO clause) is encountered. If the procedure completes and no result set is found, the SQLSTATE_PROCEDURE_COMPLETE warning is set.

Side effects

None.

See also

• DECLARE CURSOR statement
• PREPARE statement
• FETCH statement
• RESUME statement
• CLOSE statement

Description

The OPEN statement opens the named cursor. The cursor must be previously declared.

By default, all cursors are automatically closed at the end of the current transaction (COMMIT or ROLLBACK executed). The optional WITH HOLD clause will keep the cursor open for subsequent transactions. It will remain open until the end of the current connection or until an explicit CLOSE statement is executed. Cursors are automatically closed when a connection is terminated.

The ISOLATION LEVEL clause allows this cursor to be opened at an isolation level different from the current setting of the ISOLATION_LEVEL option. All operations on this cursor will be performed at the specified isolation level regardless of the option setting. If this clause is not specified, then the cursor's isolation level for the entire time the cursor is open is the value of the ISOLATION_LEVEL option when the cursor is opened. See "How locking works".

The cursor is positioned before the first row (see "Cursors in Embedded SQL" or "Using cursors in procedures and triggers").

Embedded SQL

If the cursor name is specified by an identifier or string, then the corresponding DECLARE CURSOR statement must appear prior to the OPEN in the C program; if the cursor name is specified by a host variable, then the DECLARE cursor statement must execute before the OPEN statement.

The optional USING clause specifies the host variables that will be bound to the place-holder bind variables in the SELECT statement for which the cursor has been declared.

The multiuser support fetches rows in blocks (more than 1 at a time). By default, the number of rows in a block is determined dynamically based on the size of the rows and how long it takes the database engine to fetch each row. The application can specify a maximum number of rows that should be contained in a block by specifying the BLOCK clause. For example, if you are fetching and displaying 5 rows at a time, use BLOCK 5. Specifying BLOCK 0 will cause 1 record at a time to be fetched and also cause a FETCH RELATIVE 0 to always fetch the row again.

After successful execution of the OPEN statement, the sqlerrd[3] field of the SQLCA (SQLIOESTIMATE) will be filled in with an estimate of the number of input/output operations required to fetch all rows of the query. Also, the sqlerrd[2] field of the SQLCA (SQLCOUNT) will be filled in with either the actual number of rows in the cursor (a value greater than or equal to 0), or an estimate thereof (a negative number whose absolute value is the estimate). It will be the actual number of rows if the database engine can compute it without counting the rows. The database can also be configured to always return the actual number of rows (see the ROW_COUNTS option in SET OPTION statement.), but this can be expensive.

Examples

The following examples show the use of OPEN in Embedded SQL.

     1. EXEC SQL OPEN employee_cursor;

     2. EXEC SQL PREPARE emp_stat FROM
     'SELECT empnum, empname FROM employee WHERE name like ?';
     EXEC SQL DECLARE employee_cursor CURSOR FOR emp_stat;
     EXEC SQL OPEN employee_cursor USING :pattern;

Procedure/trigger Example

     BEGIN
     DECLARE cur_employee CURSOR FOR
         SELECT emp_lname
         FROM employee ;
     DECLARE name CHAR(40) ;
     OPEN cur_employee;
     LOOP
     FETCH NEXT cur_employee into name ;
          ...
     END LOOP
     CLOSE cur_employee;
     END