Using a datetime parameter with ADO (ODBC) loses time part

Question

I stumbled onto this problem yesterday when I was busy writing some unit tests using SQLLite. My environment is Windows7/Delphi XE.

Using TADOQuery in conjunction with a TDateTime parameter results in loss of the time part.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ADODb, DateUtils, DB;

type
  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);

var DbConn : TADOConnection;
    Qry    : TADOQuery;
    DT     : TDateTime;

begin
 DBConn := TADOConnection.Create(nil);
 DBConn.ConnectionString := 'Provider=MSDASQL.1;Extended Properties="DRIVER=SQLite3 ODBC Driver;Database=:memory:;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"';
//   DBConn.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=True;User ID=%0:s;Password=%1:s;Extended Properties="DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;PORT=3306;DATABASE=test;USER=root;PASSWORD=rrr;OPTION=1048579"';
 Qry := TADOQuery.Create(nil);
 Qry.Connection := DbConn;
 try
  DBConn.Connected := True;
  Qry.SQL.Text := 'CREATE TABLE test(d datetime)';
  Qry.ExecSQL;
  Qry.ParamCheck := True;
  Qry.SQL.Text := 'INSERT INTO test (d) VALUES (:d)';
  //Qry.Parameters.ParseSQL(Qry.SQL.Text, True); // not needed
  TryEncodeDateTime(1999, 12, 12, 10, 59, 12, 0, DT);
  Qry.Parameters.ParamByName('d').Value := DT;
  Qry.Parameters.ParamByName('d').DataType := ftDateTime;
  Qry.ExecSQL;
  Qry.SQL.Text := 'SELECT d FROM test';
  Qry.Open;
  ShowMessage(FormatDateTime('MM/DD/YYYY HH:NN:SS', Qry.FieldByName('d').AsDateTime));
 finally
  FreeAndNil(Qry);
  FreeAndNil(DbConn);
 end;
end;

Funny thing is, when I comment the line Qry.Parameters.ParseSQL(Qry.SQL.Text, True); It will work fine. I need the ParseSQL part because I'm building a mini-ORM so it needs to know which parameters have to be mapped. Some observations:

• Doing the same test with MySQL5 shows the same problem (regardless of the ParseSQL part).
• This code works with SQL Server and the OLEDB driver.

I have searched the net and found some interesting links:

http://tracker.firebirdsql.org/browse/ODBC-27

http://embarcadero.newsgroups.archived.at/public.delphi.database.ado/201107/1107112007.html

http://bugs.mysql.com/bug.php?id=15681

The first link suggests 'fixing' ADODB.pas, something I do not want to do. Reading the last link, it seems that ADO maps the datetime value to date.

Answer I don't want to hear: use another library/component (like Dbexpress, Zeoslib,...)

I am not sure what would be be the most sensible approach to resolve this problem.

As Linas and Marjan Venema suggested I can omit the ParseSQL part. So the code works now with SQLlite IF I omit the line Qry.Parameters.ParamByName('d').DataType := ftDateTime;.

But MySQL refuses to save the time part. Am I seeing a compatibilty problem between ADO and MySQL ODBC here?

Cleodal

I'd set the datatype before setting the value, but not sure that would make any difference. Why does building a mini ORM need ParseSQL? I have build some ORM type libs and never needed it? IIRC Qry.SQL.Prepare should also populate the Parameters collection. – Bryson

I need to know what parameters I have in my Query and the values will be mapped from an object using rtti. you mean Qry.Prepared := true; ? makes no difference. – Cleodal

@Cleodal There is no need to call ParseSQL by yourself. It is called automatically when SQL.Text changes (ParamCheck must be True). – Solarize

The SQL.Text IS changed BEFORE you execute the query. Otherwise there would be nothing to execute... (or to set the params values for that matter) – Bryson

Ok fair enough, so the call to ParseSQL can go, but the problem with MySQL remains, the time part is not saved. – Cleodal

Here's the same problem, but from Python. – Sawtelle

Answer 1

I have tested this a bit using SQL Server and have the exact same problem when I use MSDASQL.1 (ODBC). Your code works fine with SQLOLEDB.1 and SQLNCLI10.1.

If you specify the parameter type to be ftString it will save with time using ODBC, (at least on SQL Server).

Qry.Parameters.ParamByName('d').DataType := ftString;
Qry.Parameters.ParamByName('d').Value := DateTimeToStr(DT);

Note: Be careful of local settings when you use DateTimeToStr it might not produce what your db wants it to be. A safe bet would be to use yyyy-mm-dd hh:mm:ss[.fff].

Update:

You could also set the data type of the ado parameter to adDBTimeStamp yourself. ADODB sets it to adDate when you use ftDateTime.

Qry.Parameters.ParamByName('d').ParameterObject.Type_ := adDBTimeStamp;
Qry.Parameters.ParamByName('d').Value := DT;

Confide

Converting to string just crashes the app. adDBTimeStamp works though. But now my mapping code is broken, and I am getting EOleException: "Application uses a value of the wrong type for the current operation". But I suspect this is an issue with variants. Thank you for your answer! – Cleodal

Answer 2

I had the same difficulty when importing data from SQL Server 2019 to save another database using Delphi 11.3.

I worked around the situation like this:

Preparing SQL:

ADOCommand.CommandText := 'insert into TABLE (CREATED_AT) VALUES (:CREATED_AT)';

Looping (ADOTableSource):

var FieldSource := ADOTableSource.FieldByName(FieldName);
var ParamTarget := ADOCommand.Parameters.ParamByName(FieldName);
if (FieldSource.DataType in [ftDateTime]) and (not FieldSource.IsNull) then
begin
  ParamTarget.DataType := ftString;
  ParamTarget.Value := FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz', FieldSource.AsDateTime);
end
else 
  ParamTarget.Value := FieldSource.Value;
  
ADOCommand.Execute;

Now the date is saving correctly as 2023-02-28 23:59:09.123 and no longer as 2023-02-28 23:59:09.000.

Vescuso

Answer 3

I had the same problem with VFPOLEDB (the Visual FoxPro driver) but the trick with adDBTimeStamp didn't work. FWIW, that's VFPOLEDB 9.0.0.5815 with Delphi 7 and also with RAD Studio 10 (Seattle).

In the case of VFPOLEDB there is another possible workaround that is based on the fact that in Fox the underlying representation for date/time values (type 'T') is the same as that of the date values (type 'D') passed by OLEDB, i.e. a 64-bit double where the integer part represents days since January 1st in the year 0001 and the fractional part represents the time of day.

If the table field has type 'T' and OLEDB passes a date then Fox coerces the value by doing nothing at all. So all that's needed here is adding back in the fractional part.

Example, with start_time being a date/time field:

cmd.CommandText := 'insert into foo (start_time) values (:start_time)';
cmd.Parameters.ParamByName('start_time') := Now;
cmd.Execute;

This gives 2016-05-22 00:00:00 in the table.

Now, add the fractional part like this:

cmd.CommandText := 'insert into foo (start_time) values (:start_time + :fraction)';
t := Now;
cmd.Parameters.ParamByName('start_time') := t;
cmd.Parameters.ParamByName('fraction') := Frac(t);
cmd.Execute;

This gives 2016-05-22 02:17:42. Even fractional seconds get preserved, although they do not get displayed.

Heroin