Informix Guide to SQL: Syntax SQL Statements

Use the OPEN statement to activate a cursor.

Use this statement with ESQL/C.

The OPEN statement activates the following types of cursors:

• A select cursor: a cursor that is associated with a SELECT statement
• A function cursor: a cursor that is associated with the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement
• An insert cursor: a cursor that is associated with the INSERT statement
• A collection cursor: a select or insert cursor that operates on a collection variable

The specific actions that the database server takes differ, depending on the statement with which the cursor is associated.

When you associate one of the previous statements with a cursor directly (that is, you do not prepare the statement and associate the statement identifier with the cursor) the OPEN statement implicitly prepares the statement.

In an ANSI-compliant database, you receive an error code if you try to open a cursor that is already open.

When you open either a select cursor or an update cursor that is created with the SELECT FOR UPDATE syntax, the SELECT statement is passed to the database server along with any values that are specified in the USING clause. The database server processes the query to the point of locating or constructing the first row of the active set.

The following example illustrates a simple OPEN statement in ESQL/C:

EXEC SQL declare  s_curs  cursor  for

	select  *  from  orders;

EXEC SQL open  s_curs;

If you are working in a database with explicit transactions, you must open an update cursor within a transaction. This requirement is waived if you declared the cursor using the WITH HOLD option.

When you open a function cursor, the EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement is passed to the database server along with any values that are specified in the USING clause. The values in the USING clause are passed as arguments to the user-defined function. This user-defined function must be declared to accept values. (If the statement was previously prepared, the statement was passed to the database server when it was prepared.) The database server executes the function to the point where it returns the first set of values.

The following example illustrates a simple OPEN statement in ESQL/C:

EXEC SQL declare  s_curs  cursor  for

	execute function new_func(arg1,arg2) 

	into :ret_val1, :ret_val2;

EXEC SQL open  s_curs;

In Enterprise Decision Server, to recreate this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

The database server evaluates the values that are named in the USING clause of the OPEN statement only when it opens the select or function cursor. While the cursor is open, subsequent changes to program variables in the USING clause do not change the active set of the cursor.

In an ANSI-compliant database, you receive an error code if you try to open a cursor that is already open.

In a non-ANSI database, a subsequent OPEN statement closes the cursor and then reopens it. When the database server reopens the cursor, it creates a new active set that is based on the current values of the variables in the USING clause. If the program variables have changed since the previous OPEN statement, reopening the cursor can generate an entirely different active set.

Even if the values of the variables are unchanged, the values in the active set can be different, in the following situations:

• If the user-defined function takes a different execution path from the previous OPEN statement on a function cursor
• If data in the table was modified since the previous OPEN statement on a select cursor

The database server can process most queries dynamically. For these queries, the database server does not pre-fetch all rows when it opens the select or function cursor. Therefore, if other users are modifying the table at the same time that the cursor is being processed, the active set might reflect the results of these actions.

However, for some queries, the database server evaluates the entire active set when it opens the cursor. These queries include those with the following features:

• Queries that require sorting: those with an ORDER BY clause or with the DISTINCT or UNIQUE keyword
• Queries that require hashing: those with a join or with the GROUP BY clause

For these queries, any changes that other users make to the table while the cursor is being processed are not reflected in the active set.

Because the database server is seeing the query for the first time, it might detect errors. In this case, the database server does not actually return the first row of data, but it sets a return code in the sqlca.sqlcode, SQLCODE field of the sqlca. The return code value is either negative or zero, as the following table describes.

If the SELECT, SELECTFOR UPDATE, EXECUTE Function (or EXECUTE PROCEDURE) statement is valid, but no rows match its criteria, the first FETCH statement returns a value of 100 (SQLNOTFOUND), which means no rows were found.

Tip: When you encounter an SQLCODE error, a corresponding SQLSTATE error value also exists. For information about how to get the message text, check the GET DIAGNOSTICS statement.

Opening an Insert Cursor

When you open an insert cursor, the cursor passes the INSERT statement to the database server, which checks the validity of the keywords and column names. The database server also allocates memory for an insert buffer to hold new data. (See DECLARE.)

An OPEN statement for a cursor that is associated with an INSERT statement cannot include a USING clause.

The following ESQL/C example illustrates an OPEN statement with an insert cursor:

EXEC SQL prepare  s1  from

	'insert  into  manufact  values  ('npr',  'napier')';

EXEC SQL declare  in_curs  cursor  for  s1;

EXEC SQL open  in_curs;

EXEC SQL put  in_curs;

EXEC SQL close  in_curs;

When you reopen an insert cursor that is already open, you effectively flush the insert buffer; any rows that are stored in the insert buffer are written into the database table. The database server first closes the cursor, which causes the flush and then reopens the cursor. For information about how to check errors and count inserted rows, see Error Checking.

In an ANSI-compliant database, you receive an error code if you try to open a cursor that is already open.

You can declare both select and insert cursors on collection variables. Such cursors are called collection cursors. You must use the OPEN statement to activate these cursors.

Use the name of a collection variable in the USING clause of the OPEN statement. For more information on the use of OPEN...USING with a collection variable, see Fetching From a Collection Cursor and Inserting into a Collection Cursor.

The USING clause of the OPEN statement is required when the cursor is associated with a prepared statement that includes question-mark (?) placeholders, as follows:

