Executing Prepared SQL Statements

A prepared statement is an SQL statement that is parsed and ready for execution. For these statements, you prepare the statement once and execute it as many times as needed. The DataBlade API provides the following functions to execute a prepared SQL statement.

Preparing an SQL Statement

To turn a statement string for an SQL statement into a format that the database server can execute, use the mi_prepare( ) statement. The mi_prepare( ) function performs the following tasks to create a prepared statement:

• Sends a statement string to the database server for parsing
• Assigns an optional name to the SQL statement
• Returns a pointer to a statement descriptor for the prepared statement

Assembling a Prepared Statement

The mi_prepare( ) function passes the SQL statement to the database server as a statement string. For the mi_prepare( ) function, a statement string can contain either of the following formats of an SQL statement:

• An unparameterized SQL statement (the same as the mi_exec( ) function accepts)
• A parameterized SQL statement, which contains input parameters

Assembling Unparameterized Statements

If you know all the statement information before the statement is prepared, you assemble an unparameterized statement as the statement string. Pass the SQL statement as a string (or a variable that contains a string) to the mi_prepare( ) function. For example, Figure 34 prepares an unparameterized SELECT statement that obtains column values from the customer table.

stmt_desc = mi_prepare(conn, 
   "SELECT * FROM customer;", NULL)

For more information, see Assembling a Statement String.

Assembling Parameterized Statements

If some column or expression value is provided when the statement actually executes, you assemble the parameterized statement as the statement string. Specify input parameters in the statement text representation of an SQL statement. For a description of an input parameter, see Prepared Statements and Input Parameters.

You indicate the presence of an input parameter with a question mark (?) anywhere within a statement where an expression is valid. You cannot list a program-variable name in the text of an SQL statement because the database server knows nothing about variables declared in the DataBlade API module. You cannot use an input parameter to represent an identifier such as a database name, a table name, or a column name.

For example, Figure 35 shows an INSERT statement that uses input parameters as placeholders for two column values in the customer table.

