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.
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
NULLargument. It is passed as a value, e.g.
''. Within the function, this argument is not changed back to
NULL; a non-
NULLresult is returned.
- You call a UDF with a valid argument like
''. 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
NULLto 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 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.
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.
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
These are the
ib_udf functions that have been updated to recognise
NULL input and handle it properly:
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.
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
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
- 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
NULLkeyword. 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
NULLinput and with input like
0(for numerical arguments) and/or
''(for string arguments).
- If the function performs an undesired
NULLconversion, 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
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.