• A SELECT statement that contains input parameters in its WHERE clause
• An EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that contains input parameters as arguments of its user-defined function
• An INSERT statement that contains input parameters in its VALUES clause

You can supply values for these parameters in one of the following ways:

• You can specify one or more host variables
• You can specify a system-descriptor area
• You can specify a pointer to an sqlda structure

(For more information, see PREPARE.)

If you know the number of parameters to be supplied at runtime and their data types, you can define the parameters that are needed by the statement as host variables in your program. You pass parameters to the database server by opening the cursor with the USING keyword, followed by the names of the variables. These variables are matched with the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement question-mark (?) parameters in a one-to-one correspondence, from left to right.

You cannot include indicator variables in the list of variable names. To use an indicator variable, you must include the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement as part of the DECLARE statement.

You must supply one host variable name for each placeholder. The data type of each variable must be compatible with the corresponding type that the prepared statement requires.

The following example illustrates an ESQL/C code fragment that opens a select cursor and specifies host variables in the USING clause:

sprintf (select_1, "%s %s %s %s %s",

	"SELECT o.order_num, sum(total price)",

	"FROM orders o, items i",

	"WHERE o.order_date > ? AND o.customer_num = ?",

	"AND o.order_num = i.order_num",

	"GROUP BY o.order_num");

EXEC SQL prepare statement_1 from :select_1;

EXEC SQL declare q_curs cursor for statement_1;

EXEC SQL open q_curs using :o_date, :o.customer_num;

The following example illustrates the USING clause of the OPEN statement with an EXECUTE FUNCTION statement in an ESQL/C code fragment:

stcopy ("EXECUTE FUNCTION one_func(?, ?)", exfunc_stmt);

EXEC SQL prepare exfunc_id from :exfunc_stmt;

EXEC SQL declare func_curs cursor for exfunc_id;

EXEC SQL open func_curs using :arg1, :arg2;

In Enterprise Decision Server, to recreate this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

If you do not know the number of parameters to be supplied at runtime or their data types, you can associate input values from a system-descriptor area. A system-descriptor area describes the data type and memory location of one or more values to replace question-mark (?) placeholders.

A system-descriptor area conforms to the X/Open standards.

Use the SQL DESCRIPTOR keywords to introduce the name of a system descriptor area as the location of the parameters.

The COUNT field in the system-descriptor area corresponds to the number of dynamic parameters in the prepared statement. The value of COUNT must be less than or equal to the number of item descriptors that were specified when the system-descriptor area was allocated with the ALLOCATE DESCRIPTOR statement. You can obtain the value of a field with the GET DESCRIPTOR statement and set the value with the SET DESCRIPTOR statement.

The following example shows the OPEN...USING SQL DESCRIPTOR statement:

EXEC SQL allocate descriptor 'desc1';

...

EXEC SQL open selcurs using sql descriptor 'desc1';

If you do not know the number of parameters to be supplied at runtime, or their data types, you can associate input values from an sqlda structure. An sqlda structure lists the data type and memory location of one or more values to replace question-mark (?) placeholders.

Use the DESCRIPTOR keyword to introduce a pointer to the sqlda structure as the location of the parameters.

The sqlda value specifies the number of input values that are described in occurrences of sqlvar. This number must correspond to the number of dynamic parameters in the prepared statement.

The following example shows an OPEN...USING DESCRIPTOR statement:

struct sqlda *sdp;

...
EXEC SQL open selcurs using descriptor sdp;

Use the WITH REOPTIMIZATION option to reoptimize your query-design plan.

When you prepare a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, the database server uses a query-design plan to optimize that query. If you later modify the data that is associated with the prepared statement, you can compromise the effectiveness of the query-design plan for that statement. In other words, if you change the data, you can deoptimize your query. To ensure optimization of your query, you can prepare the statement again or open the cursor again using the WITH REOPTIMIZATION option.

Informix recommends that you use the WITH REOPTIMIZATION option because it provides the following advantages over preparing a statement again:

• Rebuilds only the query-design plan rather than the entire statement
• Uses fewer resources
• Reduces overhead
• Requires less time

The WITH REOPTIMIZATION option forces the database server to optimize the query-design plan before it processes the OPEN cursor statement.

The following example shows the WITH REOPTIMIZATION option:

EXEC SQL open selcurs using descriptor sdp with 
reoptimization;

The database server allocates resources to prepared statements and open cursors. If you execute a FREE statement_id or FREE statement_id_var statement, you can still open the cursor associated with the freed statement ID. However, if you release resources with a FREE cursor_id or FREE cursor_id_var statement, you cannot use the cursor unless you declare the cursor again.

Similarly, if you use the SET AUTOFREE statement for one or more cursors, when the program closes the specific cursor, the database server automatically frees the cursor-related resources. In this case, you cannot use the cursor unless you declare the cursor again.

Related statements: ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DESCRIBE, CLOSE, DECLARE, EXECUTE, FETCH, FLUSH, FREE, GET DESCRIPTOR, PREPARE, PUT, SET AUTOFREE, SET DEFERRED_PREPARE, and SET DESCRIPTOR

For a task-oriented discussion of the OPEN statement, see the Informix Guide to SQL: Tutorial.

For more information on system-descriptor areas and the sqlda structure, refer to the Informix ESQL/C Programmer's Manual.