OPEN (Embedded SQL Form) | Teradata Vantage - OPEN (Embedded SQL Form) - Advanced SQL Engine - Teradata Database

Opens a declared cursor for an embedded SQL application and executes the SQL statement specified in its declaration.

ANSI Compliance

ANSI/ISO SQL:2011-compliant.

Required Privileges

None.

Invocation

Executable.

Embedded SQL only.

Syntax

OPEN cursor_name [
  USING {
    using_spec [,...] |
    DESCRIPTOR [:] descriptor_area
  }
]

Syntax Elements

using_spec

[:] host_variable_name [ [INDICATOR] :host_indicator_name ]

cursor_name

The name of the cursor to be opened.

host_variable_name

The variable to be used as input data for the cursor request.

The colon character preceding the name or names is optional.

host_indicator_name

The indicator variable.

The colon character preceding the name is mandatory.

descriptor_area

An SQLDA.

You can specify descriptor_area in a C program as a name or as a pointer reference (*sqldaname) when the SQLDA structure is declared as a pointer.

Usage Notes

• You should define an SQLDA.
• You must previously declare the cursor identified by cursor_name.
• The cursor identified by cursor_name must be closed.
• Once the cursor is open, the system executes the associated static or dynamic SQL statement or statements. The system then creates the result spool file and positions the cursor before the first row of the spool file.
• OPEN processing returns a 0 in the SQLCODE field of the SQLCA and ‘00000’ to SQLSTATE, unless a failure (implicit rollback) occurs. For an SQLCODE of 0, the system places the activity count in the third SQLERRD element of the SQLCA structure.
• If the cursor is updatable, or a C or COBOL application program contains a REWIND or POSITION TO STATEMENT request for the cursor, execute the OPEN statement with KEEPRESP; otherwise, execute it with NOKEEPRESP. For PL/I applications, KEEPRESP is the default.
• You cannot execute OPEN as a dynamic SQL statement.
• No more than 16 cursors can be open at one time because the processing of non-cursor-related statements is increasingly affected for the worse as more cursors are opened.

  If an application has 16 cursors open, no other request can be issued until one or more cursors are closed.

• The USING clause identifies variables used as input to the SQL statement by cursor_name.
• host_variable_name must be a valid client language variable you declared before the OPEN statement, to be used as an input variable.

  You can use a client structure to identify the input variables.

  The number of variables you specify must be the same as the number of parameter markers (the question mark character) in the identified statement.

  The nth variable corresponds to the nth marker.

  Use of the colon character prefix for host_variable_name is optional.

• descriptor_area identifies an input SQLDA structure, previously defined by the application, that contains necessary information about the input variable set.

  The number of variables identified by the SQLD field of the SQLDA must be the same as the number of parameter markers (the question mark character) in the identified statement.

  The nth variable described by the SQLDS corresponds to the nth marker.

Related Information

• Cursors referenced in the ALLOCATE statement, see ALLOCATE.
• Creating casts and using the AS ASSIGNMENT option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
• descriptor_area, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
• OPEN (Embedded SQL Form), see CLOSE.