Firebird Documentation Index → Firebird Null Guide → User-Defined Functions (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.
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.
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 play 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, e.g. like this:
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 and earlier.
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 NULL
s
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.
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.
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
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.
Firebird Documentation Index → Firebird Null Guide → User-Defined Functions (UDFs) |