Informix DataBlade API Programmer's Manual Executing User-Defined Routines

When the routine manager calls a UDR, it passes the routine-state information as an argument to the routine. When the database server calls a C UDR, it automatically passes the routine-state information as an extra argument, called the function-parameter structure, to the UDR. This function-parameter structure, MI_FPARAM, holds the routine-state information for the C UDR with which it is associated.

This MI_FPARAM structure that the routine manager passes lasts for the duration of an SQL command. The following table summarizes the memory operations for an MI_FPARAM structure.

Most UDRs do not need to access this routine-state information. For such routines, you do not have to include an MI_FPARAM structure as a parameter in the C declaration. Your UDR only needs to declare an MI_FPARAM parameter if it needs to perform one of the following tasks.

Tip: When you declare an MI_FPARAM parameter, this declaration must be the last parameter in the C declaration of your UDR. For more information about how to declare an MI_FPARAM structure for a UDR, see For the MI_FPARAM Argument.

The UDR can then use the DataBlade API accessor functions that Figure 9-1 on page 9-5, Figure 9-4 on page 9-10, and Figure 9-8 on page 9-19 list to access values in the MI_FPARAM structure.

Important: The MI_FPARAM structure is an opaque C structure to DataBlade API modules. Do not access its internal fields directly. Informix does not guarantee that the internal structure of MI_FPARAM will not change in future releases. Therefore, to create portable code, always use the accessor functions for this structure to obtain and store values.

A UDR can also allocate an MI_FPARAM structure for a user-defined routine that it invokes with the Fastpath interface. For more information, see Using a User-Allocated MI_FPARAM Structure.

The user state of a C UDR provides the following information about routine arguments:

• Data-type information about any arguments
• Boolean value to indicate whether an argument is NULL

Figure 9-1 lists the DataBlade API accessor functions that obtain and set information about routine arguments in the MI_FPARAM structure.

Determining the Data Type of UDR Arguments

With the MI_FPARAM structure, you can write UDRs that operate over a type hierarchy, rather than on a single type. At runtime, the routine can examine the MI_FPARAM structure to determine what data types were passed to the current invocation of the routine.

The MI_FPARAM structure stores the information about each UDR argument in several parallel arrays.

Use the appropriate MI_FPARAM accessor function in Figure 9-1 on page 9-5 to access the desired argument array.

All of the argument arrays in the MI_FPARAM structure have zero-based indexes. To access information for the nth argument, provide an index value of n-1 to the appropriate accessor function in Figure 9-1 on page 9-5. Figure 9-2 shows how the information at index position 1 of these arrays holds the argument information for the second argument of the UDR.

Figure 9-2 Argument Arrays in the MI_FPARAM Structure

The following calls to the mi_fp_argtype() and mi_fp_arglen() functions obtain the type identifier (arg_type) and length (arg_len) for the second argument from an MI_FPARAM structure that fparam_ptr identifies:

mi_integer my_func(arg0, arg1, arg2, fparam_ptr)

	MI_DATUM arg0;

	MI_DATUM arg1;

	MI_DATUM arg2;

	MI_FPARAM *fparam_ptr;

