Informix DataBlade API Programmer's Manual Executing SQL Statements

To execute an SQL statement, a DataBlade API module must send the SQL statement to the database server, where the statement is actually executed. The DataBlade API provides the following statement-execution functions for use in a DataBlade API module:

• mi_exec()
• mi_exec_prepared_statement()
• mi_open_prepared_statement()

All of these functions perform the same basic task: they send a string representation of an SQL statement to the database server, which executes it and returns statement results. The mi_exec() function is the simplest way to execute an SQL statement.

This section provides a summary of factors to consider when choosing the DataBlade API statement-execution function to use. It then describes the two methods for statement execution.

Tip: Before you use a DataBlade API function that sends an SQL statement to the database server, make sure you obtain a valid connection descriptor.

Choosing the DataBlade API Function

Figure 8-1 shows the functions that the DataBlade API provides to send SQL statements to the database server for execution.

As the preceding table shows, you need to consider the following factors when deciding which DataBlade API statement-execution function to use:

• What type of SQL statement do you need to send?
• Does your SQL statement contain input parameters?
• If the SQL statement is a query, can you use an implicit cursor to access the retrieved rows?

Choose the DataBlade API statement-execution function that is appropriate for the needs of your DataBlade API application.

The DataBlade API statement-execution functions can execute the following statement types:

• Your SQL statement does not return rows of data; that is, it is not a SELECT statement (or an EXECUTE FUNCTION statement that executes an iterator function).

Most SQL statements do not return rows. For example, all data definition (DDL) statements and most data manipulation (DML) statements only return a status to indicate their success.

• Your SQL statement does return rows of data.

The following SQL statements return rows:

• The SELECT statement
• The EXECUTE FUNCTION statement (when the user-defined function returns more than one row of data)

An SQL statement that returns rows is often called a query because it asks the database server to answer a question: which rows match?

Tip: The term "query" is sometimes used to refer to any SQL statement. However, this manual uses the more specific definition of "query": an SQL statement that returns rows.

The following table shows how to choose a DataBlade API statement-execution function based on SQL statement type.

A prepared SQL statement is the parsed version of an SQL statement. The database server prepares an SQL statement for execution at a later time. Preparing a statement allows you to separate the parsing and execution phases of the statement execution. When you prepare a statement, you send the statement to the database server to be parsed. The database server checks the statement for syntax errors and creates an optimized version of the statement for execution.

Therefore, you only need to prepare an SQL statement once. You can then execute the statement multiple times. Each time you execute the statement you avoid the parsing phase. Prepared statements are useful for SQL statements that execute often in your DataBlade API module.

SQL statements that have missing column or expression values are called parameterized statements because you use input parameters as placeholders for missing column values or expressions. An input parameter is a placeholder in an SQL statement that indicates that the actual column value is provided at runtime. You can specify input parameters in the statement text representation of an SQL statement for the following reasons:

• A column or expression value is unknown at the time you prepare the SQL statement.
• A column or expression value changes for each execution of the SQL statement.

For a parameterized SQL statement, your DataBlade API module must provide the following information to the database server for each of its input parameters.

You can also obtain information about the input parameters once the parameterized statement is prepared. For more information, see Obtaining Input-Parameter Information.

Therefore, a DataBlade API module must prepare an SQL statement for the following reasons:

• Increase performance by reducing the number of times that the database server parses and optimizes the statement.
• Execute a parameterized SQL statement and provide different input-parameter values each time the statement executes.

The following table shows how to choose a DataBlade API statement-execution function based on whether the SQL statement needs to be prepared.

The mi_exec_prepared_statement() and mi_open_prepared_statement() functions provide argument values for specifying the input-parameter values when they execute the statement. You can also use these functions to execute prepared statements that do not have input parameters.

When a DataBlade API statement-execution function executes a query, it must create a place to hold the resulting rows. Therefore, each of these functions automatically creates a row cursor (often called simply a cursor). The row cursor is an area of memory that serves as a holding place for rows that the database server has retrieved.

The simplest way to hold the rows of a query is to use an implicit cursor, which is defined with the following characteristics.

Most DataBlade API modules can use an implicit cursor for accessing rows. However, if the cursor characteristics of the implicit cursor are not adequate for the needs of your DataBlade API module, you can define an explicit cursor with any of the following cursor characteristics.

For more information on these cursor characteristics, see Defining an Explicit Cursor.

The following table shows how to choose a DataBlade API statement-execution function based on the type of cursor that the query requires.

