for InterBase and FireBird

Handling of <null> in FireBird 2.0
In FireBird 2.0 - and only here - it's possible, that UDFs can return <null> instead of “0" or “empty string”. For example the folowing SQL      SELECT F_LEFT(NULL, 5) FROM RDB$DATABASE returns until FireBird 2.0 an empty string. But this is wrong. The right answer is <null>. To save the old behaviour and - if you want - to have ALTERNATIVE the "right" behaviour, there is a possibility (in the DECLAIRE-script) in FireBird 2.0 to do this. If you declare - for our example -     DECLARE EXTERNAL FUNCTION F_LEFT     CSTRING(254),     INTEGER     RETURNS CSTRING(254) FREE_IT     ENTRY_POINT 'left' MODULE_NAME 'FreeAdhocUDF' the behaviour is as you know - an input of  <null> returns an empty string. If you declare     DECLARE EXTERNAL FUNCTION F_LEFT     CSTRING(254) NULL,     INTEGER NULL     RETURNS CSTRING(254) FREE_IT     ENTRY_POINT 'left' MODULE_NAME 'FreeAdhocUDF' an input of <null> returns <null>. See also the release-notes of FireBird 2.0. We have changed all UDFs with one ore more inputs for this possibility. Note for all functions: if you change nothing, the return is as before. Even when you use the new declare-script, they can return <null>. If one of the input-parameters is <null>, the output is also <null>. Functions with more constellations to return <null> are marked with a * in content an in description. To use this new behaviour in existing databases, you have to delete the UDFs and to declare them new. If this is not possible - because there are dependendies - you can do an UPDATE on RDB$FUNCTION_ARGUMENTS system-table to change it. To do this, the value of RDB$MECHANISM must be set to 5 for all parameters which had to become the behaviour of <null>, in our example parameter 1 and 2. The UPDATE for our example would be:     UPDATE RDB$FUNCTION_ARGUMENTS     SET RDB$MECHANISM = 5     WHERE RDB$FUNCTION_NAME = 'left'     AND RDB$ARGUMENT_POSITION IN (1, 2); For many functions there was the need to change the declarations basically. A "RETURN INTEGER BY VALUE" must become a "RETURN INTEGER FREE_IT", also if you don't want to use the return <null>. To use this functions you had to update this declarations with the update-script - the old version with '... BY VALUE" dosn't run correct! The easiest way is to delete and declare the UDFs new.