NULL
values passed as parameters
to UDF
, nor a way to return NULL
as a result of calling UDF
function.
Here are some tips on how to circumvent this problem.
NULL
s for purposes it was not intended to.
For example you have column MIDDLE_NAME
in your table; if the person does not have middle name,
you should fill in empty string (''
); but sometimes people use NULL
instead -
it is wrong, because NULL
means unknown value, not missing/not assigned/empty one.
Using NULL
as middle name means - "this person probably has a middle name, but we do not
know what it is" - and not - "this person does not have any middle name".
CREATE TABLE tab ( FIRST_NAME VARCHAR(32) NOT NULL, MIDDLE_NAME VARCHAR(32) NOT NULL, LAST_NAME VARCHAR(32) NOT NULL, FULL_NAME COMPUTED BY (CapitalizeUDF(FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME)) );Without
NOT NULL
constraints, whole concatenation would be NULL
if any of the columns, usually MIDDLE_NAME
, is NULL
.
(And FULL_NAME
computed column will be empty string in this case.)
UDF
s implement functions that are write-null-through type,
i.e. NULL
input parameter will result in NULL
output value,
(e.g. function that converts string to lowercase should return NULL
if input parameter is NULL
).
If this is the case, it is not necessary to call the function at all,
because you know the return value (NULL
in this case) in advance.
UPDATE tab SET product_type = LowerUDF (product_type) WHERE product_type IS NOT NULL;
CREATE TRIGGER tr FOR tab BEFORE UPDATE AS BEGIN IF (NEW.product_type IS NOT NULL) THEN NEW.product_type = LowerUDF (NEW.product_type); END
SELECT ID, Sin(angle) AS SINE FROM tab WHERE angle IS NOT NULL UNION ALL SELECT ID, CAST(NULL AS DOUBLE PRECISION) FROM tab WHERE angle IS NULL;
NULL
s.
SELECT FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME FROM ...;select name parts separately
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM ...;and do the formatting (concatenation) on the client (which btw. allows you to have only one separator space if middle name is empty/null).
UDF
will get zero value. For string it means "zero length string",
for date it means "date with zero offset from base date" (which is "1858-11-17"), etc.
You can't distinguish (inside UDF
) whether e.g. numeric parameter was really zero or NULL
.
CREATE TRIGGER tr FOR tab BEFORE UPDATE AS BEGIN NEW.FIRST_NAME = TrimUDF (NEW.FIRST_NAME); NEW.LAST_NAME = TrimUDF (NEW.LAST_NAME); ... /* side-effect of calling TRIM UDF is converting NULL to Empty string */ END
UDF
to accept the value as string.
That way, zero value will be converted by IB to string '0'
,
while NULL
will be converted to empty string ''
.
Valid date will be passed as normal string, NULL
date will be passed as empty string.
-1
if the argument was NULL
(or not a valid integer).
function TEST_Abs (val: PChar): integer; cdecl; begin try Result := Abs( StrToInt(val) ); except Result := -1; end; end; DECLARE EXTERNAL FUNCTION TEST_ABS CSTRING(20) RETURNS INTEGER BY VALUE ENTRY_POINT 'TEST_Abs' MODULE_NAME 'UDF_Examples'; SELECT I, TEST_Abs(I) FROM TAB; I TEST_ABS ======== ======== 5 5 -5 5 0 0 <null> -1
NULL
value is NULL
.
So if the UDF
is called that way
MyUDF ('*' || MyField)then zero-length parameter indicates
NULL
;
otherwise remove first character (increment pointer in case of CSTRING) and continue.
-1
if the argument was NULL
.
function TEST_TrimLen (val: PChar): integer; cdecl; begin Result := -1; if val[0] = #0 then Exit; Inc(val); // Skip extra character. Result := Length(Trim(val)); end; DECLARE EXTERNAL FUNCTION TEST_TRIMLEN CSTRING(20) RETURNS INTEGER BY VALUE ENTRY_POINT 'TEST_TrimLen' MODULE_NAME 'UDF_Examples'; SELECT X || '<', TEST_TrimLen('#' || X) FROM TAB2; TEST_TRIMLEN =========== ============ ABCD< 4 EFGH < 4 XYZ < 3 < 0 <null> -1
IF (IntegerVariable IS NOT NULL) THEN Result = MyUDF(IntegerVariable, 1); ELSE Result = MyUDF(IntegerVariable, 0);or
MyUDF(IntegerVariable, 1+0*IntegerVariable)Then just test second parameter in UDF, 0 means null, 1 means not null.
NULL
values in blob input parameters is easy.
Blob parameters are passed to UDF
as a pointer to BLOB
structure.
If the parameter is NULL
, then blob_handle
element of this structure
will be zero.
Full BLOB
structure definition/description can be found either
in Developer's Guide or in jrd/val.h
file
(IB/FB source code) or in include/ibase.h
file (Firebird only).
type TBlob = record GetSegment : Pointer; BlobHandle : ^Integer; SegmentCount : LongInt; MaxSegmentLength : LongInt; TotalSize : LongInt; PutSegment : Pointer; end; PBlob = ^TBlob; function TEST_BlobSize(inBlob: PBlob): integer; cdecl; begin Result := -1; if (not Assigned(inBlob)) or (not Assigned(inBlob^.BlobHandle)) then Exit; Result := inBlob^.TotalSize; end; DECLARE EXTERNAL FUNCTION TEST_BlobSize BLOB RETURNS INTEGER BY VALUE ENTRY_POINT 'TEST_BlobSize' MODULE_NAME 'UDF_Examples'; CREATE TABLE TAB3 (I INTEGER, B BLOB); INSERT INTO TAB3 (I,B) VALUES (1, 'abc'); INSERT INTO TAB3 (I,B) VALUES (2, ''); INSERT INTO TAB3 (I,B) VALUES (3, NULL); /* Note: Firebird can insert strings to blob fields directly. */ /* With InterBase you may need to use UDF for String->Blob conversion. */ SELECT I, TEST_BlobSize(B) FROM TAB3; I TEST_BLOBSIZE ======= ============= 1 3 2 0 3 -1
UDF
s by reference.
Firebird supports another method - passing parameters by descriptor,
which (among other useful things) allows direct detection of NULL
s in the UDF
.
(The descriptor mechanism is in fact presented in all IB
versions;
however, Firebird has fixed some bugs and added syntax to declare such UDF
in SQL
.)
Full PARAMDSC
structure definition/description can be found in
include/ibase.h
file (Firebird only).
-1
if the argument was NULL
.
const DSC_null = 1; type ParamDsc = record dsc_dtype : Byte; dsc_scale : ShortInt; dsc_length : Word; dsc_sub_type: SmallInt; dsc_flags : Word; dsc_address : Pointer; end; PParamDsc = ^ParamDsc; PInteger = ^Integer; function TEST_Abs_Dsc (val: PParamDsc): integer; cdecl; begin Result := -1; if (val=nil) or ((val^.dsc_flags and DSC_null) <> 0) then Exit; // This is just demo. Fully functional UDF has to test dsc_dtype here. Result := Abs( PInteger(val.dsc_address)^ ); end; DECLARE EXTERNAL FUNCTION TEST_ABS_DSC INTEGER BY DESCRIPTOR RETURNS INTEGER BY VALUE ENTRY_POINT 'TEST_Abs_Dsc' MODULE_NAME 'UDF_Examples'; SELECT I, TEST_Abs_DSC(I) FROM TAB; I TEST_ABS_DSC ======== ============= 5 5 -5 5 0 0 <null> -1