In order to define a NULLable
column of such types, you could
use them as types for your TSQLRecord
class definition:
type TSQLNullableRecord = class(TSQLRecord) protected fInt: TNullableInteger; fBool: TNullableBoolean; fFlt: TNullableFloat; fCurr: TNullableCurrency; fDate: TNullableDateTime; fTimeStamp: TNullableTimeLog; fCLOB: TNullableUTF8Text; fText: TNullableUTF8Text; published property Int: TNullableInteger read fInt write fInt; property Bool: TNullableBoolean read fBool write fBool; property Flt: TNullableFloat read fFlt write fFlt; property Curr: TNullableCurrency read fCurr write fCurr; property Date: TNullableDateTime read fDate write fDate; property TimeStamp: TNullableTimeLog read fTimeStamp write fTimeStamp; property CLOB: TNullableUTF8Text read fCLOB write fCLOB; property Text: TNullableUTF8Text index 32 read fText write fText; end;
Such a class would let the ORM handle SQL NULL values as expected, i.e.
returning a null
variant value, or an integer/number/text value if
there is something stored.
Of course, the corresponding column in the database would have the expected
data type, e.g. a NULLABLE INTEGER
for
TNullableInteger
property.
Note that TNullableUTF8Text
is defined as a
RawUTF8
usual field.
That is, without any size limitation by default (as for the CLOB
property), or with an explicit size limitation using the index ###
attribute (as for Text
property, which would be converted as a
VARCHAR(32)
SQL column).
You could use the following wrapper functions to create a
TNullable*
value from any non-nullable standard Delphi value:
function NullableInteger(const Value: Int64): TNullableInteger; function NullableBoolean(Value: boolean): TNullableBoolean; function NullableFloat(const Value: double): TNullableFloat; function NullableCurrency(const Value: currency): TNullableCurrency; function NullableDateTime(const Value: TDateTime): TNullableDateTime; function NullableTimeLog(const Value: TTimeLog): TNullableTimeLog; function NullableUTF8Text(const Value: RawUTF8): TNullableUTF8Text;
Some corresponding functions are able to return the expected
null
value for each kind, with strong typing (to be used for FPC
compatibility, which does not allow direct assignment to a TNullable* =
type variant
property):
function NullableIntegerNull: TNullableInteger; function NullableIntegerBoolean: TNullableBoolean; ...
You could check for a TNullable*
value to contain null, using
the following functions:
function NullableIntegerIsEmptyOrNull(const V: TNullableInteger): Boolean; function NullableBooleanIsEmptyOrNull(const V: TNullableBoolean): Boolean; ...
Or retrieve a Delphi non-nullable value in one step, using the corresponding wrappers:
function NullableIntegerToValue(const V: TNullableInteger; out Value: Int64): Boolean; function NullableBooleanToValue(const V: TNullableBoolean; out Value: Boolean): Boolean; ... function NullableIntegerToValue(const V: TNullableInteger): Int64; function NullableBooleanToValue(const V: TNullableBoolean; out Value: Boolean): Boolean; ...
Thanks to those types, and their corresponding wrapper functions, you have at hand everything needed to safely store some nullable values into your application database, with proper handling on Delphi side.
Thanks hnb for the idea, feedback, and support!
The
documentation has been updated, and should be browsed as reference.
Feedback is welcome on our forum, as usual!