{

	MI_TYPEID *arg_type;

	mi_integer arg_len;

	...

	arg_type = mi_fp_argtype(fparam_ptr, 1);

	arg_len = mi_fp_arglen(fparam_ptr, 1);

To obtain the number of arguments passed to the UDR (which is also the number of elements in the argument arrays), use the mi_fp_nargs() function. For the argument arrays of the MI_FPARAM structure in the preceding code fragment, mi_fp_nargs() would return a value of 3. The mi_fp_setnargs() function stores the number of routine arguments in the MI_FPARAM structure.

Tip: For more information on type identifiers and lengths, see Type Identifiers. For more information on the scale and precision of fixed-point and floating-point data types, see Chapter 3, Using Numeric Data Types.

Handling NULL Arguments With MI_FPARAM

By default, C user-defined routines do not handle SQL NULL values. A user-defined routine is not executed if any of its arguments is NULL; the routine automatically returns a NULL value. If you want your user-defined routine to be invoked when it receives NULL values as arguments, take the following steps:

• Use the following DataBlade API functions to programmatically handle SQL NULL values within the C UDR:
• Use the mi_fp_argisnull() function to determine whether an argument is NULL.
• Use the mi_fp_setargisnull() function to set an argument to NULL.
• Register the user-defined routine that checks for and handles NULL values with the HANDLESNULLS routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement.

For more information on how to register a user-defined routine, see Registering a C UDR.

The mi_fp_argisnull() function obtains an mi_boolean value from an element in the null-argument array of the MI_FPARAM structure. If mi_fp_argisnull() returns MI_TRUE, your user-defined routine can take the appropriate action, such as supplying a default value or exiting gracefully from the routine. The code in Figure 9-3 implements the add_one() function that returns a NULL value if the argument is NULL.

The following CREATE FUNCTION statement registers a function called add_one() in the database:

CREATE FUNCTION add_one(i INTEGER) RETURNS INTEGER

	WITH (HANDLESNULLS)

	EXTERNAL NAME '/usr/lib/db_funcs/add.so(add_one)'

	LANGUAGE C;

Notice that this CREATE FUNCTION omits the MI_FPARAM parameter of the add_one() user-defined function from the definition of the SQL add_one() user-defined routine.

The MI_FPARAM structure of a C user-defined function provides the following information about function return values:

• Data-type information about any return values
• Boolean value to indicate whether a return value is NULL

Important: Because a user-defined function is written in the C language, it can only return a single value. However, this single value can be a structure (such as a row descriptor) that contains multiple values. For information on how to return multiple values, see Returning Multiple Values.

Figure 9-4 lists the DataBlade API accessor functions that obtain and set information about function return values in an MI_FPARAM structure. (Only user-defined functions return values; user-defined procedures do not.)

Determining the Data Type of UDR Return Values

The database server sets the return-value data type of the user-defined function. Most user-defined functions might need to check the return-value data type but they do not need to set it.

The routine manager uses the return-value information to determine how to bind the return value to a return variable or an SQL value. You only need to access return-value information if your UDR needs to perform one of the following tasks:

• To override the expected return type (for type hierarchies). You can set this return-value data type in the MI_FPARAM structure
• To set the actual length, precision, or scale of the return value
• To return an SQL NULL value (see Returning a NULL Value)
• To check the return value of a UDR that you are going to execute with the Fastpath interface and for which you have created a user-allocated MI_FPARAM structure (seeUsing a User-Allocated MI_FPARAM Structure)

If your UDR does not need to perform these tasks, it does not need to modify return-value information in the MI_FPARAM structure.

The MI_FPARAM structure uses several parallel arrays to store the following information about each return value.

Use the appropriate MI_FPARAM accessor function in Figure 9-4 on page 9-10 to access the desired return-value array.

All of the return-value arrays in the MI_FPARAM structure have zero-based indexes. To access information for the nth return value, provide an index value of n-1 to the appropriate accessor function in Figure 9-4 on page 9-10. Figure 9-5 shows how the information at index position 0 of these arrays holds the return-value information for the first (and only) return value of a user-defined function.

Figure 9-5 Return-Value Arrays in the MI_FPARAM Structure

The following calls to the mi_fp_rettype() and mi_fp_retlen() functions obtain the type identifier (ret_type) and length (ret_len) for the first (and only) return value from an MI_FPARAM structure that fparam_ptr identifies:

MI_FPARAM *fparam_ptr;
MI_TYPEID *ret_type;
mi_integer ret_len;


ret_type = mi_fp_rettype(fparam_ptr, 0);
ret_len = mi_fp_retlen(fparam_ptr, 0);

To obtain the number of return values of the user-defined function, use the mi_fp_nrets() function. However, the number of return values is always 1 for a C user-defined function. The mi_fp_setnrets() function stores the number of return values in the MI_FPARAM structure.

To return most values from a user-defined function, you use a C return statement. (For more information, see Returning a Value.) However, to return the SQL NULL value, you must access the MI_FPARAM structure of the UDR.

The DataBlade API provides the following functions to support the return of an SQL NULL value from a C user-defined function:

• To indicate that your user-defined function returns a NULL value, use the mi_fp_setreturnisnull() function to store the value of MI_TRUE at the appropriate index position in the null-return array of the MI_FPARAM structure.
• The mi_fp_returnisnull() function accesses the MI_FPARAM structure to determine whether a return value is NULL.

The mi_fp_setreturnisnull() function sets an mi_boolean value to indicate whether the return value is NULL. The code in Figure 9-3 on page 9-9 implements the add_one() function that uses the mi_fp_setreturnisnull() function to return a NULL value when add_one() receives a NULL argument.

Warning: Do not return a NULL-valued pointer from a user-defined routine. If you need to have the UDR return an SQL NULL value, always use mi_fp_setreturnisnull(). Otherwise, serious memory corruption might occur.

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 private-state information, called user-state information, in a private storage area of MI_FPARAM.

The database server passes the same MI_FPARAM structure to every invocation of the UDR within the same routine sequence. Therefore, 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 MI_FPARAM to cache information that preserves its own state.

Tip: For more information about user state and the routine sequence, see Creating the Routine Sequence.

The MI_FPARAM structure can hold a user-state pointer, which 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. Figure 9-6 shows that the DataBlade API provides the following accessor functions to access the user state of a user-defined routine.

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 allows 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).
• concurrent activity is not interleaved.

