Function descriptors within an SQL command

When you pass a public connection descriptor to one of the Fastpath look-up functions, the look-up function allocates the function descriptor with a PER_COMMAND memory duration. Therefore, the function descriptor remains allocated for the duration of the SQL command that invokes the UDR.

For a list of Fastpath look-up functions, see Fastpath look-up functions.

To cache the function descriptor, save its address in the MI_FPARAM structure of the UDR. The MI_FPARAM structure has a PER_COMMAND duration. Therefore, storing the function-descriptor address in the user state of MI_FPARAM guarantees that all UDR invocations within the SQL command can access it. You can also cache the connection description in MI_FPARAM. When the SQL command completes, the function descriptor, the connection descriptor, and the MI_FPARAM structure are deallocated.

The following code fragment for the non_optimal() UDR opens a connection and obtains the function descriptor for the user-defined function equal() for each invocation of non_optimal():
mi_integer non_optimal(arg1, arg2, fparam)
   mi_integer arg1, arg2;
   MI_FPARAM *fparam;
{
   MI_CONNECTION *conn=NULL;
   MI_FUNC_DESC *func_desc=NULL;
   MI_DATUM func_result;
   mi_integer func_error;
   mi_string *func_sig="equal(int, int)";

/* Open a connection */
if ( (conn = mi_open(NULL, NULL, NULL)) 
      == (MI_CONNECTION *)NULL )
   {
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_open() call failed");
   return MI_ERROR;
   }

/* Get the function descriptor for equal() */
if ( (func_desc = mi_routine_get(conn, 0, func_sig)) 
      == (MI_FUNC_DESC *)NULL )
   {
   mi_db_error_raise(NULL, MI_EXCEPTION, 
      "mi_routine_get() call failed");
   return MI_ERROR;
   }

/* Execute the equal() user-defined function */
func_result = mi_routine_exec(conn, func_desc, &func_error,
   arg1, arg2);
if ( func_error == MI_ERROR )
   {
   mi_db_error_raise(NULL, MI_EXCEPTION,
      "mi_routine_exec() call failed");
   return MI_ERROR;
   }
...
When you cache the function descriptor in the user state of MI_FPARAM, you do not have to repeatedly call mi_routine_get() for each invocation of the equal() function. Instead, you can call mi_routine_get() only in the first invocation of equal(). The following code fragment opens a connection and gets the function descriptor in the first invocation of the user-defined function equal() only. It caches these descriptors into the user_state structure, whose address it stores in the user-state pointer of MI_FPARAM, for subsequent invocations of equal(): 
typedef struct user_state
   {
   MI_CONNECTION *conn;
   MI_FUNC_DESC *func_desc;
   };

mi_integer optimal(arg1, arg2, fparam)
   mi_integer arg1, arg2;
   MI_FPARAM *fparam;
{
   MI_CONNECTION *local_conn = NULL;
   MI_FUNC_DESC *local_fdesc = NULL;
   mi_string *func_sig="equal(int, int)";
   user_state *func_state;

   /* Obtain the connection descriptor from MI_FPARAM */
   func_state = (user_state *)mi_fp_funcstate(fparam);
   if ( func_state == NULL ) /* first time UDR is called */
      {
      /* Allocate a user_state structure */
      func_state = 
         (user_state *)mi_dalloc(sizeof(user_state),
         PER_COMMAND);

      /* Obtain the connection descriptor */
      if ( (local_conn = mi_open(NULL, NULL, NULL))
            == (MI_CONNECTION *)NULL )
         {
         mi_db_error_raise(NULL, MI_EXCEPTION, 
            "mi_open() call failed");
         return MI_ERROR;
         }

      /* Obtain the function descriptor for equal() */
      if ( (local_fdesc = 
            mi_routine_get(local_conn, 0, func_sig)) 
            == (MI_FUNC_DESC *)NULL )
         {
         mi_db_error_raise(NULL, MI_EXCEPTION,
            "mi_routine_get() call failed");
         return MI_ERROR;
         }

      /* Cache the connection descriptor and function
       * descriptor in MI_FPARAM
       */
      func_state->conn = local_conn;
      func_state->func_desc = local_fdesc;

      /* Save the user state in MI_FPARAM */
      mi_fp_setfuncstate(fparam, (void *)func_state);
      }

   /* Execute the equal() user-defined function */
   func_result = mi_routine_exec(func_state->conn,
      func_state->func_desc, &func_error, arg1, arg2);
   if ( func_error == MI_ERROR )
      {
      mi_db_error_raise(NULL, MI_EXCEPTION,
         "mi_routine_exec() call failed");
      return MI_ERROR;
      }
...