Handling NULL arguments with MI_FPARAM

If you want your UDR to run when it receives NULL values as arguments, take the following steps:

1. 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.
2. Register the UDR that checks for and handles NULL values with the HANDLESNULLS routine modifier in the CREATE FUNCTION or CREATE PROCEDURE statement.

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 UDR can take the appropriate action, such as supplying a default value or exiting gracefully from the routine. The following code fragment implements the add_one() function that returns a NULL value if the argument is NULL.

mi_integer add_one(i, fParam)
   mi_integer i;
   MI_FPARAM *fParam;
{
   /* determine if the first argument is NULL */
   if ( mi_fp_argisnull(fParam, 0) == MI_TRUE )
      {
      mi_db_error_raise(NULL, MI_MESSAGE, 
         "Addition to a NULL value is undefined.\n");

      /* return an SQL NULL value */
      mi_fp_setreturnisnull(fParam, 0, MI_TRUE);

      /* the argument to this "return" statement is ignored by the
       * database server because the previous call to the 
       * mi_fp_setreturnisnull() function has set the return value
       * to NULL
       */
      return 0;
      }
   else
      return(i+1);
}

The following CREATE FUNCTION statement registers a function named 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;