Obtaining Argument Values

To obtain the argument value with a C UDR, access the parameter that you have specified in the C declaration of the function. The parameter declaration indicates the appropriate passing mechanism for the UDR parameters. You can access the argument values through these declarations, as you would any other C-function parameter, as follows:

• For pass-by-reference parameters, access the argument value through its pointer. Do not modify this pointer within the body of the UDR.

  Most data types are passed by reference. The sample UDR bigger_double( ), in Figure 67, shows how to access pass-by-reference arguments within a UDR.

• For pass-by-value parameters, you can access the argument value directly through its parameter variable.

  For a list of data types that can be returned by value, see Table 13. The sample UDR bigger_int( ), in Figure 68, shows how to access pass-by-value arguments within a UDR.

Handling Character Arguments

When the routine manager receives text data for a C UDR, it puts this text data into an mi_lvarchar varying-length structure. It then passes a pointer to this mi_lvarchar structure as the MI_DATUM structure for the UDR argument. Therefore, a C UDR must have its text parameter declared as a pointer to an mi_lvarchar structure when the parameter accepts data from the following SQL character data types:

• CHAR
• LVARCHAR
• NCHAR
• NVARCHAR
• VARCHAR

For example, suppose you want to define a user-defined function named initial_cap( ) that accepts a VARCHAR string, ensures that the string begins with an uppercase letter, and ensures that the rest of the string consists of lowercase letters. This UDR would be useful in the following query to retrieve a customer's last name:

SELECT customer_num
FROM customer
WHERE initial_cap(lname) = "Sadler";

In the preceding query, use of the initial_cap( ) function means that you do not have to ensure that the customer last names (in the lname column) were entered with an initial uppercase letter. The preceding query would locate the customer number for either Sadler or sadler.

The following CREATE FUNCTION statement registers the initial_cap( ) function in the database:

CREATE FUNCTION initial_cap(str VARCHAR(50))
RETURNS VARCHAR(50)
EXTERNAL NAME '/usr/udrs/text/checkcaps.so'
LANGUAGE C;

The following declaration of initial_cap( ) specifies an mi_lvarchar pointer as the parameter data type even though the function is registered to accept a VARCHAR column value:

/* Valid C UDR declaration for string parameter */
mi_lvarchar *initial_cap(str)
   mi_lvarchar *str;

The following declaration of initial_cap( ) is invalid because it specifies an mi_string pointer as the parameter data type:

/* INVALID declaration for string parameter */
mi_string *initial_cap(string)
   mi_string *string;

The initial_cap( ) function in the preceding declaration would not execute correctly because it interprets its argument as an mi_string value when the routine manager actually sends this argument as an mi_lvarchar value.

Figure 69 shows the implementation of the initial_cap( ) function.

#include <mi.h>
#include <ctype.h>

mi_lvarchar *initial_cap(str)
   mi_lvarchar *str;
{
   char *var_ptr, one_char;
   mi_lvarchar *lvarch_out;
   mi_integer i, var_len;

   /* Create copy of input data */
   lvarch_out = mi_var_copy(str);

   /* Obtain data pointer for varying-length data */
   var_ptr = mi_get_vardata(lvarch_out);

   /* Obtain data length */
   var_len = mi_get_varlen(lvarch_out);

   /* Check string for proper letter case */
   for ( i=0; i < var_len; i++ )
      {  
      one_char = var_ptr[i];

      if ( i == 0 ) 
         /* Change lowercase first letter to uppercase */
         {
         if ( islower(one_char) ) /* is lowercase */
            var_ptr[i] = toupper(one_char);
         }
      else 
         /* Change uppercase other letters to lowercase */
         if ( isupper(one_char) ) /* is uppercase */
            var_ptr[i] = tolower(one_char);
      }  

   return (lvarch_out);
}

Handling NULL Arguments

By default, a C UDR does not handle SQL NULL values. When you call a UDR with an SQL NULL as the argument, the routine manager does not invoke the UDR. It returns a value of SQL NULL for the UDR. To have the UDR invoked when it is called with SQL NULL arguments, register the UDR with the HANDLESNULLS routine modifier and code the UDR to take special steps when it receives a NULL argument.

To determine whether an argument is SQL NULL, declare the MI_FPARAM structure as the last argument in the UDR and use the mi_fp_argisnull( ) function to check for NULL argument values. Do not just compare the argument with a NULL-valued pointer. For more information, see Handling NULL Arguments with MI_FPARAM.

Handling Opaque-Type Arguments