insrt_stdesc = mi_prepare(conn, 
   "INSERT INTO customer (customer_num, company) \
   VALUES (?,?);", NULL

In Figure 35, the first input parameter is defined for the value of the customer_num column and the second for the value of the company column.

Before the prepared statement executes, your DataBlade API module must assign a value to the input parameter. You pass these input-parameter values as arguments to the mi_exec_prepared_statement( ) or mi_open_prepared_statement( ) function. For more information, see Assigning Values to Input Parameters.

Assigning an Optional Name

You can obtain access to a prepared statement through its statement descriptor. However, other SQL statements that need to reference the prepared statement cannot use a statement descriptor. Therefore, you can assign an optional string name to a prepared SQL statement. Specify a name as the third argument of the mi_prepare( ) function.

WHERE CURRENT OF cursor_name

SELECT customer_num, company FROM customer
WHERE customer_num = 104 FOR UPDATE OF company;

SELECT customer_num, company FROM customer
WHERE customer_num = 104;

The following code fragment uses the mi_prepare( ) statement to assign a name to a cursor and an UPDATE WHERE CURRENT OF statement to update the fifth row in this cursor:

/* Prepare the FOR UPDATE statement */
if ( (stmt1 = mi_prepare(conn, 
            "select * from tab1 for update;",
            "curs1")) == NULL )
   return MI_ERROR;

/* Open the cursor */
if ( mi_open_prepared_statement(stmt1, MI_BINARY,
      MI_QUERY_BINARY, num_params, values, lengths, nulls,
      types, NULL, 0, NULL) != MI_OK )
   return MI_ERROR;

/* Fetch the 5th row */
if ( mi_fetch_statement(stmt1, MI_CURSOR_NEXT, 0, 5) 
      != MI_OK )
   return MI_ERROR;

/* Get values from 5th row */
if ( mi_get_result(conn) != MI_ROWS 
      || mi_next_row(conn, &res) == NULL )
   return MI_ERROR;

/* Update 5th row */
if ( mi_exec("update tab1 set int_col = int_col + 2 \
            where current of curs1;", NULL) != MI_OK )
   return MI_ERROR;

/* Clean up */
if ( mi_close_statement(stmt1) != MI_OK )
   return MI_ERROR;
if ( mi_drop_prepared_statement(stmt1) != MI_OK )
   return MI_ERROR;

The mi_open_prepared_statement( ) function also provides the ability to name the cursor. However, if you specify a cursor name in mi_prepare( ), make sure that you pass a NULL-valued pointer as the cursor name to mi_open_prepared_statement( ). Conversely, if you want to specify the cursor name in mi_open_prepared_statement( ), use a NULL-valued pointer as the cursor name in mi_prepare( ). 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.

If your prepared statement does not fetch rows, pass a NULL-valued pointer as the third argument to mi_prepare( ).

Returning a Statement Descriptor

The mi_prepare( ) function sends the contents of an SQL statement string to the database server, which parses the statement and returns it in an optimized executable format. The function returns a pointer to an explicit statement descriptor (usually called just a statement descriptor). A statement descriptor, MI_STATEMENT, is a DataBlade API structure that contains the information about a prepared SQL statement, including the executable format of the SQL statement.

The following table summarizes the memory operations for a statement descriptor.

A statement descriptor can be identified in either of the following ways:

• As a pointer to an MI_STATEMENT structure, which mi_prepare( ) returns

  The mi_prepare( ) function is a constructor function for a statement descriptor.

• As an integer statement identifier, which the mi_get_id( ) function returns when passed MI_STATEMENT_ID as its second argument

Table 56 lists the DataBlade API accessor functions for an explicit statement descriptor.

You pass a statement descriptor to the other DataBlade API functions that handle prepared statements, including mi_exec_prepared_statement( ), mi_open_prepared_statement( ), mi_fetch_statement( ), mi_close_statement( ), and mi_drop_prepared_statement( ).

Obtaining Input-Parameter Information

From a statement descriptor, you can obtain information about an input parameter once an SQL statement has been prepared. An input parameter indicates a value that is provided when the prepared statement executes. Table 57 lists the DataBlade API accessor functions that obtain input-parameter information from the statement descriptor.

Input-parameter information is available only for the INSERT and UPDATE statements. Support for the UPDATE statement includes the following forms of UPDATE:

• UPDATE with or without a WHERE clause
• UPDATE WHERE CURRENT OF

If you attempt to request input-parameter information for other SQL statements, the input-parameter functions in Table 57 raise an exception.

The statement descriptor stores input-parameter information in several parallel arrays.

All of the input-parameter arrays in the statement descriptor have zero-based indexes. Within the statement descriptor, each input parameter in the prepared statement has a parameter identifier, which is the zero-based position of the input parameter within the input-parameter arrays. When you need information about an input parameter, specify its parameter identifier to one of the statement-descriptor accessor functions in Table 57.

Figure 36 shows how the information at index position 1 of these arrays holds the input-parameter information for the second input parameter of a prepared statement.

Figure 36. Input-Parameter Arrays in the Statement Descriptor

To access information for the nth input parameter, provide an index value of n-1 to the appropriate accessor function in Table 57. The following calls to the mi_parameter_type_id( ) and mi_parameter_nullable( ) functions obtain from the statement descriptor that stmt_desc identifies the type identifier (param_type) and whether the column is nullable (param_nullable) for the second input parameter:

MI_STATEMENT *stmt_desc;
MI_TYPEID *param_type;
mi_integer param_nullable;
...
param_type = mi_parameter_type_id(stmt_desc, 1);
param_nullable = mi_parameter_nullable(stmt_desc, 1);

To obtain the number of input parameters in the prepared statement (which is also the number of elements in the input-parameter arrays), use the mi_parameter_count( ) function.

Sending the Prepared Statement

For a prepared statement to be executed, you must send it to the database server with one of the following DataBlade API functions.

Both these functions support the following parameters.

Once the database server executes the prepared statement, the statement becomes the current statement. The database server sends back the statement results, including whether the current statement was successful. Obtain the status of the current statement with the mi_get_result( ) function. For more information, see Processing Statement Results.

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( ).

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 Assigning 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 Determining 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 on this row cursor, see Queries and Implicit Cursors.

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 (page 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 Figure 37, 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.

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 */
   }

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( ).

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 on how to assign input-parameter values, see Assigning 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 on this cursor, see Defining an Explicit Cursor.

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 Assigning 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.

  End of Client Only
• 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 Defining 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 Determining 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 Fetching 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 Figure 37.

Defining an Explicit Cursor

The control flag of mi_open_prepared_statement( ) allows you to define an explicit cursor to hold the rows that the prepared query returns. You can choose the following cursor characteristics when you define the cursor:

• The cursor type
• The cursor mode
• The cursor lifespan

Defining a Cursor Type

The mi_open_prepared_statement( ) function supports the following types of cursors for holding query rows.

Cursor Type

Description

Sequential cursor

Enables you to move through the rows of the cursor in the forward direction only

You can pass only once through the rows.

Scroll cursor

Enables you to move through the rows of the cursor in the forward and backward directions

You can move back in the rows without having to reopen the cursor; however, the database server stores the data for a scroll cursor in a temporary table. The data can become stale; that is, the data in the cursor is consistent with the data in the database when the cursor is filled, but if the data in the database changes, the data in the cursor does not reflect these changes.

Table 58 shows the control-flag values that determine cursor type and cursor mode.

Defining a Cursor Mode

You can specify one of the following cursor modes for the cursor with the control-flag bit mask.

When you execute a prepared SELECT statement with no FOR UPDATE or FOR READ ONLY clause, the cursor mode you need depends on whether your database is ANSI-compliant, as follows:

• In a database that is not ANSI compliant, the SELECT statement specifies a read-only mode by default.

  You do not need to specify the FOR READ ONLY keywords in the SELECT statement. The only advantage of specifying the FOR READ ONLY keywords explicitly is for better program documentation. To specify an update mode, you must specify the FOR UPDATE keywords in the SELECT statement.

• In an ANSI-compliant database, the SELECT statement specifies an update mode by default.

  You do not need to specify the FOR UPDATE keywords in the SELECT statement. The only advantage of specifying the FOR UPDATE keywords explicitly is for better program documentation. To specify a read-only mode, you must specify the FOR READ ONLY keywords in the SELECT statement.

For more information on the FOR UPDATE and FOR READ ONLY clauses, see the description of the SELECT statement in the IBM Informix: Guide to SQL Syntax.

By default, both the sequential and scroll cursor types have a cursor mode of update (also called read/write). Table 58 shows the cursor types and cursor modes, with the required bit-mask values for the control flag.

Defining a Cursor Lifespan

You can define the lifespan of the cursor with the control-flag bit mask. By default, the database server closes all cursors at the end of a transaction. If your DataBlade API module requires uninterrupted access to a set of rows across transaction boundaries, you can define a hold cursor. A hold cursor can be either a sequential or a scroll cursor.

To define a hold cursor, you specify the MI_SEND_HOLD constant in the control-flag bit mask of the mi_open_prepared_statement( ) function, as the following table shows.

Fetching Rows Into a Cursor

When mi_open_prepared_statement( ) successfully opens a cursor, the cursor is empty, with the cursor position pointing to the first location of the cursor, and the mi_get_result( ) function returns a status of MI_NO_MORE_RESULTS to indicate that the cursor does not contain rows.

Figure 38 shows the state of the explicit cursor that contains one integer column after mi_open_prepared_statement( ) executes.

Figure 38. Row Cursor After mi_open_prepared_statement( )

To populate the open cursor, use the mi_fetch_statement( ) function, which fetches the specified number of retrieved rows from the database server into the cursor. You can perform a fetch operation on an update or a read-only cursor. To fetch rows into a cursor, you must specify the following information to mi_fetch_statement( ):

• The statement descriptor for the prepared statement that returns rows
• The location in the rows on the database server at which to begin the fetch
• The number of rows to fetch into the cursor

The mi_fetch_statement( ) function requests the specified number of retrieved rows from the database server and copies them into the cursor, which is associated with the specified statement descriptor. When mi_fetch_statement( ) completes successfully, the following items are true:

• The cursor contains the number of rows that the num_rows argument specifies.
• The cursor position points to the first of the fetched rows in the cursor.
• The mi_get_result( ) function returns a status of MI_ROWS to indicate that the cursor does contain rows.

With mi_fetch_statement( ), you can request rows at different locations based on the type of cursor that mi_open_prepared_statement( ) has defined. To specify location, mi_fetch_statement( ) has an action argument of type MI_CURSOR_ACTION, which supports the cursor-action constants in the following table.

Figure 39 shows the state of a row cursor that Figure 38 defines after the following mi_fetch_statement( ) executes:

mi_fetch_statement(stmt_desc, MI_CURSOR_NEXT, 0, 0);

Figure 39. Fetching All Retrieved Rows Into a Cursor

Once the rows reside in the cursor, your DataBlade API module can access these rows one at a time with the mi_next_row( ) function. For more information, see Retrieving Query Data.

If you specify a non-zero value for the num_rows argument, mi_fetch_statement( ) fetches the requested number of rows into the cursor. Specify a non-zero value for num_rows if your DataBlade API module needs to handle rows in smaller groups. In this case, you retrieve num_rows number of query rows from the cursor with mi_next_row( ). When mi_next_row( ) indicates that no more rows are in the cursor, you must determine whether to fetch any remaining rows from the database server into the cursor, as follows:

• If you do not need to examine additional rows, exit the mi_next_row( ) and mi_get_result( ) loops normally and close the cursor with mi_close_statement( ).
• If you do need to fetch any rows remaining on the database server into the cursor, execute the mi_fetch_statement( ) function again after the following conditions occur:
  • The mi_get_result( ) function returns MI_DML (for a SELECT statement).
  • The number of query rows that mi_next_row( ) obtains is less than the number of rows that mi_fetch_statement( ) fetches (num_rows) from the database server.

    You can obtain the number of query rows with the mi_result_row_count( ) function.

The mi_fetch_statement( ) for Figure 39 specified a value of zero (0) as the number of rows to fetch, which tells the function to fetch all retrieved rows. Figure 40 shows the state of the row cursor that Figure 38 defines when the mi_fetch_statement( ) function specifies a num_rows argument of three instead of zero, as follows:

mi_fetch_statement(stmt_desc, MI_CURSOR_NEXT, 0, 3);

Figure 40. Fetching First Three Rows into a Cursor

mi_string *cmd = 
   "select order_num from orders \
    where customer_num = ?;";
MI_STATEMENT *stmt;
...
if ( (stmt = mi_prepare(conn, cmd, NULL)) == NULL )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_prepare( ) failed");

values[0] = 104;
types[0] = "integer";
lengths[0] = 0;
nulls[0] = MI_FALSE;

/* Open the read-only cursor to hold the query rows */
if ( mi_open_prepared_statement(stmt, MI_SEND_READ,
      MI_TRUE, 1, values, lengths, nulls, types, 
      "cust_select", retlen, rettypes) 
      != MI_OK )
   mi_db_error_raise(NULL, MI_EXCEPTION,
      "mi_open_prepared_statement( ) failed");

/* Fetch the retrieved rows into the cursor */
if ( mi_fetch_statement(stmt, MI_CURSOR_NEXT, 0, 3) != MI_OK )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_fetch_statement( ) failed");

if ( mi_get_result(conn) != MI_ROWS )
   mi_db_error_raise(NULL, MI_EXCEPTION,
   "mi_get_result( ) failed or found nonquery statement");

/* Retrieve the query rows from the cursor */
if ( !(get_data(conn)) )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "get_data( ) failed");