Warning: Avoid the use of static and global variables in a user-defined routine. UDRs that use variables with these scopes are ill-behaved routines. You must execute an ill-behaved UDR in a separate virtual-processor class, called a user-defined VP. For more information, see Handling an Ill-Behaved Routine.

To save the user-state information for multiple invocations, follow these steps in the first invocation of the UDR:

1. Use the mi_fp_funcstate() function to retrieve the user-state pointer from the MI_FPARAM structure.

Once the UDR has the user-state pointer, it can obtain state information from the private storage area on subsequent invocations.

2. Check for a NULL-valued user-state pointer.

On the first invocation of your UDR, the user-state pointer is a NULL-valued pointer. If the user-state pointer is a NULL-valued pointer, allocate a private user-defined buffer or structure for the user-state information.

When you allocate memory for the user-state information, you must protect this memory so that it is not reclaimed while it is still in use. Define a memory duration of PER_COMMAND for this memory with a DataBlade API memory-allocation function such as mi_dalloc() or mi_switch_mem_duration(). For more information, see Determining Memory Duration.

3. Put the private data in the user-defined buffer or structure to initialize the user state.
4. If the UDR has just allocated the private user-state buffer, use the mi_fp_setfuncstate() function to store the address of this user-defined buffer or structure as a user-state pointer in the MI_FPARAM structure.

You save the user-state pointer in the MI_FPARAM structure so that later UDR invocations of the routine sequence can access the routine-state information.

On subsequent invocations of the UDR, you can obtain user-state information with these steps:

1. Use the mi_fp_funcstate() function to retrieve the user-state pointer from the MI_FPARAM structure.
2. If the user-state pointer is not a NULL-valued pointer, cast the pointer to the data type of your user-state information.

Once the user-state pointer points to the correct data type, you can access the user-state information.

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 user-defined routines that are declared as parallelizable can be executed in parallel queries. The database server always executes an SQL statement that contains a nonparallelizable user-defined routine serially.

Tip: By default, the CREATE FUNCTION statement registers a UDR as non-parallelizable. To declare a user-defined function as parallelizable, specify the PARALLELIZABLE routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement. For more information, see Creating Parallelizable UDRs.

The MI_FPARAM structure has a memory duration of PER_COMMAND. Therefore, 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 9-7 implements the rowcount() function. This function uses the MI_FPARAM structure to hold a count of the number of rows in a query.

Figure 9-7 Using the MI_FPARAM Structure to Hold Private-State Information

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 it after the first invocation of the function.

Tip: The rowcount() function in Figure 9-7 shows how to use the MI_FPARAM structure to hold private user-state information. This method removes the need for global or static variables, which can make a C UDR ill-behaved. Figure 12-7 on page 12-32 shows the bad_rowcount() function, which incorrectly implements a row counter with a static variable.

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.

Tip: You could also implement the rowcount() function as a user-defined aggregate function. User-defined aggregates do not use the MI_FPARAM structure to hold state information. For more information, see Writing an Aggregate Function.

Obtaining Other Routine Information

The MI_FPARAM structure of a C UDR provides additional information about a UDR. Figure 9-8 lists the DataBlade API accessor functions that obtain and set other routine information of a UDR.

For more information about use of the iterator-completion flag and iterator status, see Writing an Iterator Function. For information about use of the routine identifier, see Routine Resolution.