Examples of using PER_STMT_EXEC memory duration

For example, suppose the a_func() user-defined function allocates PER_STMT_EXEC memory. The following code fragment shows a UDR that calls a_func() in a noncursor statement that executes twice.

mi_integer udr_with_prepared_stmt()
{
   ...
   stmt3 = mi_prepare(conn, 
      "insert into tab3 values (a_func(87));", NULL);

   /* 1st execution of prepared INSERT */
   mi_exec_prepared_statement(stmt3, ...); 

   /* Code that needs to access PER_STMT_EXEC memory is here */
   ...

   /* 2nd execution of prepared INSERT */
   mi_exec_prepared_statement(stmt3, ...); 
   ...
   return stat;
}

The following code fragment shows use of a_func() in a cursor statement.

mi_integer get_orders(start_with_cust, end_with_cust)
   mi_integer start_with_cust;
   mi_integer end_with_cust;
{
   mi_string *cmd = 
      "select order_num, a_func(order_num) from orders \
       where customer_num = ?;";
   MI_STATEMENT *stmt;
   mi_integer i;
...
if ( (stmt = mi_prepare(conn, cmd, NULL)) == NULL )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_prepare() failed");

if ( start_with_cust > end_with_cust )
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "Arguments invalid.");

for ( i = start_with_cust; i <= end_with_cust; i++)
   {
   values[0] = i;
   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 non-query 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");

   /* Code that needs to access PER_STMT_EXEC memory is here. */
   ...

   } /* end for */

/* 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");
}