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

The following figure shows the state of the explicit cursor that contains one integer column after mi_open_prepared_statement() executes.
Figure 1: Row cursor after mi_open_prepared_statement()

begin figure description - This figure is described in the surrounding text. - end figure description
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.
Cursor-action flag Description Type of cursor
MI_CURSOR_NEXT Fetches the next num_rows rows, starting at the current retrieved row on the database server Sequential

Scroll
MI_CURSOR_PRIOR Fetches the previous num_rows rows, starting at the current retrieved row Scroll
MI_CURSOR_FIRST Fetches the first num_rows rows Sequential

Scroll
MI_CURSOR_LAST Fetches the last num_rows rows Sequential

Scroll
MI_CURSOR_ABSOLUTE Moves jump rows into the retrieved rows and fetches num_rows rows Sequential (as long as the jump argument does not move the cursor position backward)

Scroll
MI_CURSOR_RELATIVE Moves jump rows from the current retrieved row and fetch num_rows rows Sequential (as long as the jump argument is a positive number)

Scroll
The following figure shows the state of a row cursor that Row cursor after mi_open_prepared_statement() defines after the following mi_fetch_statement() executes: 
mi_fetch_statement(stmt_desc, MI_CURSOR_NEXT, 0, 0);
Figure 2: Fetching all retrieved rows into a cursor

begin figure description - This figure is described in the surrounding text. - end figure description

After the rows are 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 nonzero value for the num_rows argument, mi_fetch_statement() fetches the requested number of rows into the cursor. Specify a nonzero 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 Fetching all retrieved rows into a cursor specified a value of zero, as the number of rows to fetch, which tells the function to fetch all retrieved rows. Fetching first three rows into a cursor shows the state of the row cursor that Row cursor after mi_open_prepared_statement() 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 3: Fetching first three rows into a cursor

begin figure description - This figure is described in the surrounding text. - end figure description
Server only:
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 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");

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 Assign values to input parameters.