MySQL Bugs: #10518: error when using parameter with delphi query component

Bug #10518	error when using parameter with delphi query component
Submitted:	10 May 2005 15:38	Modified:	3 Apr 2007 10:51
Reporter:	karyadi .	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	MyODBC-3.51.06	OS:	Windows (Win XP Pro)
Assigned to:		CPU Architecture:	Any

Description:
I develope some application using Delphi 5.0 programming, using the BDE for the connection, and ODCB Driver 3.51.06.
The bug is :
When i try to select with a query component, which i use some parameter for the "WHERE" parameter, it's show an error "Aplication Not Applicable".
Sample (first):
   query1.sql.text := 'select * from trx_sales where date1>:p1 and date1<:p2';
   query1.parambyname('date1').asdatetime := datetimepicker1.datedate;
   query1.parambyname('date1').asdatetime := datetimepicker2.datedate;
   query1.open;

But When i try use like this (second):
   query1.sql.text := 'select * from trx_sales where date1>:p1 and date1<:p2';
   query1.parambyname('date1').asstring := datetimetostr(datetimepicker1.datedate);
   query1.parambyname('date1').asstring := datetimetostr(datetimepicker2.datedate);
   query1.open;

There is no error, but the parameter that i used, it's not working.
so ... Can Any one can help me.
or may be it was a bug from MyODBC-3.51.06
because when i'm using MyODBC-2.50 -NT there is no problem when i'm using then first coding.

Thx..
best regards,

karyadi

How to repeat:
I develope some application using Delphi 5.0 programming, using the BDE for the connection, and ODCB Driver 3.51.06.
The bug is :
When i try to select with a query component, which i use some parameter for the "WHERE" parameter, it's show an error "Aplication Not Applicable".
Sample (first):
   query1.sql.text := 'select * from trx_sales where date1>:p1 and date1<:p2';
   query1.parambyname('date1').asdatetime := datetimepicker1.datedate;
   query1.parambyname('date1').asdatetime := datetimepicker2.datedate;
   query1.open;

But When i try use like this (second):
   query1.sql.text := 'select * from trx_sales where date1>:p1 and date1<:p2';
   query1.parambyname('date1').asstring := datetimetostr(datetimepicker1.datedate);
   query1.parambyname('date1').asstring := datetimetostr(datetimepicker2.datedate);
   query1.open;

There is no error, but the parameter that i used, it's not working.
so ... Can Any one can help me.
or may be it was a bug from MyODBC-3.51.06
because when i'm using MyODBC-2.50 -NT there is no problem when i'm using then first coding.

Thx..
best regards,

karyadi

Could you write structure of table ?  I would like to know a type of field - date1.
Do you use "datetime" or "date" type ?

myodbc version. 3.51.11.00
delphi 5, mysql 4.1.10a, Winxp pro
i have same similiar error. i'm trying to port my delphi+mssql application to
delphi+mysql. i can't send any datetime parameter to any TQuery component in
delphi.
i used it with other mysql 4 versions. there is no difference.

(TEST CASE)

CREATE TABLE `message` (
  `MessageNo` double NOT NULL default '0',
  `User` char(8) NOT NULL default '',
  `StartDate` datetime default NULL,
  `Readed` char(1) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

in delphi drop a tquery component on a form.
and connect it to mysql database.

query1.Close;
query1.SQL.Clear;
query1.SQL.Add('SELECT * FROM message');
query1.SQL.Add('WHERE StartDate<=:QDate');
query1.ParamByName('QDate').AsDateTime := MyDate; //--> error here
query1.Open;

when you run app. it doesnt accept parameter as datetime...

// this function shows the code where MyDate is coming from
Function TfrmGSifre.MyDate: TDateTime;
var
  Year, Month, Day: Word;
begin
  {Get MySQL Server's System date}
  With QryGetDate Do
  try
    Close;
    SQL.Clear;
    SQL.Add('SELECT Date= GetDate()');
    Open;
    DecodeDate(FieldByName('Date').asDateTime, Year, Month, Day);
    MyDate:= EncodeDate(Year, Month, Day);
  finally
    Close;
  end;
end;

for information, only way i can find it to work is this:
changing parameter type to asstring and changing MyDate to string as follows:

query1.ParamByName('QDate').AsString :=
StringReplace(StringReplace(Datetimetostr(MyDate),'/',DateSeparator,[rfReplaceAl
l]),'(','',[rfReplaceAll]);

hope this helps but its to will take a huge work for a big application porting
from mssql to mysql. i have hundreds of forms and thousands of query.

same query working good with mssql... using parameter asdatetime.

other users' problem reports about the same bug :

#5395 http://bugs.mysql.com/bug.php?id=5395
#1026 http://bugs.mysql.com/bug.php?id=1026

thanks for help...

no, sorry its not working as i say, i would think it was working but the server i selected was mssql not mysql. So this datetime -> string converting operation is not working on mysql server:

query1.ParamByName('QDate').AsString:=StringReplace(StringReplace(Datetimetostr(MyDate),'/',DateSeparator,[rfReplaceAl
l]),'(','',[rfReplaceAll]);

Again it's working in mssql with this converting too.

So i still waiting for solving of this bug.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

This is the duplicate of Bug#18084. Please see available workarounds there.