Synopse Open Source

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!