/* Close the cursor */
if ( mi_close_statement(stmt) == MI_ERROR )
   mi_db_error_raise(NULL, MI_EXCEPTION,
      "mi_close_statement( ) failed");

/* Release resources */
if ( mi_drop_prepared_statement(stmt) == MI_ERROR )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_drop_prepared_statement( ) failed");
if ( mi_close(conn) == MI_ERROR )
   mi_db_error_raise(NULL, MI_EXCEPTION,
      "mi_close( ) failed");

Assigning Values to Input Parameters

For a parameterized SQL statement, your DataBlade API module must perform the following steps:

1. Specify input parameters in the text of the SQL statement.
2. Send the SQL statement to the database server for parsing.
3. Provide input-parameter values when the SQL statement executes.

The mi_prepare( ) statement performs these first two steps for a parameterized statement. For more information, see Preparing an SQL Statement.

The mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions assign values to input parameters when they send a parameterized SQL to the database server for execution. To provide a value for an input parameter, you pass information in several parallel arrays:

• Parameter-value array
• Parameter-value type array
• Parameter-value length array
• Parameter-value null array

These input-parameter value arrays are similar to the input-parameter arrays in the statement descriptor (see Figure 36). They have an element for each input parameter in the prepared statement. However, they are unlike the input-parameter arrays in the statement descriptor in the following ways:

