OPEN (E-SQL)

The OPEN statement begins row-at-a-time data retrieval for a specified cursor.

Syntax

OPEN cursor_name [USING DESCRIPTOR :sqlda | USING :hvar [,...]]

Arguments

cursor_name

Is a previously declared, opened, and fetched cursor.

sqlda

Is an input SQLDA data structure that was previously constructed by the application. The SQLDA data structure contains the address, data type, and length of each input parameter. This option is used only with cursors that are declared by dynamical SQL statements.

hvar

Is one or more input host variables that correspond to parameter markers in the SELECT statement. This option is used only with cursors that are declared by dynamical SQL statements.

Remarks

The OPEN statement runs the SELECT statement specified in the corresponding DECLARE CURSOR statement to produce a result set, which is accessed one row at a time by the FETCH statement.

If the cursor is declared with a static SELECT statement, the SELECT statement can contain host variables (hvar) but not parameter markers (?). Host variables can only be used in place of constants. They cannot be used in place of the names of tables, columns, other database objects, or keywords. The current values of the host variables are substituted when the OPEN statement runs. Because the OPEN statement is for a statically declared cursor, it cannot contain the USING :hvar and USING DESCRIPTOR :sqlda options.

If the cursor is declared by using a dynamic SELECT statement, the SELECT statement can contain parameter markers but not host variables. Parameter markers can be used in place of column names in the SELECT statement. If the SELECT statement has parameter markers, the OPEN statement must include either the USING :hvar option with the same number of host variables as in the SELECT statement or the USING DESCRIPTOR :sqlda option that identifies the SQLDA data structure already populated by the application.

With the USING DESCRIPTOR :sqlda option, the values of the program variables are substituted for parameter markers in the SELECT statement. The program variables are addressed by corresponding sqldata entries in the SQLDA data structure. For information about SQLDA, see Using the SQLDA Data Structure.

A separate database connection is used for each open browse cursor. Each connection counts toward the total number of user connections configured on a Microsoft® SQL Server™ installation. If an attempt to make a new connection when opening a browse cursor fails, or if a valid current connection is not made when opening a standard cursor, run-time error -19521 “Open cursor failure” usually occurs.

Examples

EXEC SQL DECLARE c1 CURSOR FOR

    SELECT au_fname,au_lname FROM authors FOR BROWSE;

EXEC SQL OPEN c1;

while (SQLCODE == 0)

{

    EXEC SQL FETCH c1 INTO :fname,:lname;

}

See Also