FreeAdhocUDF for InterBase and FireBird in deutschin englishen français
homeprevious pageÜbersicht • overview • aperçunext pagelast page mailing-listwas ich vermisse • what I missed •eMail Kommentar • eMail commentprint
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.
vorige Seite • previous page • passée sitenach oben • go top • vers le hautnächste Seite • next page • prochain site