Look up a UDR

To look up a UDR, use one of the following Fastpath look-up functions.
DataBlade® API Function How it looks up a UDR
mi_routine_get() Looks up a UDR using a routine signature that is passed as a character string
mi_routine_get_by_typeid() Looks up a UDR using a routine signature that is passed as separate arguments
mi_func_desc_by_typeid() Looks up a UDR by its routine identifier and returns its function descriptor

The mi_func_desc_by_typeid() function is available only within a C UDR. It is not valid within a client LIBMI application.

To obtain a function descriptor for a UDR, a Fastpath look-up function performs the following steps:
  1. Asks the database server to look up the UDR in the sysprocedures system catalog..

    If the name of the UDR in the routine signature that you specify is not unique in the database, the mi_routine_get() and mi_routine_get_by_typeid() functions use the routine signature to perform routine resolution. For more information, see Routine resolution

    Server only: A routine identifier uniquely identifies a UDR, so the mi_func_desc_by_typeid() function does not need to perform routine resolution. The routine identifier corresponds to the entry for the UDR in sysprocedures.procid. A negative routine identifier indicates a built-in function that does not have an entry in sysprocedures. The database server looks up information for a built-in function in an internal cache.
  2. Allocates a function descriptor for the routine and save the routine sequence in this descriptor.

    You can obtain information about the UDR from this function descriptor. For more information, see Obtain information from a function descriptor. You can also allocate your own MI_FPARAM structure to use instead of this automatically allocated one. For more information, see A user-allocated MI_FPARAM structure.

  3. Allocates an MI_FPARAM structure for the function descriptor.

    You can get a pointer to this structure with the mi_fparam_get() function. For more information, see Obtain the MI_FPARAM structure.

  4. Returns a pointer to the function descriptor that identifies the specified UDR.

    Subsequent calls to mi_routine_exec() can use this function descriptor to identify the UDR to execute. For more information, see Execute the routine.

Suppose the following CREATE FUNCTION statements register three user-defined functions named numeric_func() in your database: 
CREATE FUNCTION numeric_func(INTEGER, INTEGER) RETURNS INTEGER;
CREATE FUNCTION numeric_func(FLOAT, FLOAT) RETURNS FLOAT;
CREATE FUNCTION numeric_func(MONEY, MONEY) RETURNS MONEY;
The numeric_func() user-defined function is an overloaded routine. The following code fragment uses the mi_routine_get() function to obtain the function descriptor for the version of numeric_func() that handles INTEGER arguments.
Figure 1: Obtaining a function descriptor for the numeric_func() function
MI_CONNECTION *conn;
MI_FUNC_DESC *fdesc = NULL;
MI_FPARAM *fparam;
...
fdesc = mi_routine_get(conn, 0, 
   "function numeric_func(integer, integer)");
if ( fdesc =! NULL )
   {
   fparam = mi_fparam_get(conn, fdesc);
   if ( mi_fp_nrets(fparam) > 1 )
      /* multiple return values: have SPL routine */
      ...
   else
      /* no matching user-defined routine*/
      ...
   }

The mi_routine_get() function returns a NULL-valued pointer to indicate either no matching routine exists or the routine has multiple return values. Obtaining a function descriptor for the numeric_func() function also shows how to determine which of these conditions a NULL return value indicates. It uses the mi_fparam_get() function to obtain the MI_FPARAM structure that is associated with the located numeric_func() function. (The mi_routine_get() function has allocated and initialized this MI_FPARAM structure as part of the look-up process.) The code fragment then uses the mi_fp_nrets() accessor function to obtain the number of UDR return values from this MI_FPARAM structure. Because C UDRs can only return one value, any UDR that returns more than one value must be an SPL routine.

Use mi_routine_get() when you can create the full signature of the UDR as a literal string. Otherwise, you can use the mi_routine_get_by_typeids() function to build the routine signature. For example, if you have a user-supplied query, you can use mi_column_typeid() to get the type identifier for the column that the query returns. The mi_routine_get_by_typedesc() function is also useful when you need to invoke overloaded UDRs with different parameter data types (and you have parameter type identifiers).

In Obtaining a function descriptor for the numeric_func() function, you can replace the call to mi_routine_get() with the following call to the mi_routine_get_by_typeid() function: 
MI_TYPEID *arg_types[2];
...
arg_type[0] = mi_typestring_to_id(conn, "integer");
arg_type[1] = arg_type[0];
fdesc = mi_routine_get_by_typeid(conn, MI_FUNC,
   "numeric_func", NULL, 2, arg_types);

In this call to mi_routine_get_by_typeid(), the arg_types array contains pointers to the type identifiers for the two INTEGER parameters.

Server only:
If you already have a routine identifier for the UDR that you want to execute with Fastpath, use the mi_func_desc_by_typeid() function to obtain the function descriptor of the UDR. In Obtaining a function descriptor for the numeric_func() function, you can replace the call to mi_routine_get() with the following call to mi_func_desc_by_typeid():
mi_funcid rout_id;
...
fdesc = mi_func_desc_by_typeid(conn, rout_id);

In this call, the mi_funcid data type holds the routine identifier of the UDR to look up.

Client only:

When you call mi_routine_get() or mi_routine_get_by_typeid() from a client LIBMI application, the function allocates a local copy (on the client computer) of the function descriptor and MI_FPARAM structure. You can use the function descriptor and MI_FPARAM accessor functions within a client LIBMI application to access these local copies.

The mi_func_desc_by_typeid() function is not valid within a client LIBMI application.