Saving a User State

The routine manager provides information about arguments and return values of a UDR in the MI_FPARAM structure that is associated with a UDR. In addition, you can store the address of private-state information, called user-state information, in a special field of the MI_FPARAM structure.

The database server passes the same MI_FPARAM structure to every invocation of the UDR within the same routine sequence. When your user-state information is part of the MI_FPARAM structure, your UDR can access this information across all the invocations within the same routine sequence. Your routine can use this private area of the MI_FPARAM structure to cache information that preserves its own state.

The MI_FPARAM structure can hold a user-state pointer that points to this private state information. The user-state pointer references a thread-private place holder that allows a UDR to associate a user-defined state information with a routine sequence. Table 63 shows that the DataBlade API provides the following accessor functions to access the user state of a UDR.

User-state information is useful for a UDR in the following cases:

• To save information between invocations of an iterator function

  For more information, see Writing an Iterator Function.

• To replace static or global variables in C function

  Use of the MI_FPARAM structure to hold state information enables a UDR to access global information without the use of static or global variables. It is never safe to use static and global variables that are updated because the updated value is not visible if the thread migrates to another virtual processor (VP) and concurrent activity is not interleaved.

The MI_FPARAM structure is associated with the routine sequence. Therefore, for a UDR in a query that executes in parallel, each thread has its own routine sequence and therefore its own MI_FPARAM structure. The first invocation of the routine by each thread would have to perform any initialization. Only UDRs that are declared as parallelizable can be executed in parallel queries. The database server always executes an SQL statement that contains a nonparallelizable UDR serially.

The MI_FPARAM structure has a memory duration of PER_COMMAND. The database server reinitializes the user-state information that mi_fp_funcstate( ) references to NULL at the end of the SQL command (for example, at the end of the subquery execution for each outer row from an outer query).

The code example in Figure 48 implements the rowcount( ) function. This function uses the MI_FPARAM structure to hold a count of the number of rows in a query.

/* The rowcount( ) function maintains the row count with a variable that
 * is stored as user-state information in the MI_FPARAM structure 
 */
mi_integer rowcount (fparam_ptr)
   MI_FPARAM *fparam_ptr;
{
   mi_integer *count = NULL;

   /* obtain the current user-state pointer from the MI_FPARAM structure */
   count = (mi_integer *)mi_fp_funcstate(fparam_ptr);

   /* if the user-state pointer is NULL, this is the first 
    * invocation of the function
    */
   if ( count == NULL )
      {
      /* allocate memory for the user-state information */
      count = (mi_integer *)mi_dalloc(sizeof(mi_integer), PER_COMMAND);

      /* save user-state pointer in the MI_FPARAM structure */
      mi_fp_setfuncstate(fparam_ptr, (void *)count);

      /* initialize the row counter */
      *count = 0;
      }

   /* increment the row counter */
   (*count)++;
   return (*count);
}

The rowcount( ) function uses the mi_fp_funcstate( ) function to obtain the user-state pointer from the MI_FPARAM structure. If this pointer is NULL, rowcount( ) allocates memory for the count variable and uses the mi_fp_setfuncstate( ) function to store this pointer as the user-state pointer in the MI_FPARAM structure. It uses the mi_dalloc( ) function to allocate this memory with a duration of PER_COMMAND so that the database server does not deallocate the memory after the first invocation of the function.

For the rowcount( ) function to be used in an SQL statement, it must be registered in the database. The following CREATE FUNCTION statement registers the rowcount( ) function for use in SQL statements:

CREATE FUNCTION rowcount( ) RETURNS INTEGER
   EXTERNAL NAME '/usr/lib/db_funcs/count.so(rowcount)'
   LANGUAGE C;

The CREATE FUNCTION statement must omit the MI_FPARAM argument; therefore the registered rowcount( ) function has no arguments. Suppose that the following query uses the rowcount( ) function:

SELECT rowcount( ) from employee;

The query calls the rowcount( ) function for each row in the employee table. Because the rowcount( ) function uses the MI_FPARAM structure to hold its state information (the count variable), each query has its own private count variable. Separate queries do not interfere with one another as they might with static and global variables.