The mi_routine_exec( ) function can execute any UDR that is registered in the open database. Therefore, any UDR that you can use in an SQL statement, you can directly execute with mi_routine_exec( ). Once you obtain a function descriptor for a registered UDR or cast function, the mi_routine_exec( ) function sends it to the routine manager for execution. You can use the function descriptor in repeated calls to mi_routine_exec( ). This executed routine runs in the virtual processor (VP) that was defined for it. This VP is not necessarily the VP in which the calling UDR runs.
The mi_routine_exec( ) function takes the following steps:
When you call mi_routine_exec( ), you provide argument values for the UDR that Fastpath is to execute. Keep the following points in mind when you create the argument list for mi_routine_exec( ):
Therefore, you must pass in the arguments with the appropriate passing mechanism for the data type. Most data types are to be passed by reference to the UDR. For a list of data types that can be passed by value, see Table 13. For examples of how to pass arguments to a UDR through mi_routine_exec( ), see Sample mi_routine_exec( ) Calls.
If you call a UDR that does not handle NULLs with a NULL argument value, the routine might return incorrect values.
For more information, see Using a User-Allocated MI_FPARAM Structure.
The mi_routine_exec( ) function dispatches the UDR through the routine manager. Therefore, each UDR gets a call to mi_call( ) because the routine manager checks for sufficient space before it executes the UDR.
When the mi_routine_exec( ) function executes a user-defined function, it returns as an MI_DATUM value the return value of the user-defined function that it has executed. This MI_DATUM value contains a value appropriate for the passing mechanism for the data type. Most data types are to be passed by reference from the UDR. For a list of data types that can be passed by value, see Table 13.
You can then use C casting to convert this MI_DATUM value to the appropriate data type. You can obtain information about the return type (such as its data type) from the MI_FPARAM structure.
If the user-defined function returned an SQL NULL value, mi_routine_exec( ) returns a NULL-valued pointer and sets the status argument to MI_OK.
For examples of how to receive a UDR return value from mi_routine_exec( ), see Sample mi_routine_exec( ) Calls. For more information on MI_DATUM values, see The MI_DATUM Data Type.
The code fragment in Figure 54 uses the mi_routine_exec( ) function to execute the numeric_func( ) routine that has INTEGER parameters.
MI_CONNECTION *conn;
MI_FUNC_DESC *fdesc;
MI_DATUM ret_val;
mi_integer int_ret;
mi_integer error;
...
/* fdesc obtains from code in Figure 51 */
ret_val = mi_routine_exec(conn, fdesc, &error, 1, 2);
if ( ret_val == NULL )
{
if ( error == MI_OK )
/* numeric_func( ) returned an SQL NULL value */
...
else /* error in mi_routine_exec( ) */
{
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_routine_exec( ) failed");
return MI_ERROR;
}
}
else /* cast MI_DATUM in ret_val to INTEGER */
{
int_ret = (mi_integer) MI_DATUM;
...In Figure 54, the mi_routine_exec( ) function uses the fdesc function descriptor that the mi_routine_get( ) function obtained for the numeric_func( ) function that accepts INTEGER arguments and returns an INTEGER value (Figure 51). The last two arguments to mi_routine_exec( ) are the integer argument values for numeric_func( ). The ret_val variable is an MI_DATUM value for the mi_integer data type that the numeric_func( ) function returns.
Figure 54 also tests the return status of mi_routine_exec( ) to check for the return value from numeric_func( ). If the return value (ret_val) is a NULL-valued pointer, the code then determines which of the following results this NULL value indicates:
Finally, the code fragment in Figure 54 casts the MI_DATUM value that mi_routine_exec( ) has returned to an mi_integer value. The MI_DATUM structure contains the actual return value because the routine manager can pass integer values by value (they can fit into an MI_DATUM structure).
Suppose the call to mi_routine_get( ) had been calling the version of numeric_func( ) that accepted a FLOAT argument and returned a FLOAT value, as follows:
fdesc = mi_routine_get(conn, 0, "function numeric_func(float)");
The call to mi_routine_exec( ) to execute this version of numeric_func( ) would require that the argument and the return value be passed by reference, because the FLOAT data type cannot be stored directly in an MI_DATUM structure.
Figure 55 shows a sample call to mi_routine_exec( ) to execute the numeric_func(FLOAT) user-defined function. The argument list to mi_routine_exec( ) passes the FLOAT value by reference and the returned FLOAT value is returned by reference.
MI_CONNECTION *conn;
MI_FUNC_DESC *fdesc;
MI_DATUM ret_val;
mi_double_precision double_arg1, double_arg2, double_ret;
mi_integer error;
...
fdesc = mi_routine_get(conn, 0, "function numeric_func(float)");
ret_val = mi_routine_exec(conn, fdesc, &error, &double_arg1,
&double_arg2);
if ( ret_val == NULL )
{
if ( error == MI_OK )
/* numeric_func( ) returned an SQL NULL value */
...
else /* error in mi_routine_exec( ) */
{
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_routine_exec( ) failed");
return MI_ERROR;
}
}
else /* cast MI_DATUM in ret_val to FLOAT */
{
double_ret = (mi_double_precision *)MI_DATUM;
...The following call to mi_routine_exec( ) executes the DECIMAL-to-mytype cast function for which Figure 52 obtained the function descriptor, fdesc2:
ret_val = mi_routine_exec(conn, fdesc2, &error, dec_val); mytype_val = (mytype *)ret_val;
The dec_val argument is an mi_decimal variable that contains the DECIMAL source data type to cast to the mytype target data type with the dec_to_mt( ) cast function. The ret_val variable is an MI_DATUM value of the mytype data type that contains the casted DECIMAL value.
The execution of a built-in cast (system or MI_SYSTEM_CAST) function is different from the execution of a user-defined cast function. A user-defined cast function takes only one argument, the data value to be converted, but a built-in cast function takes three arguments:
The FPARAM information for the value argument comes from the source type for the cast. The length and precision fields are both integers so their precision is always 0. Below is an example of a system cast.
if (cast_status == MI_SYSTEM_CAST)
{
mi_integer precision
=
mi_type_precision(tdTarget);
mi_integer length =
mi_type_maxlength(tdTarget);
mi_integer scale = mi_type_scale(tdTarget);
fd_fparam =
mi_fparam_get(conn,fd);
....
.....
mi_setretlen(fd_fparam,0,length);
mi_setretprec(fd_fparam,0,precision);
mi_setretscale(fd_fparam,0,scale);
retValue = mi_routine_exec(conn, fd, &error,
value,
NULL,NULL);
Looking up a UDR and creating its function descriptor can be an expensive operation. If you have multiple UDR invocations calling the same UDR through Fastpath, you can cache the function descriptor to make it available for reuse within either of the following scopes:
If the same UDR executes many times within a single SQL command, you can cache this function descriptor as part of the MI_FPARAM structure of the UDR.
If the same UDR executes many times within a session, you can cache the function descriptor in PER_SESSION named memory.
When you reuse the function descriptor, you save the overhead of looking up the UDR and allocating a function descriptor each time you reexecute the same UDR.
When you pass a public connection descriptor to one of the Fastpath look-up functions (see Table 65), 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.
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;
}
...
When you pass a session-duration connection descriptor to one of the Fastpath look-up functions (see Table 65), the look-up function allocates a function descriptor with a PER_SESSION memory duration, called a session-duration function descriptor. The session-duration function descriptor remains allocated until the session ends. In this case, all UDRs within the session can access the cached function descriptor.
The following table summarizes the memory operations for a session-duration function descriptor in a C UDR.
To cache the function descriptor, save its address in PER_SESSION named memory. This location guarantees that all UDRs within the session can access the function descriptor. A UDR can create a session-duration function descriptor and cache it as follows:
The UDR must store the pointers to these function descriptors in a named-memory block so that they can be accessed across different UDRs. Use the mi_named_alloc( ) or mi_named_zalloc( ) function to allocate the PER_SESSION memory. Other UDRs might also be using the same session-duration connection. Therefore, you might need to handle concurrency issues on the named memory.
When one of these look-up functions receives a session-duration connection descriptor (instead of a public connection descriptor), it allocates a session-duration function descriptor.
The following code fragment uses the mi_routine_get( ) function to obtain a session-duration function descriptor for the func1( ) UDR:
MI_CONNECTION *sess_conn;
MI_FUNC_DESC **fdesc;
mi_integer status;
/* Obtain a session-duration connection descriptor */
sess_conn = mi_get_session_connection( );
/* Allocate a PER_SESSION named-memory block named
* 'funcptr_blk'. Assign address of this block to fdesc
* pointer.
*/
if ( (status = mi_named_alloc((sizeof)(MI_FUNC_DESC *),
"funcptr_blk", PER_SESSION, (void **)&fdesc))
!= MI_OK )
{
/* Unable to allocate named-memory block. Handle error */
}
/* Obtain the session-duration function descriptor for
* func1( ). Store function descriptor in named-memory block.
*/
if ( (*fdesc = mi_routine_get(sess_conn, 0,
"function func1(int, char)") == (MI_FUNC_DESC *)NULL)
{
/* Unable to obtain function descriptor for func1( ) UDR.
* Handle error.
*/
}
The preceding code fragment uses the mi_get_session_connection( ) function to obtain the session-duration connection descriptor, sess_conn. It then passes sess_conn to the mi_routine_get( ) function to obtain a session-duration function descriptor for func1( ). The address of this session-duration function descriptor is stored in a PER_SESSION named-memory block named funcptr_blk. All UDRs that need to access the func1( ) function descriptor can obtain it from funcptr_blk. For more information, see Reusing the Session-Duration Function Descriptor.
To reuse the session-duration function descriptor in another UDR, you:
The following code fragment shows how other UDRs can access the fdesc function descriptor until the session ends:
MI_CONNECTION *sess_conn;
MI_FUNC_DESC **fdesc;
mi_integer status, error;
mi_integer i = 55;
mi_char ch = 'c';
MI_DATUM *value;
/* Obtain a public and session-duration connection
* descriptor
*/
conn = mi_open(NULL, NULL, NULL);
sess_conn = mi_get_session_connection( );
/* Obtain the address of the 'funcptr_blk' named-memory block,
* which contains the session-duration function descriptor
*/
if ( (status = mi_named_get("funcptr_blk", PER_SESSION,
(void **)&fdesc)) != MI_OK )
{
/* Handle error */
...
}
/* Execute the UDR associated with the 'fdesc'
* session-duration function descriptor
*/
value = mi_routine_exec(conn, *fdesc, &error, i, c);
Once you obtain the session-duration function descriptor, the mi_routine_exec( ) function can execute the associated UDR. You can specify either a public connection descriptor or a session-duration connection descriptor to mi_routine_exec( ). If the UDR that the function descriptor references is dropped while the session-duration function descriptor is still in use, the database server generates an error when you try to execute the routine.
The session-duration function descriptor has a PER_SESSION memory duration. The function descriptor remains active until either of the following events occurs:
To explicitly free a session-duration function descriptor, you must free both the function descriptor with mi_routine_end( ) and the associated PER_SESSION named memory with mi_named_free( ), as the following code fragment shows:
/* Free resources for the session-duration function
* descriptor
*/
if ( mi_routine_end(sess_conn, *fdesc) != MI_OK )
{
/* Handle error */
...
}
mi_named_free("funcptr_blk", PER_SESSION);The database server automatically frees memory for the session-duration function descriptor and its PER_SESSION named memory at the end of the session.