Set database value to NULL

Question

I have an SQLite double field that is used to display a date. There is no default value for the field. I need the field value to be NULL when there is no date entered because when the field is displayed as a date, it shows '12/30/1899'. For some reason, I cannot set the field to NULL. I am using C++ Berlin 10.1. Here is the code:

FDQuery1->First();
while (!FDQuery1->Eof) {
    if (!VarIsNull(FDQuery1->FieldByName("DateLeft")->Value))
        if (FDQuery1->FieldByName("DateLeft")->Value < 100) {
            FDQuery1->Edit();
            FDQuery1->FieldByName("DateLeft")->Value = NULL;
            FDQuery1->Post();
        }
    FDQuery1->Next();
}

"FDQuery1->FieldByName("DateLeft")->Value" is still 0 after running this code.

How can I set the value to NULL?

Answer 1

It would seem that this question is really about FireDAC, which is the technology you're using in C++ to interface with a database. It doesn't really matter what that database is (SQLite or something else).

The official documentation I found isn't much help, but we can already assume that writing the C macro NULL like this isn't going to do what you want. That's a utility for C code that, when converted to a number, is zero.

It is not possible for the FireDAC library to notice that you gave the expression NULL rather than 0, because the expression NULL holds no type information that says that's what it is (at best, it'll be a void*, so you may get some weird string-like semantics out of it — this weirdness is why nullptr was added in C++11!).

So, NULL is not the right tool for the job. Yes, I know that SQL has a keyword called NULL that looks the same and does what you want in an SQL query, but we're not writing SQL. We're writing C++. So we can forget that.

I found some old Delphi chatter that seems to discuss the same (or a similar) interface and suggests using the Clear() method on a field to erase its value, implicitly making it "null".

Give that a go.

So, something like:

FDQuery1->FieldByName("DateLeft")->Clear();