Delphi - Save DateTime Null to DB (ADO / MySQL)

Question

Hi,

i have a delphi form with some DB Controls on it.

To represent a date I use the TJvDBDatePickerEdit (from JCL), which has a nice property

ShowCheckBox := True;

to allow the user to enter that no date is known (DBNull).

I verify, that nulling the DatePicker works as expected by:

procedure Tframe.adoQueryBeforePost(DataSet: TDataSet);
begin
  if (qryAuftraege.FieldByName('MyDateField').IsNull) then
  begin
    ShowMessage('IsNull!');
  end;
end;

The message box shows. So the field that the ADO Component should write is of type varNull.

Now there is this pipeline: TADOQuery -> TADOConnection -> MS OLE DB Provider for ODBC Drivers -> MySQL ODBC 5.1 Driver

And the MySQL ODBC driver now shows in it's log:

UPDATE `db`.`table` SET `MyDateField`=_latin1'0004-00-00' WHERE `ID`=5 AND `MyDateField`=_latin1'2009-06-17';

Well, the date is stored as '0004-00-00' (which MySQL seems to save like that). Now when querying the entry, it is recognized as Null by Delphi, but when it shall be updated the next time, calling:

UPDATE `bvl`.`auftraege` SET `MyDateField`=_latin1'2009-06-17' WHERE `ID`=5 AND `MyDateField` IS NULL;

of course fails, because MyDateField is NOT NULL (at least in the MySQL DB).

Where does this string '0004-00-00' come from? If it is a bug somewhere, where could I possibly intercept it?

I already know that there are components to buy that provide direct MySQL connections which (I assume) don't show this behaviour.

Answer 1

To save a null date/time to the database record, just call

qryAuftraege.FieldByName('MyDateField').clear;

Answer 2

I don't know the implications of doing this, but I've always used

qryAuftraege.FieldByName('MyDateField').AsString:='';

Answer 3

Be sure to check your MySQL options as discussed here...

Then, I would try with a different set of connection components as the stack (ADO + ODBC 5.1 driver) might the problem here.
Have you tried with another ODBC driver? Or with DBX...