At 12:31 AM 13/12/2008, you wrote:

>Hi,
>
>I have an issue with inserting DateTime values using Delphi with ADO
>and parameters.
>
>I have the following SQL in an ADOCommand component:
>
>insert into ORDER (ORDERID, CHANGEDDATE) values (:OrderID, :Changed);
>
>I set the paramter with the following code:
>
>AdoCommand1.Parameters.ParamValues['OrderID'] := '000001';
>AdoCommand1.Parameters.ParamValues['Changed'] := Now;
>
>When I execute the AdoCommand the database is updated, but the Time
>part of Now is always 00:00.
>The field CHANGEDDATE is defined as Timestamp in the firebird database.
>
>- Running Firebird 1.5 and Firebird ODBC 2.0.0.148.
>
>When running the exact same program against an MS SQL Server 2005 the
>time part is updated correctly.

That's because ADO's TParameters are all TOleVariants, which natively interpret the Windows date/time formats for Microsoft databases. The Firebird driver has to offer you a method for casting the TOleVariant to a format that Firebird recognises as a TIMESTAMP.

Something in your mix has succeeded in recognising that the parameter needs to be a TIMESTAMP type (otherwise you would have got an exception) but there is not enough available for the driver to convert the time part. The driver is following some implementation rule that says "If I can't figure out the time part, just swallow it and send it as midnight."

>Anyone experienced the same? Is this a problem in the Firebird ODBC
>driver?

This is not the right list to ask this question. Please raise it in the firebird-odbc-devel list. The driver should have some casting technique to interpret an OleVariant correctly as a DateTime.

Native Delphi drivers provide casting methods for the stronger TParam classes, e.g., AsDateTime for timestamps, because variants (realised in the Value property of TParam and ADO's TParameter) don't have the capability to interpret Delphi's native date/time formats for cross-platform databases like Firebird without them. I can't *find* any casting methods exposed for TADOCommand's Parameters but you could test this by trying:

AdoCommand1.Parameters.ParamValues['Changed'].AsDateTime := Now;

FWIW, here's an observation about the particular example you have given here - which *is* on-topic for this list: don't apply client-side timestamping to anything you are going to store in the database. Timestamping needs to be based on server time, not parameterised on client time. Change the statement it to one of the following:

insert into ORDER (ORDERID, CHANGEDDATE) values (:OrderID, CURRENT_TIMESTAMP);
or
insert into ORDER (ORDERID, CHANGEDDATE) values (:OrderID, CAST('NOW' AS TIMESTAMP));

Or, even better, use a Before Insert trigger for this timestamping.

./heLen