ParamByName('testdatefrom').AsDateTime No Result When Update To MySQL 5.0.41

[chintatlim]

MyDAC Version: 4.40.0.22
MySQL Version: 5.0.41

SQL
Select * From test
where testdate >= :testdatefrom and testdate <= :testdateto

ParamByName('testdatefrom').AsDateTime = DateFrom
ParamByName('testdateto').AsDateTime = DateTo

Last time using MySQL 5.0.33 got results, but update to MySQL 5.0.41 no result return, why?

[Jackson]

We couldn't reproduce the problem.
Please specify the definition of your database objects and the script for populating table with data.
Also specify the exact version of Delphi or C++Builder that you use.

[chintatlim]

Delphi2007 Eng

Script:

DROP TABLE IF EXISTS `test`.`test`;
CREATE TABLE `test`.`test` (
`saleskey` int(10) unsigned NOT NULL auto_increment,
`salesdate` date default NULL,
`amount` decimal(20,2) NOT NULL default '0.00',
PRIMARY KEY (`saleskey`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Insert Into `test`.`test`
Values (Null, '2007-5-17', 100), (Null, '2007-5-18', 120);

---------------------------------------------------------------------
Delphi Program:

*.pas

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, DBGrids, DB, MemDS, DBAccess, MyAccess;

type
TForm1 = class(TForm)
MyQuery1: TMyQuery;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Button1: TButton;
Button2: TButton;
MyConnection1: TMyConnection;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
LdtDateFrom, LdtDateTo: TDateTime;
begin
MyConnection1.Server:= '127.0.0.1';
MyConnection1.Username:= 'root';
MyConnection1.Port:= 3307;
MyConnection1.Database:= 'test';
MyConnection1.Connect;

LdtDateFrom:= EncodeDate(2007, 5, 17);
LdtDateTo:= EncodeDate(2007, 5, 17);

MyQuery1.SQL.Clear;
MyQuery1.SQL.Add('Select * from test'#13#10 +
'Where salesdate >= :salesdatefrom and salesdate <= :salesdateto');
MyQuery1.ParamByName('salesdatefrom').AsDateTime:= LdtDateFrom;
MyQuery1.ParamByName('salesdateto').AsDateTime:= LdtDateTo;
MyQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
MyQuery1.Close;
MyConnection1.Disconnect;
end;

end.


----------------------------------------------------------------------------

*.dfm

object Form1: TForm1
Left = 0
Top = 0
Caption = 'Form1'
ClientHeight = 168
ClientWidth = 415
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object DBGrid1: TDBGrid
Left = 0
Top = 0
Width = 393
Height = 129
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
end
object Button1: TButton
Left = 48
Top = 135
Width = 145
Height = 25
Caption = 'Connect && Open Query'
TabOrder = 1
OnClick = Button1Click
end
object Button2: TButton
Left = 208
Top = 135
Width = 145
Height = 25
Caption = 'Disconnect && Close Query'
TabOrder = 2
OnClick = Button2Click
end
object MyQuery1: TMyQuery
Connection = MyConnection1
FetchAll = True
Left = 264
Top = 64
end
object DataSource1: TDataSource
DataSet = MyQuery1
Left = 296
Top = 64
end
object MyConnection1: TMyConnection
Left = 232
Top = 64
end
end

[Jackson]

Use the TParam.AsDate property instead of TParam.AsDateTime.
It looks like this is a new behaviour in the MySQL 5.0.41 server.
For example:

Code: Select all

MyQuery1.ParamByName('salesdatefrom').AsDate := LdtDateFrom; 
MyQuery1.ParamByName('salesdateto').AsDate := LdtDateTo; 
MyQuery1.Open;

[chintatlim]

You are right, this is MySQL 5.0.41 date comparison new behavior, please refer http://bugs.mysql.com/bug.php?id=28336.

This new behavior break our application report result. So. just wait MySQL to solve this problem.

Thank you.