Executing the mi_get_result( ) Loop

The mi_get_result( ) function is usually executed in a loop after one of the DataBlade API statement-execution functions (in Table 53) sends a statement to the database server. The function is normally called in the outermost loop of row-retrieval code. This loop executes for each of several states of the database server as it processes statement results. These states are represented as the status of the current statement. The mi_get_result( ) function can return the following status information.

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

Handling Unsuccessful Statements

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 type, 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 Database Server Exceptions.

Handling a DDL Statement

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 DDL statement that executed as the current statement.

The mi_get_result( ) function returns an MI_DDL status for any SQL statement that is valid in a UDR and is not a DML statement (see Table 59). For example, mi_get_result( ) returns the MI_DDL status for a GRANT statement, even though SQL does not strictly consider GRANT as a DDL statement. However, the following SQL statements are not valid with a UDR:

For any valid DDL statement, the mi_get_result( ) loop returns the following states of the database server:

1. An MI_DDL status indicates that the SQL statement has successfully completed.
2. In the next iteration of the mi_get_result( ) loop, mi_get_result( ) returns MI_NO_MORE_RESULTS.

Handling a DML Statement

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. SQL contains the DML statements that Table 59 lists.

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.

SELECT COUNT(*) FROM mytable;

Figure 43 shows a sample function, named handle_dml( ), that handles the MI_DML statement status that mi_get_result( ) returns.

mi_integer handle_dml(conn)
   MI_CONNECTION *conn;
{
   char         *cmd;
   mi_integer   count;

   /* What kind of statement was it? */
   cmd = mi_result_command_name(conn);
   DPRINTF("trc_class", 11, 
      ("Statement executed was %s", cmd));

   /* Get # rows affected by statement */
   if ( (count = mi_result_row_count(conn)) == MI_ERROR )
      {
      DPRINTF("trc_class", 11, 
         ("Cannot get row count\n"));
      return( -1 );
      }
   else if ( count = 0 )
      {
      DPRINTF("trc_class", 11, 
         ("No rows returned from query\n"));
      }
   else
      DPRINTF("trc_class", 11, 
         ("Rows Returned\n"));

   return ( count );
}

The handle_dml( ) function in Figure 43 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.

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: The get_results( ) Function.

For a successful UPDATE, INSERT, and DELETE statement, the mi_get_result( ) loop returns the following states of the database server:

1. An MI_DML status indicates that the DML statement has successfully completed.
2. 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, the mi_get_result( ) loop returns the following states of the database server:

1. An MI_ROWS statement status indicates that the current statement is a query that has executed successfully and whose cursor is ready for processing of query rows.
2. 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 (or EXECUTE FUNCTION) has successfully completed.
3. 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.

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. A query can be instigated by a SELECT or an EXECUTE FUNCTION statement. 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.

Handling "No More 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