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?

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;