• An input-parameter value array describes the actual value for an input parameter.

  An input-parameter array describes the column with which the input parameter is associated.

• You must allocate and manage an input-parameter value array.

  The DataBlade API does not provide accessor functions for input-parameter value arrays. For each input parameter, your DataBlade API module must declare, allocate, and assign a value to the array.

All of the input-parameter-value arrays have zero-based indexes. Figure 41 shows how the information at index position 1 of these arrays holds the input-parameter-value information for the second input parameter of a prepared statement.

Figure 41. Arrays for Initialization of Input Parameters

You specify the number of input-parameter values in the input-parameter value arrays with the nparams argument of mi_exec_prepared_statement( ) or mi_open_prepared_statement( ).

The following sections provide additional information about each of the input-parameter-value arrays.

Parameter-Value Array

The parameter-value array, values, is the fifth argument of the mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions. Each element of the parameter-value array is a pointer to an MI_DATUM structure that holds the value for each input parameter. The format of this value depends on:

• Whether the control mode for the input-parameter data is text or binary representation:

  The params_are_binary argument of mi_exec_prepared_statement( ) or mi_open_prepared_statement( ) indicates this control mode. For more information on the format of data for different control modes, see Table 55.

• In binary representation, whether the MI_DATUM value is passed by reference or by value:
  • For C UDRs, the data type of the value determines the passing mechanism.
  • For client LIBMI applications, pass all values (regardless of data type) by reference.