When the routine manager receives opaque-type data for a C UDR, the way the routine manager passes this data to the UDR depends on the kind of opaque data type, as follows:

• For fixed-length opaque types, the routine manager usually passes a pointer to the internal format of the opaque type.
• For varying-length opaque types, the routine manager passes a pointer to an mi_bitvarying varying-length structure.

A C UDR must declare its opaque-type parameter appropriately.

Handling Fixed-Length Opaque-Type Arguments

For UDR arguments that are fixed-length opaque types, the routine manager passes a pointer to the internal format of the opaque type to the C UDR. If the fixed-length opaque type is defined as passed by value, however, the routine manager passes the actual internal format. For more information, see Determining the Passing Mechanism for an Opaque Type.

For example, suppose you want to define a user-defined function named circle_area( ) that accepts a fixed-length opaque type named circle (which is defined in Figure 113) and computes its area. The following CREATE FUNCTION statement registers the circle_area( ) function in the database:

CREATE FUNCTION circle_area(arg1 circle)
RETURNS FLOAT
EXTERNAL NAME '/usr/udrs/circle/circle.so'
LANGUAGE C;

Because circle is a fixed-length data type that cannot fit into an MI_DATUM structure, the following declaration of circle_area( ) specifies a pointer to the internal format of circle:

/* Valid C UDR declaration for fixed-length opaque-type
 * parameter
 */
mi_double_precision *circle_area(circle_ptr)
   circle_t *circle_ptr;

Figure 70 shows the implementation of the circle_area( ) function.

#include <mi.h>
#include <ctype.h>
#include <circle.h>

mi_double_precision *circle_area(circle)
   circle_t *circle;
{
   mi_double_precision *area;

   /* Allocate memory for mi_double_precision return 
    * value
    */
   area = mi_alloc(sizeof(mi_double_precision));

   /* Calculate circle area using radius from circle_t
    * structure and constant PI_CONSTANT (defined in
    * circle.h).
    */
   *area = (circle->radius * circle->radius) * PI_CONSTANT;

   return ( area )
}

Handling Varying-Length Opaque-Type Arguments

For UDR arguments that are varying-length opaque types, the routine manager puts the data into an mi_bitvarying varying-length structure. It then passes a pointer to this mi_bitvarying structure as the MI_DATUM structure for the UDR argument. Your UDR must extract the actual opaque-type data from the data portion of the mi_bitvarying varying-length structure. For more information on how to access varying-length structures, see Using a Varying-Length Structure.

Suppose that you want to create a user-defined function named image_id( ) that accepts a varying-length opaque type named image (which is defined in Figure 114) and returns its integer image identifier (img_id). The following CREATE FUNCTION statement registers the image_id( ) function in the database:

CREATE FUNCTION image_id(arg1 image)
RETURNS INTEGER
EXTERNAL NAME '/usr/udrs/image/image.so'
LANGUAGE C;

Because image is a varying-length data type, the following declaration of image_id( ) specifies an mi_bitvarying pointer as the parameter data type even though the function is registered to accept a value of type image:

/* Valid C UDR declaration for varying-length opaque-type
 * parameter
 */
mi_integer image_id(image)
   mi_bitvarying *image;

The following declaration of image_id( ) is invalid because it specifies an image pointer as the parameter data type:

/* INVALID declaration for varying-length opaque-type
 * parameter
 */
mi_integer image_id(image)
   image_t *image;

The image_id( ) function in the preceding declaration would not execute correctly because it interprets its argument as the internal structure for image (image_t) when the routine manager actually sends this argument as an mi_bitvarying value.

Figure 71 shows the implementation of the image_id( ) function.

#include <mi.h>
#include <ctype.h>
#include <image.h>

mi_integer image_id(image)
   mi_bitvarying *image;
{
   image_t *image_ptr;

   /* Obtain pointer to image_t structure, contained
    * within the data portion of the mi_bitvarying 
    * structure.
    */
   image_ptr = (image_t *)mi_get_vardata((mi_lvarchar *)image);

   return (image_ptr->img_id);
}

Modifying Argument Values

Do not modify a UDR argument unless it is an OUT parameter. The routine manager does not make routine-specific copies of the arguments that it passes to UDRs because it is more efficient not to do so. Keep in mind that values passed into a UDR are often used on other places in the SQL statement. If you modify a pass-by-reference value within the UDR, you also modify it for all other parts of the SQL statement (including other UDRs) that operate on the value after the UDR executes. When you modify a pass-by-reference argument within the UDR, you might create an order-dependent result of the SQL statement. That is, it now might make a difference when your UDR is run within the SQL statement.