The mi_open_prepared_statement() function

The mi_open_prepared_statement() function sends a prepared statement that is a query to the database server for execution and opens a cursor for the retrieved rows of the query.

Syntax

mi_integer mi_open_prepared_statement(stmt_desc, control,
params_are_binary, n_params, values, lengths, nulls, 
types, cursor_name,retlen, rettypes)
   MI_STATEMENT *stmt_desc;
   mi_integer control;
   mi_integer params_are_binary;
   mi_integer n_params;
   MI_DATUM values[];
   mi_integer lengths[];
   mi_integer nulls[];
   mi_string *types[];
   mi_string *cursor_name;
   mi_integer retlen;
   mi_string *rettypes[];
stmt_desc
A pointer to a statement descriptor for the prepared statement to open. The mi_prepare() function generates this statement descriptor.
control
A bit-mask flag that controls the following characteristics:

Whether the returned rows are returned in their binary (internal) or text (external) representation

The type of cursor to create and open

params_are_binary
This value is set to one of the following values:
1 (MI_TRUE)
The input-parameter values (in the values array) are passed in their internal (binary) representation.
0 (MI_FALSE)
The input-parameter values (in the values array) are passed in their external (text) representation.
n_params
The number of entries in the nulls, lengths, and values arrays.
values
An array of MI_DATUM structures that contain the values of the input parameters in the prepared statement.
lengths
An array of the lengths (in bytes) of the input-parameter values.
nulls
An array that indicates whether each input parameter contains an SQL NULL value. Each array element is set to one of the following values:
1 (MI_TRUE)
The value of the associated input parameter is an SQL NULL value.
0 (MI_FALSE)
The value of the associated input parameter is not an SQL NULL value.
types
An array of pointers to the data type names for the input parameters. This array can be a NULL-valued pointer.
cursor_name
A name of the cursor that holds the fetched rows. This name must be unique.
retlen
The length of the rettypes array. Currently, valid values are:
>0
Indicates the number of columns that the query returns
0
Indicates that no result values exist
rettypes
An array of pointers to the data type names to which the result values are cast. This array can be a NULL-valued pointer if result values do not need to be cast.
Valid in client LIBMI application? Valid in user-defined routine?
Yes Yes

Usage

The mi_open_prepared_statement() performs the following tasks:
  • Binds any input-parameter values to the input-parameter placeholders in the prepared SQL statement that the stmt_desc statement descriptor references

    For any input parameters specified in the statement text of the SQL statement, you must initialize the values, lengths, and nulls arrays. If the prepared statement has input parameters and is not an INSERT statement, you must use the types array to supply the data types of the input parameters. You can provide the input-parameter values in either of the representations that correspond with the params_are_binary flag.

    For information about how to bind input-parameter values, see the HCL OneDB™ DataBlade® API Programmer's Guide.

  • Sends the prepared statement to the database server for execution
  • Opens an explicit cursor with characteristics that are specified by a bit mask in the control argument
    Cursor type Control-flag value
    Read-only sequential cursor MI_SEND_READ (default)
    Update scroll cursor MI_SEND_SCROLL
    Read-only scroll cursor MI_SEND_READ and MI_SEND_SCROLL

    The cursor is stored as part of the statement descriptor. Only one cursor per statement descriptor is current.

    The control argument also determines the representation of any returned values.

    Data representation Control-flag value
    External (text) representation None (default)
    Internal (binary) representation MI_BINARY

The mi_open_prepared_statement() function allocates type descriptors for each of the data types of the input parameters in the types array. 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. 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.

You can set the data types of the selected columns by setting a pointer to type name for each returned column in the rettypes array. If the pointer is NULL, the type is not modified. It will either be the return type of the column or the type set by a previous mi_open_prepared_statement() call. You cannot set the return types of subcolumns of columns of a complex type.

You can use the cursor_name argument to specify the name of the cursor that holds the fetched rows. This name must be unique within the client session.

Server only: When you specify a non-NULL value as the cursor_name argument for mi_open_prepared_statement(), make sure that you specify a NULL-valued pointer as the name argument for the mi_prepare() function. If you specify a non-NULL cursor name for mi_prepare(), use a NULL-valued pointer as the cursor_name value for mi_open_prepared_statement(). If you specify a cursor name in both mi_prepare() and mi_open_prepared_statement(), the DataBlade API uses the cursor name that mi_open_prepared_statement() provides.

To use an internally-generated unique name for the cursor, specify a NULL-valued pointer.

Client only: To use an internally generated unique name for the cursor, specify a NULL-valued pointer for the cursor_name argument of mi_open_prepared_statement().

Once opened, you can set up rows in the cursor for fetching with the mi_fetch_statement() function.

Return values

MI_OK
The function was successful.
MI_ERROR
The function was not successful.

A successful return indicates only that the connection is valid and a cursor was successfully opened. It does not indicate the success of the SQL statement. Use the mi_get_result() function to determine the success of the SQL statement.