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

Assembling a Statement String

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 32 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);

• 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 database server returns.

  Figure 33 shows the SELECT statement of Figure 32 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);

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.

Sending an SQL Statement

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 that mi_exec( ) sends, the statement becomes the current statement. The current statement is the most recent SQL statement on the connection. Only one statement per connection is current. The database server sends back the results of the current statement, including whether the current statement was successful.

The mi_exec( ) function creates an implicit statement descriptor to hold the information about the current statement. The following table summarizes the memory operations for an implicit statement descriptor.

Table 54 lists the DataBlade API accessor functions for the implicit statement descriptor that mi_exec( ) creates.

You obtain the status of the current statement with the mi_get_result( ) function. For more information, see Processing Statement Results.

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

1. Opens an implicit cursor to hold the query rows
2. Reads the query rows into the open cursor

The Implicit Row 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.

When the mi_exec( ) function successfully fetches the query results 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.

Control Modes for 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-terminated strings. Data in its text representation is often called a literal value.
• 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.

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

Control Mode

Control-Flag Value

Text representation

MI_QUERY_NORMAL

Binary representation

MI_QUERY_BINARY

In the send_statement( ) function (page Example: The send_statement( ) Function), 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.

Example: The send_statement( ) Function

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: To send an SQL statement to the database server for
 *          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, MI_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: The get_results( ) Function.