Show TOC

OPEN Statement [ESQL] [SP]Locate this document in the navigation structure

Opens a previously declared cursor to access information from the database.

Syntax
OPEN <cursor-name>
   ... [ USINGDESCRIPTOR<sqlda-name> | <host-variable> [, …] } ] ]
   ... [ WITH HOLD ]
Parameters

(back to top)

  • cursor-name identifier or host-variable.

    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.

  • sqlda-name identifier
  • WITH HOLD keeps the cursor open for subsequent transactions. The cursor remains 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.
  • USING specifies the host variables that are bound to the placeholder bind variables in the SELECT statement for which the cursor has been declared.
Examples

(back to top)

  • Example 1 use of OPEN in Embedded SQL:
    EXEC SQL OPEN employee_cursor;
    and
    EXEC SQL PREPARE emp_stat FROM
    'SELECT EmployeeID, Surname FROM Employees WHERE name like ?';
    EXEC SQL DECLARE employee_cursor CURSOR FOR emp_stat;
    EXEC SQL OPEN employee_cursor USING :pattern;
  • Example 2 an example from a procedure:
    BEGIN
    DECLARE cur_employee CURSOR FOR
      SELECT Surname
      FROM Employees ;
    DECLARE name CHAR(40) ;
    OPEN cur_employee;
    LOOP
    FETCH NEXT cur_employee into name ;
       ...
    END LOOP
    CLOSE cur_employee;
    END
Usage

(back to top)

By default, all cursors are automatically closed at the end of the current transaction (COMMIT or ROLLBACK).

The cursor is positioned before the first row.

A cursor declared using the FOR READ ONLY clause sees the version of table(s) on which the cursor is declared when the cursor is opened, not the version of table(s) at the time of the first FETCH statement.

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

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 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). The sqlerrd[2] field is the actual number of rows, if the database server can compute this value without counting the rows.

Standards

(back to top)

  • SQL—ISO/ANSI SQL compliant.
  • SAP Database Products—The simple OPEN <cursor-name> syntax is supported by SAP ASE. None of the other clauses are supported in SAP ASE stored procedures. Open Client/Open Server supports the USING descriptor or host name variable syntax.
Permissions

(back to top)

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