Statements with mi_exec_prepared_statement()

The mi_exec_prepared_statement() function is for the execution of prepared statements, both queries and other valid SQL statements.

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


DataBlade® API function	Step in prepared-statement execution
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_exec_prepared_statement()	Sends the prepared statement to the database server for execution and opens any cursor required
mi_drop_prepared_statement()	Releases prepared-statement resources

The mi_exec_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, see Assign values to input parameters.
Sends the prepared statement to the database server for execution
The control flag 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.
When it executes a query, it performs the following additional steps:
- Opens an implicit cursor to hold the query rows
- Reads the query rows into the open cursor
The DataBlade® API stores the cursor as part of the statement descriptor. For more information about this row cursor, see Queries and implicit cursors.

Tip: If the implicit cursor that mi_exec_prepared_statement() creates does not adequately meet the needs of your DataBlade® API module, you can use the mi_open_prepared_statement() function to define other types of cursors. For more information, see Define an explicit cursor.

When the mi_exec_prepared_statement() function successfully fetches the query rows into the cursor, the cursor position points to the first row of the cursor, and the mi_get_result() function returns a status of MI_ROWS to indicate that the cursor contains rows.

You can access these rows one at a time with the mi_next_row() function. Each access obtains the row to which the cursor position points. After each access to the cursor, the cursor position moves to the next row. For more information, see Retrieving query data.

The following variation of the send_statement() function (Example: The send_statement() function) uses mi_exec_prepared_statement() instead of mi_exec() to send an SQL statement to the database server:

mi_integer send_statement2(conn, stmt)
   MI_CONNECTION *conn;
   mi_string *stmt;
{
   mi_integer count;
   MI_STATEMENT *stmt_desc;

   /* Prepare the statement */
   if ( (stmt_desc = mi_prepare(conn, stmt, NULL)) == NULL )
      mi_db_error_raise(conn, MI_EXCEPTION,
         "mi_prepared failed\n");

   /* Send the basic statement, specifying that query
    * be sent in its text representation
    */
   if ( mi_exec_prepared_statement(stmt_desc, 0, MI_FALSE,
         0, NULL, NULL, NULL, 0, NULL) == MI_ERROR )
      mi_db_error_raise(conn, MI_EXCEPTION,
         "mi_exec_prepared_statement failed\n");

   /* Get the results of the current statement */
   count = get_results(conn);

   /* Release statement resources */
   if ( mi_drop_prepared_statement(stmt_desc) == MI_ERROR )
      mi_db_error_raise(conn, MI_EXCEPTION,
         "mi_drop_prepared_statement failed\n");
   if ( mi_query_finish(conn) == MI_ERROR )
      mi_db_error_raise(conn, MI_EXCEPTION,
         "mi_query_finish failed\n");

   return ( count );
}

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

On the first call to mi_exec_prepared_statement(), specify in the types array the correct data type names for the input parameters.
On subsequent calls to mi_exec_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_exec_prepared_statement() must allocate, thereby reducing memory usage.

In the following code fragment, mi_exec_prepared_statement() in the initial pass of the for loop specifies the INTEGER data type for the single input parameter in an INSERT statement. For subsequent passes of the for loop, mi_exec_prepared_statement() receives a NULL-valued pointer for its types array. When it receives this NULL-valued pointer, mi_exec_prepared_statement() reuses the type descriptor that it has already created.

Figure 1. Reusing type descriptors in repeated calls to mi_exec_prepared_statement()

mi_string *types[1] = {"int"};
mi_string **types_exec;
...
sprintf(command, "insert into tabA values(?, %d);", j);
if ( (stmt_desc = mi_prepare(conn, command, NULL)) == NULL )
   {
   return -1;
   }

types_exec = types;
for (j=0; j < numLoop; j++)
   {
   values[0] = (MI_DATUM) j;

   if ( (ret = mi_exec_prepared_statement(stmt_desc, 
         MI_BINARY, 1, 1, values, lengths, nulls,
         types_exec, 0, NULL)) )
      {
      return -2;
      }

   if ( (ret = mi_get_result(conn)) == MI_ERROR )
      return -4;

   if ( ret == MI_DML || MI_DDL )
      row_count += mi_result_row_count(conn);

   types_exec = NULL; /* reuse data types from 1st pass */
   }