Informix DataBlade API Programmer's Manual Executing SQL Statements

Once a DataBlade API statement-execution function executes, the SQL statement that it sent is the most recent SQL statement on the connection. This most recent SQL statement is called the current statement. Information about the current statement is associated with a connection. Only one statement is current at a time. After you send the current statement to the database server for execution, your DataBlade API module must process the statement results by:

• Determining the status of the current statement, including whether results are available
• Retrieving any results

Retrieving the results of an SQL statement is a multiphase process that involves several levels of nested iteration, as the following table shows.

The first step in processing statement results is to determine the status of the current statement with the mi_get_result() function, as follows:

• Execute the mi_get_result() function in a loop that iterates for each current statement.
• Interpret the statement status that mi_get_result() returns for each current statement.

For a sample function that shows one way to use mi_get_result() to process statement results, see Example: get_data() Function.

The mi_get_result() function is usually executed in a loop after one of the DataBlade API statement-execution functions in Figure 8-1 sends a statement to the database server. The function is normally called in the outermost loop of row-retrieval code. This loop terminates when mi_get_result() returns the status MI_NO_MORE_RESULTS. Think of the mi_get_result() loop as an iteration over the states of the database server.

The mi_get_result() function returns the status of the current statement on a particular connection. The function can provide the following status information about the current statement.

You can use a switch statement based on these statement-status constants to determine how to handle the status of the current statement.

The mi_get_result() function returns a status of MI_ERROR to indicate that the current statement did not execute successfully. When mi_get_result() returns this status, you can use the mi_db_error_raise() function to raise a database server exception. If you have registered a callback on the MI_Exception event, you can obtain an SQL status variable (SQLCODE or SQLSTATE) from the error descriptor that the database server passes to the callback. This SQL status variable can help determine the cause of the failure. For more information on how to handle the MI_Exception event, see Handling Database Server Exceptions.

The mi_get_result() function returns a status of MI_DDL to indicate that the current statement was a DDL statement that has successfully executed.

When you receive the MI_DDL statement status, you can use the mi_result_command_name() function to obtain the name of the executed statement. For any valid DDL statement, mi_get_result() first returns an MI_DDL status to indicate that the DDL statement has successfully completed. The next iteration of the mi_get_result() function returns a MI_NO_MORE_RESULTS statement status to indicate that statement processing is complete.

The mi_get_result() function returns a status of MI_DML to indicate that the current statement is a data manipulation (DML) statement that has successfully executed. The SQL language contains the following DML statements.

Tip: The mi_get_result() function returns the MI_DML status when the current statement is the EXECUTE FUNCTION statement. This SQL statement can return rows of data and therefore is handled in the same way as the SELECT statement. However, execution of the EXECUTE PROCEDURE statement causes a statement status of MI_DDL because this SQL statement never returns rows.

When you receive the MI_DML statement status, you can use the DataBlade API functions in the following table to obtain information about the results of the current statement.

Important: If you want a count of the numbers of rows that satisfy a given query, but you do not want the data in the rows, you can run a query that uses the COUNT aggregate more efficiently than you can run a query that returns the actual rows. For example, the following query counts the number of rows in mytable:

SELECT COUNT(*) FROM mytable;

Figure 8-16 shows a sample function, called handle_dml(), that handles the MI_DML statement status from mi_get_result().

Figure 8-16 Sample Function to Handle MI_DML Statement Status

The handle_dml() function in Sample Function to Handle MI_DML Statement Status uses the mi_result_command_name() and mi_result_row_count() functions to obtain additional information about the DML statement. The function returns the number of rows affected (from mi_result_row_count()) to the calling routine.

The handle_dml() function in Figure 8-16 assumes it is called from within a C UDR because it uses the DPRINTF statement. The DPRINTF statement is part of the DataBlade API tracing feature and available only to C UDRs. The first DPRINTF statement in Figure 8-16 sends the name of the current statement to a trace file when the current trace level is 11 or higher. For more information on tracing, see Using Tracing.

For the handle_dml() function to execute in a client LIBMI application, it would need to replace the DPRINTF statements with a client-side output function such as printf() or fprintf(). The following line shows the use of the printf() function to display the name of the current statement:

printf("Statement executed was %s", cmd);

