User-Defined Functions (UDFs)

NULL <–> non-NULL conversions you didn't ask for
Descriptors
Improvements in Firebird 2
Being prepared for undesired conversions
More on UDFs

UDFs (User-Defined Functions) are functions that are not internal to the engine, but defined in separate modules. Firebird ships with two UDF libraries: ib_udf (a widely used InterBase library) and fbudf. You can add more libraries, e.g. by buying or downloading them, or by writing them yourself. UDFs can't be used out of the box; they have to be “declared” to the database first. This also applies to the UDFs that come with Firebird itself.

`NULL` `<–>` non-`NULL` conversions you didn't ask for

Teaching you how to declare, use, and write UDFs is outside the scope of this guide. However, we must warn you that UDFs can occasionally perform unexpected NULL conversions. This will sometimes result in NULL input being converted to a regular value, and other times in the nullification of valid input like '' (an empty string).

The main cause of this problem is that with “old style” UDF calling (inherited from InterBase), it is not possible to pass NULL as input to the function. When a UDF like LTRIM (left trim) is called with a NULL argument, the argument is passed to the function as an empty string. (Note: in Firebird 2 and up, it can also be passed as a null pointer. We'll get to that later.) From inside the function there is no way of telling if this argument represents a real empty string or a NULL. So what does the function implementor do? He has to make a choice: either take the argument at face value, or assume it was originally a NULL and treat it accordingly.

If the function result type is a pointer, returning NULL is possible even if receiving NULL isn't. Thus, the following unexpected things can happen:

You call a UDF with a NULL argument. It is passed as a value, e.g. 0 or ''. Within the function, this argument is not changed back to NULL; a non-NULL result is returned.
You call a UDF with a valid argument like 0 or ''. It is passed as-is (obviously). But the function code supposes that this value really represents a NULL, treats it as a black hole, and returns NULL to the caller.

Both conversions are usually unwanted, but the second probably more so than the first (better validate something NULL than wreck something valid). To get back to our LTRIM example: in Firebird 1.0, this function returns NULL if you feed it an empty string. This is wrong. In 1.5 it never returns NULL: even NULL strings (passed by the engine as '') are “trimmed” to empty strings. This is also wrong, but it's considered the lesser of two evils. Firebird 2 has finally got it right: a NULL string gives a NULL result, an empty string is trimmed to an empty string – at least if you declare the function in the right way.

Descriptors

As early as in Firebird 1.0, a new method of passing UDF arguments and results was introduced: “by descriptor”. Descriptors allow NULL signalling no matter the type of data. The fbudf library makes ample use of this technique. Unfortunately, using descriptors is rather cumbersome; it's more work and less fun for the UDF implementor. But they do solve all the traditional NULL problems, and for the caller they're just as easy to use as old-style UDFs.

Improvements in Firebird 2

Firebird 2 comes with a somewhat improved calling mechanism for old-style UDFs. The engine will now pass NULL input as a null pointer to the function, if the function has been declared to the database with a NULL keyword after the argument(s) in question:

declare external function ltrim
  cstring(255) null
  returns cstring(255) free_it
  entry_point 'IB_UDF_ltrim' module_name 'ib_udf';

This requirement ensures that existing databases and their applications can continue to function like before. Leave out the NULL keyword and the function will behave like it did under Firebird 1.5.

Please note that you can't just add NULL keywords to your declarations and then expect every function to handle NULL input correctly. Each function has to be (re)written in such a way that NULLs are dealt with correctly. Always look at the declarations provided by the function implementor. For the functions in the ib_udf library, consult ib_udf2.sql in the Firebird UDF directory. Notice the 2 in the file name; the old-style declarations are in ib_udf.sql.

These are the ib_udf functions that have been updated to recognise NULL input and handle it properly:

ascii_char
lower
lpad and rpad
ltrim and rtrim
substr and substrlen

Most ib_udf functions remain as they were; in any case, passing NULL to an old-style UDF is never possible if the argument isn't of a referenced type.

On a side note: don't use lower, .trim and substr* in new code; use the internal functions LOWER, TRIM and SUBSTRING instead.

“Upgrading” `ib_udf` functions in an existing database

If you are using an existing database with one or more of the functions listed above under Firebird 2, and you want to benefit from the improved NULL handling, run the script ib_udf_upgrade.sql against your database. It is located in the Firebird misc\upgrade\ib_udf directory.

Being prepared for undesired conversions

The unsolicited NULL <–> non-NULL conversions described earlier usually only happen with legacy UDFs, but there are a lot of them around (most notably in ib_udf). Also, nothing will stop a careless implementor from doing the same in a descriptor-style function. So the bottom line is: if you use a UDF and you don't know how it behaves with respect to NULL:

Look at its declaration to see how values are passed and returned. If it says “by descriptor”, it should be safe (though it never hurts to make sure). Ditto if arguments are followed by a NULL keyword. In all other cases, walk through the rest of the steps.
If you have the source and you understand the language it's written in, inspect the function code.
Test the function both with NULL input and with input like 0 (for numerical arguments) and/or '' (for string arguments).
If the function performs an undesired NULL <–> non-NULL conversion, you'll have to anticipate it in your code before calling the UDF (see also Testing for NULL – if it matters, elsewhere in this guide).

The declarations for the shipped UDF libraries can be found in the Firebird subdirectory examples (v. 1.0) or UDF (v. 1.5 and up). Look at the files with extension .sql

More on UDFs

To learn more about UDFs, consult the InterBase 6.0 Developer's Guide (free at http://www.ibphoenix.com/downloads/60DevGuide.zip), Using Firebird and the Firebird Reference Guide (both on CD), or the Firebird Book. CD and book can be purchased via http://www.ibphoenix.com.