Statements with mi_open_prepared_statement()

The mi_open_prepared_statement() function is for the execution of queries.

In a DataBlade® API module, use the following DataBlade® API functions to execute a prepared SQL statement with mi_open_prepared_statement().


DataBlade® API function	Step in execution of prepared statement
mi_prepare()	Prepares the statement string for execution
mi_statement_command_name(), mi_get_statement_row_desc(), or input-parameter accessor function (Input-parameter information in the statement descriptor)	Obtains information about the prepared statement (optional)
mi_open_prepared_statement()	Sends the prepared statement to the database server for execution and open the cursor
mi_fetch_statement()	Retrieves any data that the query returns
mi_close_statement(), mi_drop_prepared_statement()	Releases prepared-statement resources

The mi_open_prepared_statement() function performs the following tasks for the prepared SQL statement:

Binds any input-parameter values to the appropriate input parameters in the prepared statement.
For more information about how to assign input-parameter values, see Assign values to input parameters.
Sends the prepared statement to the database server for execution.
Creates and opens an explicit cursor with characteristics specified in the control argument.
The DataBlade® API stores the cursor as part of the statement descriptor. For more information about this cursor, see Define an explicit cursor.

Tip: The mi_open_prepared_statement() function performs the same basic task for a DataBlade® API module as the SQL OPEN statement does for the Informix® ESQL/C application.

The main difference between mi_exec_prepared_statement() and mi_open_prepared_statement() is that the latter allows more flexibility in the definition of the cursor used for the query rows. With mi_open_prepared_statement(), you can define an explicit cursor. In particular, mi_open_prepared_statement() allows you to specify:

A string name to assign to the cursor
The cursor_name parameter is a pointer to the string name that you want to assign to the cursor. You can use this cursor_name for an update cursor so that the UPDATE or DELETE statement can reference the cursor in its clause:
```
WHERE CURRENT OF cursor_name
```
For more information, see Assign an optional name.
Client only: To use an internally-generated unique name for the cursor, specify a NULL-valued pointer for the cursor_name argument.
The type of cursor to use for holding the query rows
The mi_open_prepared_statement() function supports several flag values in its control flag that determine the type of cursor it creates. For more information, see Define an explicit cursor.
In addition, the control flag also supports the MI_BINARY flag to indicate that query rows are to be returned in binary representation. For more information, see Determine control mode for query data.
The number of rows to read into the cursor at one time
Unlike mi_exec() and mi_exec_prepared_statement(), mi_open_prepared_statement() does not read any retrieved rows into the open cursor. To fetch rows into the explicit cursor, use the mi_fetch_statement() function. For more information, see Fetch rows into a cursor.

The mi_open_prepared_statement() function allocates type descriptors for each of the data types of the input parameters. If the calls to mi_open_prepared_statement() are in a loop in which these data types do not vary between loop iterations, mi_open_prepared_statement() can reuse the type descriptors, as follows:

On the first call to mi_open_prepared_statement(), specify in the types array the correct data type names for the input parameters.
On subsequent calls to mi_open_prepared_statement(), replace the array of data type names with a NULL-valued pointer.

This method saves on the number of type descriptors that mi_open_prepared_statement() must allocate, thereby reducing memory usage. For sample code in which the mi_exec_prepared_statement() function reuses type descriptors, see Reusing type descriptors in repeated calls to mi_exec_prepared_statement().