With the mi_open_prepared_statement() function, you can specify an explicit cursor to hold the query rows. In addition, you can assign a name to the cursor that you can use in other SQL statements.

The mi_exec() function provides the simplest way to send a basic SQL statement to the database server for execution. A basic SQL statement is one that does not need to be prepared. That is, the statement does not execute many times in the DataBlade API module or it does not contain input parameters. To send a basic SQL statement to the database server for execution, take the following steps:

• Assemble a statement string, which contains the SQL statement to execute.
• Send the statement string to the database server with mi_exec().

The database server parses the statement string, optimizes it, executes it, and sends back the statement results.

The mi_exec() function passes the SQL statement to the database server as a statement string, which is a text representation of the SQL statement. To execute a statement with mi_exec(), the statement string must include the entire SQL statement; that is, it cannot contain any input parameters.

You can assemble this statement string in the following ways:

• If you know all the information at compile time, assemble the statement as a fixed string.

If you know the whole statement structure, you can specify the string itself as the argument to mi_exec(), as the following line shows:

mi_exec(conn,

'select company from customer where \

customer_num = 101', MI_QUERY_BINARY);

• If you do not know all the information about the statement at compile time, you can use the following features to assemble the statement string:
• Character variables can hold the identifiers in the SQL statement (column names or table names) or parts of the statement like the WHERE clause. They can also contain keywords of the statement.

You can then build the SQL statement as a series of string operations, as Figure 8-2 shows

mi_string stmt_txt[30]; mi_string fld_name[15]; ... stcopy("select ", stmt_txt); fld_name = obtain_fldname(...); stcat(fld_name, stmt_txt); stcat("from customer where customer_num = 101", stmt_txt); ... mi_exec(conn, stmt_txt, MI_QUERY_BINARY);

Figure 8-2 Assembling a SELECT Statement from a Character String

.

• If you know what column values the statement specifies, you can declare program variables to provide column values that are needed in a WHERE clause or to hold column values that are returned by the database server.

Figure 8-3 shows the SELECT statement of Figure 8-2 on page 8-10 changed so that it uses a variable to determine the customer number dynamically.

mi_string stmt_txt[30]; mi_integer cust_num; ... stcopy("select company from customer where customer_num = ", stmt_txt); cust_num = obtain_custnum(...); stcat(cust_num, stmt_txt); ... stmt_desc = mi_exec(conn, stmt_txt, MI_QUERY_BINARY);

Figure 8-3 Using a Variable to Assemble a SELECT Statement

The statement string can contain multiple SQL statements. Each SQL statement must be terminated with the semicolon (;) symbol. For more information, see Executing Multiple SQL Statements.

The mi_exec() function is for the execution of basic SQL statements, both queries and other valid SQL statements. In a DataBlade API module, use the following DataBlade API functions to execute a basic SQL statement.

Once the database server executes the statement, it sends back the statement results, including whether the statement was successful and any query data. After mi_exec() executes, the SQL statement it sent is the most recent SQL statement on the connection. This most recent SQL statement is called the current statement. Only one statement per connection is current. The mi_exec() function creates an implicit statement descriptor to hold the information about the current statement. You obtain the status of the current statement with the mi_get_result() function. For more information, see Processing Statement Results.

Tip: The return value that the mi_exec() function returns does not indicate the success of the current statement. It indicates if mi_exec() was able to successfully send the statement to the database server.

When mi_exec() executes a query, it performs the following additional steps:

• Open an implicit cursor to hold the query rows.
• Read the query rows into the open cursor.

When mi_exec() executes a query, it automatically opens an implicit cursor to hold the resulting rows. This cursor is associated with the current statement and is stored as part of the connection descriptor. Therefore, only one cursor per connection can be current. For more information, see Queries and Implicit Cursors.

Tip: If the implicit cursor that mi_exec() 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 Defining an Explicit Cursor.

When the mi_exec() function successfully fetches the query results into the cursor:

• the cursor position points to the first row of the cursor.
• 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 data that the database server returns for a query can be in one of two control modes:

• In text representation, the query data is represented as null-tcrminated strings.
• In binary representation, the query data is represented in its internal format; that is, in the format that the database server uses to store the value.

Figure 8-4 shows the format of different data types in the two control modes.

The mi_exec() function indicates the control mode of the query with a bit-mask control argument, which is one of the following flags.

In the send_statement() function (page 8-15), mi_exec() sets the control mode of the query data to text representation.

To determine the control mode for query data, use the mi_binary_query() function. The mi_binary_query() function determines the control mode for data of the current statement.

The send_statement() function takes an existing open connection and an SQL statement string as arguments and sends the statement to the database server with the mi_exec() function. It specifies text representation for the query results.

/* FUNCTION: send_statement()
** PURPOSE: send an SQL statement to the database server for
**                    to execution.
**
** CALLED BY: Called from within a C user-defined function to
**                        execute a basic SQL statement
**/
 
mi_integer
send_statement(MI_CONNECTION *conn, mi_string *stmt)
{
	mi_integer count;
 
	/* Send the statement, specifying results be sent 
	** in their text representation (MI_QUERY_NORMAL) */
	if ( MI_ERROR == mi_exec(conn, stmt, MI_QUERY_NORMAL) )
		{
		 mi_db_error_raise(conn, EXCEPTION, 
			"mi_exec failed\n");
		}

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

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

	return ( count );
}

The send_statement() function calls another user function, get_results(), to examine the status of the current statement. For the implementation of the get_results() function, see Example: get_results() Function.

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.

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:

• Send a statement string to the database server for parsing.
• Assign an optional name to the SQL statement.
• Return a pointer to a statement descriptor for the prepared statement.

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

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

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 8-5 prepares an unparameterized SELECT statement that obtains column values from the customer table.

Figure 8-5 Preparing an Unparameterized Statement

For more information, see Assembling the Statement String.

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 8-6 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 8-6, 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.

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 the desired statement name as the third argument of the mi_prepare() function. The statement name that you assign with mi_prepare() also applies to any cursor that the prepared statement might use.

Assigning a statement name is useful for a statement that includes an update cursor so that an UPDATE or DELETE statement that contains the following clause can reference the cursor in this clause:

WHERE CURRENT OF cursor_name

You can specify an update cursor in the syntax of the SELECT statement that you prepare, as the following versions of the SELECT statement show:

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


SELECT customer_num, company FROM customer
WHERE customer_num = 104  

For more information on the FOR UPDATE keywords of SELECT with databases that are ANSI compliant and not ANSI compliant, see Defining Cursor Mode.

The mi_open_prepared_statement() function also provides the ability to name the cursor. However, if you specify a statement name in mi_prepare(), make sure that you pass a NULL-valued pointer as the cursor name to mi_open_prepared_statement(). Generally, statements are named with mi_prepare().

If you do not need to assign a statement name, pass a NULL-valued pointer as the last argument to mi_prepare().

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

Figure 8-7 lists the DataBlade API accessor functions for a statement descriptor.

Important: To DataBlade API modules, the statement descriptor (MI_STATEMENT) is an opaque C structure. Do not access the internal fields of this structure directly. Informix does not guarantee that the internal structure of the MI_STATEMENT will not change in future releases. Therefore, to create portable code, always use these accessor functions to obtain prepared-statement information.

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

Tip: The DataBlade API also provides several private functions that use statement descriptors. These private functions are only available to DataBlade partners for special DataBlade functionality. For more information on private statement-descriptor functions, see your Informix sales representative.

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. Figure 8-8 lists the DataBlade API accessor functions that obtain input-parameter information from the statement descriptor.

Important: To DataBlade API modules, the input-parameter information in the statement descriptor (MI_STATEMENT) is part of an opaque C data structure. Do not access the internal fields of this structure directly. Informix does not guarantee that the internal structure of the MI_STATEMENT structure will not change in future releases. Therefore, to create portable code, always use these accessor functions to obtain input-parameter information.

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 Figure 8-8 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 Figure 8-8 on page 8-21.

Figure 8-9 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 8-9 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 Figure 8-8 on page 8-21. 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.

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.

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:

• Bind 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.

• Send 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 mi_exec_prepared_statement() executes a query, it performs the following additional steps:
• Open an implicit cursor to hold the query rows.
• Read 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.

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 Defining 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.
• 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 8-15) 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_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:

• Bind 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.

• Send the prepared statement to the database server for execution.
• Create and open a 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 row cursor, see Defining an Explicit Cursor.

Tip: The mi_open_prepared_statement() function performs the same basic task for a DataBlade API module as the SQL statement OPEN statement does for an 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

To use an internally-generated unique name for the cursor, specify a NULL-valued pointer for the cursor_name argument. For more information, see Assigning an Optional Statement Name.

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

• How many 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 cursor, use the mi_fetch_statement() function. For more information, see Fetching Rows Into a 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

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

Figure 8-10 shows the control-flag values that determine cursor type and 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 an ANSI-compliant database, 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 Informix Guide to SQL: Syntax.

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

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

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.
• the mi_get_result() function returns a status of MI_NO_MORE_RESULTS to indicate that the current cursor does not contain rows.

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

Figure 8-11 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 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.

Figure 8-12 shows the state of a row cursor that Figure 8-11 on page 8-33 defines after the following mi_fetch_statement() executes:

mi_fetch_statement(stmt_desc, MI_CURSOR_NEXT, 0, 0);



 






Figure 8-12  
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 8-12 on page 8-35 specified a value of zero (0) as the number of rows to fetch, which tells the function to fetch all retrieved rows. Figure 8-13 shows the state of the row cursor that Figure 8-11 on page 8-33 defines when the mi_fetch_statement() function specifies a num_rows argument of three instead of zero:

mi_fetch_statement(stmt_desc, MI_CURSOR_NEXT, 0, 3);

 






Figure 8-13  
Fetching First Three Rows into a Cursor 

The following code fragment uses the mi_open_prepared_statement() function to assign an input-parameter value, execute a SELECT statement, and retrieve the query rows:

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 non-query 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");

This code fragment sends its input-parameter value in binary representation. The code fragment is part of a C UDR because it passes the INTEGER input-parameter value by value. For more information, see 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 the 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 8-9 on page 8-23). They have an element for each input parameter in the prepared statement. However, unlike the input-parameter arrays in the statement descriptor:

• input-parameter value arrays describe the actual value for an input parameter.

Input-parameter arrays describe the column with which the input parameter is associated.

• you must allocate and manage the input-parameter value arrays.

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

All of the input-parameter-value arrays have zero-based indexes. Figure 8-14 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 8-14 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.

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 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 Figure 8-4 on page 8-13.

• 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 The MI_DATUM Value.

For the prepared INSERT statement in Figure 8-6 on page 8-18, the code fragment in Figure 8-15 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 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);

The following code fragment initializes the input parameters to the same values but it assigns these values in binary representation instead of text representation:

/* 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 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);

In the preceding code fragment, the first element of the values array is assigned an integer value. Because this code executes in a C UDR, the integer value in the MI_DATUM of this array must be passed by value.

In a client LIBMI application, all values in MI_DATUM must be passed by reference. Therefore, to assign values to input parameters within a client LIBMI application, you must assign all values in the values array as pointers to the actual values.

The preceding code fragment assumes that it executes within a C UDR because it passes the value for the first input parameter (an INTEGER column by value. In a client LIBMI application, you cannot use the pass-by-value mechanism. Therefore, the assignment to the first input parameter must pass a pointer to the integer value, as follows:

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

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.

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

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.

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.

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.

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.

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

• The mi_close_statement() function closes the cursor.
• 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 the cursor once your DataBlade API module no longer needs it. The mi_close_statement() function is the destructor function for a cursor that is associated with a statement descriptor. That is, it frees the implicit cursor that the mi_exec_prepared_statement() opens or the explicit 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().

Tip: The mi_close_statement() function performs the same basic task for a DataBlade API module as the SQL statement CLOSE statement does for an Informix ESQL/C application. 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 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.

A DataBlade API statement-execution function can send more than one SQL statement to the database server at a time. In this case, the statement string contains several SQL statements, each separated by a semicolon (;). When the statement string contains more than one SQL statement, the mi_get_result() function executes for each statement in the string.

Suppose you send the following statement string for execution:

"insert into tab1 (id) values (1); \
  insert into tab1 (id) values (2); \
  insert into tab1 (id) values (3); "

For the preceding statement string, the mi_get_result() function executes four times, three times returning an MI_DML status for each INSERT statement and once to return the final MI_NO_MORE_RESULTS status. For more information on mi_get_result(), see Processing Statement Results.

Keep in mind that the effects of one part of the statement string are not visible to other parts. Therefore, if one SQL statement depends on an earlier one, do not put them both in the same statement string. For example, the following statement string causes an error:

mi_exec(myconn, "create table tab1(a int, b int); \
	insert into tab1 values (1,2);",
	MI_QUERY_NORMAL);

The preceding mi_exec() call generates an error occurs because the INSERT statement cannot see the result of the CREATE TABLE statement. The solution is to call mi_exec() twice, as follows:

/* Execute CREATE TABLE in first mi_exec() call */
mi_exec(myconn, "create table tab1 (a integer, b integer);",
	MI_QUERY_NORMAL);
mi_query_finish(myconn);

/* Execute INSERTin second mi_exec() call */
mi_exec(myconn, "insert into tab1 values (1,2);",
	MI_QUERY_NORMAL);
mi_query_finish(myconn);