For more information, see Contents of an MI_DATUM Structure.

For the prepared INSERT statement in Figure 35, the code fragment in Figure 42 assigns values to the input parameters for the customer_num and company columns. These values are in text representation because the params_are_binary argument of mi_exec_prepared_statement( ) is MI_FALSE.

/* Initialize input parameter for customer_num column */
values[0] = "0"; /* value of '0' for SERIAL customer_num */
lengths[0] = 0; /* SERIAL is built-in type: no length */
types[0] = "serial";
nulls[0] = MI_FALSE;

/* Initialize input parameter for company column */
stcopy("Trievers Inc.", strng);
values[1] = strng;
lengths[1] = stleng(strng); /* CHAR types need length! */
types[1] = "char(20)";
nulls[1] = MI_FALSE;

/* Send INSERT statement to database server for execution along 
 * with the input-parameter-value arrays
 */
mi_exec_prepared_statement(insrt_stdesc, 0, MI_FALSE, 2
   values, lengths, nulls, types, 0, NULL);

/* Initialize input parameter for customer_num column */
values[0] = 0; /* value of 0 for SERIAL customer_num */
lengths[0] = 0; /* SERIAL is built-in type: no length */
types[0] = "serial";
nulls[0] = MI_FALSE;

/* Initialize input parameter for company column */
values[1] = mi_string_to_lvarchar("Trievers Inc.");
lengths[1] = 0; /* CHAR types need length! */
types[1] = "char(20)";
nulls[1] = MI_FALSE;

/* Send INSERT statement to database server for execution along
 * with the input-parameter-value arrays
 */
mi_exec_prepared_statement(insrt_stdesc, 0, MI_TRUE, 2
   values, lengths, nulls, types, 0, NULL);

col1 = 0;
values[0] = &col1;

Parameter-Value Length Array

The parameter-value length array, lengths, is the sixth argument of the mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions. Each element of the parameter-value length array is the integer length (in bytes) of the data type for the input-parameter value.

The meaning of the values in the lengths array depends on the control mode of the input-parameter values, as follows:

• For input-parameter values that are in text representation, the lengths array lists the lengths of the text strings.

  Make sure the lengths value matches the length of the null-terminated string of the input-parameter value (minus the null terminator). Use a library function, such as strlen( ) or stleng( ), to determine the string length.

• For input-parameter values that are in binary representation, the database server does not need to access the entry of the parameter-value length array.

  Lengths are not needed in the following special cases:

  • Input parameters whose data types (such as mi_integer) are passed with fixed lengths
  • Input parameters with string values are passed in varying-length structures

    A varying-length structure holds its own data length.

