UDF and NULL parameters

InterBase does not (officially) offer any method for detecting NULL values passed as parameters to UDF, nor a way to return NULL as a result of calling UDF function. Here are some tips on how to circumvent this problem.

Do not use NULLs

It is quite a frequent mistake to use NULLs for purposes it was not intended to. For example you have column MIDDLE_NAME in your table; if the person does not have middle name, you should fill in empty string (''); but sometimes people use NULL instead - it is wrong, because NULL means unknown value, not missing/not assigned/empty one. Using NULL as middle name means - "this person probably has a middle name, but we do not know what it is" - and not - "this person does not have any middle name".

Example

CREATE TABLE tab (
  FIRST_NAME  VARCHAR(32) NOT NULL,
  MIDDLE_NAME VARCHAR(32) NOT NULL,
  LAST_NAME   VARCHAR(32) NOT NULL,
  FULL_NAME   COMPUTED BY
   (CapitalizeUDF(FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME)) );
Without NOT NULL constraints, whole concatenation would be NULL if any of the columns, usually MIDDLE_NAME, is NULL. (And FULL_NAME computed column will be empty string in this case.)

Do not call UDF with NULL parameters

Many (most) UDFs implement functions that are write-null-through type, i.e. NULL input parameter will result in NULL output value, (e.g. function that converts string to lowercase should return NULL if input parameter is NULL). If this is the case, it is not necessary to call the function at all, because you know the return value (NULL in this case) in advance.

Example 1

UPDATE tab
   SET product_type = LowerUDF (product_type)
 WHERE product_type IS NOT NULL;

Example 2

CREATE TRIGGER tr FOR tab BEFORE UPDATE AS
BEGIN
  IF (NEW.product_type IS NOT NULL) THEN
      NEW.product_type = LowerUDF (NEW.product_type);
END

Example 3

SELECT ID, Sin(angle) AS SINE             FROM tab WHERE angle IS NOT NULL
 UNION ALL
SELECT ID, CAST(NULL AS DOUBLE PRECISION) FROM tab WHERE angle IS NULL;

Test for NULLs on client side (in application)

Frequent question on newsgroups is How do I implement UDF that converts NULL to empty string - I need it to be able to concatenate First+Middle+Last names. Such operation is in fact formatting of the string, and hence it is better to handle it on the client, where you should be able to test for NULLs.

Example

Instead of selecting concatenated full name 
  SELECT FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME FROM ...;
select name parts separately 
  SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM ...;
and do the formatting (concatenation) on the client (which btw. allows you to have only one separator space if middle name is empty/null).

So what will happen when you do call UDF with NULL parameter ?

It is simple - UDF will get zero value. For string it means "zero length string", for date it means "date with zero offset from base date" (which is "1858-11-17"), etc. You can't distinguish (inside UDF) whether e.g. numeric parameter was really zero or NULL.
Sometimes this behaviour can be useful.

Example

CREATE TRIGGER tr FOR tab BEFORE UPDATE AS
BEGIN
  NEW.FIRST_NAME = TrimUDF (NEW.FIRST_NAME);
  NEW.LAST_NAME  = TrimUDF (NEW.LAST_NAME);
  ...
  /* side-effect of calling TRIM UDF is converting NULL to Empty string */
END

Pass non-string parameters converted to string

If you need to pass numeric (or date, time, timestamp) parameter, write your UDF to accept the value as string. That way, zero value will be converted by IB to string '0', while NULL will be converted to empty string ''. Valid date will be passed as normal string, NULL date will be passed as empty string.

Example

This function will return either absolute value, or -1 if the argument was NULL (or not a valid integer). 
function TEST_Abs (val: PChar): integer; cdecl;
begin
  try
    Result := Abs( StrToInt(val) );
  except
    Result := -1;
  end;
end;


DECLARE EXTERNAL FUNCTION TEST_ABS
  CSTRING(20)
  RETURNS INTEGER BY VALUE
  ENTRY_POINT 'TEST_Abs' MODULE_NAME 'UDF_Examples';


SELECT I, TEST_Abs(I) FROM TAB;

       I TEST_ABS
======== ========
       5        5
      -5        5
       0        0
  <null>       -1

Pass string parameters with extra character concatenated

Result of concatenating valid string and NULL value is NULL. So if the UDF is called that way 
 MyUDF ('*' || MyField)
then zero-length parameter indicates NULL; otherwise remove first character (increment pointer in case of CSTRING) and continue.

Example

