OPEN statement [ESQL] [SP]

Use this statement to open a previously declared cursor to access information from the database.

Syntax 
OPEN cursor-name
[ USING { DESCRIPTOR sqlda-name | hostvar, ... } ]
[ WITH HOLD ]
[ ISOLATION LEVEL n ]
[ BLOCK n ]

cursor-name : identifier or hostvar

sqlda-name : identifier
Parameters

  • USING DESCRIPTOR clause   The USING DESCRIPTOR clause is for embedded SQL only. It specifies the host variables to be bound to the place-holder bind variables in the SELECT statement for which the cursor has been declared.

  • WITH HOLD clause   By default, all cursors are automatically closed at the end of the current transaction (COMMIT or ROLLBACK). The optional WITH HOLD clause keeps 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.

  • ISOLATION LEVEL clause   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 are 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 following values are supported:

    • 0
    • 1
    • 2
    • 3
    • snapshot
    • statement snapshot
    • readonly statement snapshot

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

  • BLOCK clause   This clause is for embedded SQL use only. Rows may be fetched by the client application more than one at a time. This is referred to as block fetching, prefetching, or multi-row fetching. The BLOCK clause can reduce the number of rows prefetched. Specifying the BLOCK clause on OPEN is the same as specifying the BLOCK clause on each FETCH. See FETCH statement [ESQL] [SP].

Remarks

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

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.

Embedded SQL usage: After successful execution of the OPEN statement, the sqlerrd[3] field of the SQLCA (SQLIOESTIMATE) is 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) is filled 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 is the actual number of rows if the database server can compute it without counting the rows. The database can also be configured to always return the actual number of rows (see row_counts option [database]), but this can be expensive. If cursor-name is specified by an identifier or string, the corresponding DECLARE CURSOR must appear prior to the OPEN in the C program; if the cursor-name is specified by a host variable, the DECLARE CURSOR statement must execute before the OPEN statement.

Permissions

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

Side effects

None.

See also
Standards and compatibility

  • SQL/2003   Embedded SQL use is a core feature. Procedures use is a Persistent Stored Modules feature.

Example

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

EXEC SQL OPEN employee_cursor;

and

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

The following example is from a procedure or trigger.

BEGIN
   DECLARE cur_employee CURSOR FOR
   SELECT Surname
   FROM Employees;
   DECLARE name CHAR(40);
   OPEN cur_employee;
   LP: LOOP
         FETCH NEXT cur_employee INTO name;
         IF SQLCODE <> 0 THEN LEAVE LP END IF;
    ...
   END LOOP
   CLOSE cur_employee;
END
Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0