Parameter-Value Null Array

The parameter-value null array, nulls, is the seventh argument of the mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions. Each element of the parameter-value null array is either:

• MI_FALSE: the input-parameter value is not an SQL NULL value
• MI_TRUE: the input-parameter value is an SQL NULL value

Parameter-Value Type Array

The parameter-value type array, types, is the eighth argument of the mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions. Each element of the parameter-value type array is a pointer to a string that identifies the data type of the input-parameter value. The type names must match those that the mi_type_typename( ) function would generate for the data type.

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. Otherwise, you can pass in a NULL-valued pointer as the types argument.

Determining Control Mode for Query Data

The mi_exec_prepared_statement( ) and mi_open_prepared_statement( ) functions specify the control mode for the data of a prepared query in their bit-mask control argument. To determine the control mode, set the control argument as the following table shows.

Control Mode

Control Argument

Text representation

Zero (default)

Binary representation

MI_BINARY

For mi_exec_prepared_statement( ), MI_BINARY is the only valid control-flag constant for the control argument. Therefore, a default value of zero (0) as the control argument indicates text representation of the data. The following mi_exec_prepared_statement( ) call specifies a control mode of binary representation:

mi_open_prepared_statement(stmt_desc, MI_BINARY, ...);

For mi_open_prepared_statement( ), the control argument indicates the cursor characteristics in addition to the control mode. To specify a text representation, omit the MI_BINARY control-flag constant from the control argument. Including MI_BINARY in the control argument indicates that results are to be returned in binary representation. The following mi_open_prepared_statement( ) call specifies an update scroll cursor and a control mode of binary representation:

mi_open_prepared_statement(
   stmt_desc, 
   MI_BINARY + MI_SEND_SCROLL, 
   ...);

For information on how to specify cursor characteristics to mi_open_prepared_statement( ), see Defining an Explicit Cursor. For more information on the control mode, see Control Modes for Query Data.

Releasing Prepared-Statement Resources

When your DataBlade API module no longer needs a prepared statement, you can release the resources that it uses with the following DataBlade API functions.

Closing a Statement Cursor

For prepared queries (SQL statements that return rows), the statement descriptor has a cursor associated with it. The scope of this cursor is from the time it is opened, with mi_exec_prepared_statement( ) or mi_open_prepared_statement( ), until one of the following events occurs:

• The mi_close_statement( ) function closes the cursor (explicit cursors only).
• The mi_drop_prepared_statement( ) function frees the statement descriptor.
• The mi_close( ) function closes the connection.
• The SQL statement that invoked the C UDR ends.

To conserve resources, use the mi_close_statement( ) function to explicitly close an explicit cursor once your DataBlade API module no longer needs it. The mi_close_statement( ) function is the destructor function for an explicit cursor that is associated with a statement descriptor. That is, it frees the cursor that the mi_open_prepared_statement( ) function opens. Until you drop the prepared statement with mi_drop_prepared_statement( ), you can still reopen an explicit cursor with another call to mi_open_prepared_statement( ).

The mi_close_statement( ) function is not the destructor function for an implicit cursor that is associated with a statement descriptor. That is, it does not free the cursor that the mi_exec_prepared_statement( ) function opens. To close an implicit cursor, use the mi_drop_prepared_statement( ) function.

Dropping a Prepared Statement

A statement descriptor describes a prepared statement. However, this DataBlade API structure is not allocated from the memory-duration pools. Instead, its scope is from the time it is created with mi_prepare( ) until whichever of the following events occurs first:

• The mi_drop_prepared_statement( ) function frees the statement descriptor.
• The mi_close( ) function closes the connection.
• The SQL statement that invoked the C UDR ends.

To conserve resources, use the mi_drop_prepared_statement( ) function to explicitly deallocate the statement descriptor once your DataBlade API module no longer needs it. The mi_drop_prepared_statement( ) function is the destructor function for a statement descriptor. It frees the statement descriptor and any resources (such as an implicit or explicit cursor) that are associated with the statement descriptor. These resources include the prepared statement and any associated row cursor. Once you drop a prepared statement, you must reprepare it before it can be executed again.