This function will return length of trimmed string parameter, or -1 if the argument was NULL. 
function TEST_TrimLen (val: PChar): integer; cdecl;
begin
  Result := -1;
  if val[0] = #0 then Exit;

  Inc(val);  // Skip extra character.
  Result := Length(Trim(val));
end;


DECLARE EXTERNAL FUNCTION TEST_TRIMLEN
  CSTRING(20)
  RETURNS INTEGER BY VALUE
  ENTRY_POINT 'TEST_TrimLen' MODULE_NAME 'UDF_Examples';


SELECT X || '<', TEST_TrimLen('#' || X) FROM TAB2;

            TEST_TRIMLEN
=========== ============
ABCD<                  4
 EFGH <                4
 XYZ <                 3
<                      0
<null>                -1

Use separate indicator parameter

You can use extra input parameter to carry null-informatoin, like 
  IF (IntegerVariable IS NOT NULL)
  THEN Result = MyUDF(IntegerVariable, 1);
  ELSE Result = MyUDF(IntegerVariable, 0);
or 
  MyUDF(IntegerVariable, 1+0*IntegerVariable)
Then just test second parameter in UDF, 0 means null, 1 means not null.

Blob parameters

Testing NULL values in blob input parameters is easy. Blob parameters are passed to UDF as a pointer to BLOB structure. If the parameter is NULL, then blob_handle element of this structure will be zero.

Full BLOB structure definition/description can be found either in Developer's Guide or in jrd/val.h file (IB/FB source code) or in include/ibase.h file (Firebird only).

Example

This function will return either blob size, or -1 if the parameter is NULL 
type
  TBlob = record
    GetSegment         : Pointer;
    BlobHandle         : ^Integer;
    SegmentCount       : LongInt;
    MaxSegmentLength   : LongInt;
    TotalSize          : LongInt;
    PutSegment         : Pointer;
  end;
  PBlob = ^TBlob;


function TEST_BlobSize(inBlob: PBlob): integer; cdecl;
begin
  Result := -1;
  if (not Assigned(inBlob)) or
     (not Assigned(inBlob^.BlobHandle)) then Exit;

  Result := inBlob^.TotalSize;
end;


DECLARE EXTERNAL FUNCTION TEST_BlobSize
  BLOB
  RETURNS INTEGER BY VALUE
  ENTRY_POINT 'TEST_BlobSize' MODULE_NAME 'UDF_Examples';


CREATE TABLE TAB3 (I INTEGER, B BLOB);
INSERT INTO TAB3 (I,B) VALUES (1, 'abc');
INSERT INTO TAB3 (I,B) VALUES (2, '');
INSERT INTO TAB3 (I,B) VALUES (3, NULL);
/* Note: Firebird can insert strings to blob fields directly. */
/* With InterBase you may need to use UDF for String->Blob conversion. */


SELECT I, TEST_BlobSize(B) FROM TAB3;

      I TEST_BLOBSIZE
======= =============
      1             3
      2             0
      3            -1

Use Firebird's descriptors

Parameters are passed to ordinary UDFs by reference. Firebird supports another method - passing parameters by descriptor, which (among other useful things) allows direct detection of NULLs in the UDF. (The descriptor mechanism is in fact presented in all IB versions; however, Firebird has fixed some bugs and added syntax to declare such UDF in SQL.)

Full PARAMDSC structure definition/description can be found in include/ibase.h file (Firebird only).

Example

This function will return either absolute value, or -1 if the argument was NULL. 
const
  DSC_null = 1;

type
  ParamDsc = record
    dsc_dtype   : Byte;
    dsc_scale   : ShortInt;
    dsc_length  : Word;
    dsc_sub_type: SmallInt;
    dsc_flags   : Word;
    dsc_address : Pointer;
  end;
  PParamDsc = ^ParamDsc;

  PInteger  = ^Integer;

function TEST_Abs_Dsc (val: PParamDsc): integer; cdecl;
begin
  Result := -1;
  if (val=nil) or ((val^.dsc_flags and DSC_null) <> 0) then Exit;

  // This is just demo. Fully functional UDF has to test dsc_dtype here.
  Result := Abs( PInteger(val.dsc_address)^ );
end;


DECLARE EXTERNAL FUNCTION TEST_ABS_DSC
  INTEGER BY DESCRIPTOR
  RETURNS INTEGER BY VALUE
  ENTRY_POINT 'TEST_Abs_Dsc' MODULE_NAME 'UDF_Examples';


SELECT I, TEST_Abs_DSC(I) FROM TAB;

       I TEST_ABS_DSC
======== =============
       5             5
      -5             5
       0             0
  <null>            -1
Copyright © 2002 Ivan Prenosil