Assign an optional name

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 a name as the third argument of the mi_prepare() function.

Server only:

The last argument to mi_prepare() specifies the cursor name for the prepared statement. Assigning a cursor 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 about the FOR UPDATE keywords of SELECT with databases that are ANSI compliant and not ANSI compliant, see Define a cursor mode.

The following code fragment uses the mi_prepare() statement to assign a name to a cursor and an UPDATE WHERE CURRENT OF statement to update the fifth row in this cursor:

/* Prepare the FOR UPDATE statement */
if ( (stmt1 = mi_prepare(conn, 
            "select * from tab1 for update;",
            "curs1")) == NULL )
   return MI_ERROR;

/* Open the cursor */
if ( mi_open_prepared_statement(stmt1, MI_BINARY,
      MI_QUERY_BINARY, num_params, values, lengths, nulls,
      types, NULL, 0, NULL) != MI_OK )
   return MI_ERROR;

/* Fetch the 5th row */
if ( mi_fetch_statement(stmt1, MI_CURSOR_NEXT, 0, 5) 
      != MI_OK )
   return MI_ERROR;

/* Get values from 5th row */
if ( mi_get_result(conn) != MI_ROWS 
      || mi_next_row(conn, &res) == NULL )
   return MI_ERROR;

/* Update 5th row */
if ( mi_exec("update tab1 set int_col = int_col + 2 \
            where current of curs1;", NULL) != MI_OK )
   return MI_ERROR;

/* Clean up */
if ( mi_close_statement(stmt1) != MI_OK )
   return MI_ERROR;
if ( mi_drop_prepared_statement(stmt1) != MI_OK )
   return MI_ERROR;

The mi_open_prepared_statement() function also provides the ability to name the cursor. However, if you specify a cursor name in mi_prepare(), make sure that you pass a NULL-valued pointer as the cursor name to mi_open_prepared_statement(). Conversely, if you want to specify the cursor name in mi_open_prepared_statement(), use a NULL-valued pointer as the cursor name in mi_prepare(). If you specify a cursor name in both mi_prepare() and mi_open_prepared_statement(), the DataBlade® API uses the cursor name that mi_open_prepared_statement() provides.

If your prepared statement does not fetch rows, pass a NULL-valued pointer as the third argument to mi_prepare().

Client only: The last argument to mi_prepare() specifies the statement name for the prepared statement. The cursor_name argument of mi_open_prepared_statement() specifies the cursor name for the prepared statement. If you do not need to assign a statement name, pass a NULL-valued pointer as the last argument to mi_prepare().