Delphi - 将空日期时间保存到数据库（ADO / MySQL）
我有一个 Delphi 表单，上面有一些数据库控件。
为了表示日期，我使用了 TJvDBDatePickerEdit（来自 JCL），它有一个很好的属性，
ShowCheckBox := True;
允许用户输入未知日期 (DBNull)。
我验证了 DatePicker 的清空是否按预期工作：
procedure Tframe.adoQueryBeforePost(DataSet: TDataSet);
begin
if (qryAuftraege.FieldByName('MyDateField').IsNull) then
begin
ShowMessage('IsNull!');
end;
end;
消息框显示。 因此，ADO 组件应写入的字段的类型为 varNull。
现在有这个管道：
TADOQuery -> TADOConnection -> MS OLE DB Provider for ODBC Drivers -> MySQL ODBC 5.1 Driver
MySQL ODBC 驱动程序现在在其日志中显示：
UPDATE `db`.`table` SET `MyDateField`=_latin1'0004-00-00' WHERE `ID`=5 AND `MyDateField`=_latin1'2009-06-17';
嗯，日期存储为 '0004-00-00' （MySQL 似乎是这样保存的）。 现在，当查询条目时，Delphi将其识别为Null，但是当下次更新它时，调用：
UPDATE `bvl`.`auftraege` SET `MyDateField`=_latin1'2009-06-17' WHERE `ID`=5 AND `MyDateField` IS NULL;
当然会失败，因为MyDateField不是NULL（至少在MySQL DB中）。
这个字符串'0004-00-00'来自哪里？ 如果它是某个地方的错误，我可以在哪里拦截它？
我已经知道有一些组件可以提供直接的 MySQL 连接，但（我认为）不会表现出这种行为。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
要将空日期/时间保存到数据库记录中，只需调用
To save a null date/time to the database record, just call
请务必检查您的 MySQL 选项，如此处所述...
然后，我会尝试使用一组不同的连接组件，因为堆栈（ADO + ODBC 5.1 驱动程序）可能会出现问题。
您是否尝试过使用其他 ODBC 驱动程序？
或者使用 DBX...
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...
我不知道这样做的含义，但我一直使用
I don't know the implications of doing this, but I've always used