For an example of how to call handle_dml(), see the MI_DML case of the switch statement in Example: get_results() Function.

For UPDATE, INSERT, and DELETE, mi_get_result() first returns an MI_DML status to indicate that the DML statement has successfully completed. In the next iteration of the mi_get_result() loop, mi_get_result() returns MI_NO_MORE_RESULTS.

For a successful SELECT (or EXECUTE FUNCTION) statement, mi_get_result() first returns an MI_ROWS statement status to indicate that the current statement is a query that has executed successfully and whose cursor is ready for processing of query rows. After all query rows are retrieved, the next iteration of the mi_get_result() loop returns an MI_DML statement status to indicate that the SELECT has successfully completed. The next iteration of the mi_get_result() loop returns the MI_NO_MORE_RESULTS status to indicate that statement processing is complete. For more information, see Handling Query Rows.

The mi_get_result() function returns a status of MI_ROWS to indicate that a current statement is a query that has successfully executed and a cursor is open. The MI_ROWS statement status does not indicate that rows are in the cursor. If the query has not found any matching rows (the NOT FOUND condition), mi_get_result() still returns MI_ROWS. To retrieve rows from the cursor, use the mi_next_row() statement. If no rows exist in the cursor, mi_next_row() returns a NULL-valued pointer. For more information, see Retrieving Query Data.

The mi_get_result() function returns a status of MI_NO_MORE_RESULTS to indicate that statement processing for the current statement is complete. The function can return MI_NO_MORE_RESULTS when any of the following conditions occur for the current statement:

• When the cursor is empty after mi_open_prepared_statement() has opened the cursor and before mi_fetch_statement() has fetched rows into this cursor
• After mi_next_row() has retrieved the last row from the cursor
• When the query is complete, after mi_query_finish() or mi_query_interrupt() has executed
• When any non-query statement is complete: after mi_get_result() has returned the MI_DML or MI_DDL statement status

The following user function, get_results(), demonstrates the mi_get_result() row-retrieval loop, controlled with the mi_get_result() function. It also demonstrates the use of the mi_result_command_name() function to get the name of the current statement and the mi_result_row_count() function to get the number of rows affected by this statement.

/*
** FUNCTION: get_results()
** PURPOSE: Get results of current statement. 
**                    Obtain the kind of statement and the number of
**                              rows affected.
**                    Return the number of rows affected.
**
** CALLED BY: send_statement() , see page  8-75.
*/
#include "mi.h"
 
mi_integer get_results(MI_CONNECTION * conn)
{
	mi_integer   count;
	mi_integer   result;
	char         cmd [25];

	while ( (result = mi_get_result(conn) ) 
			!= MI_NO_MORE_RESULTS )
		{
		switch( result )
			{
			case MI_ERROR:
				mi_db_error_raise(conn, MI_EXCEPTION, 
		"Could not get statement results (mi_get_result)\n");

			case MI_DDL:
				count = 0;
				break;

			case MI_DML:
				count = handle_dml(conn); 
				break;

			case MI_ROWS:
				count = get_data(conn);
				break;

			default:
				mi_db_error_raise(conn, MI_EXCEPTION, 
		"Unknown statement results (mi_get_result)\n");

			} /* end switch */
		} /* end while */

	return ( count );
}

When a query returns rows of data, the mi_get_result() loop in get_results() executes three times:

• The first iteration of the mi_get_result() loop returns MI_ROWS to indicate that the query has successfully opened a cursor.

The get_results() function executes the MI_ROWS case of the switch statement. This function then calls another user function, get_data(), to iterate over all query rows. For the implementation of the get_data() function, see Example: get_data() Function.

• The second iteration of the mi_get_result() loop returns MI_DML to indicate that the cursor processing has completed and the query has successfully completed.

The get_data() function has handled the rows in the cursor so no more query rows remain to be processed. The get_results() function executes the MI_DML case of the switch statement, which calls the handle_dml() function to obtain the name and number of statements from the current statement. For the implementation of the handle_dml() function, see Figure 8-16 on page 8-50.

• The third iteration of the mi_get_result() loop returns MI_NO_MORE_RESULTS to indicate that processing for the query is complete.

The MI_NO_MORE_RESULTS value from mi_get_result() causes the mi_get_result() loop to terminate.