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:Microsoft Windows (Win XP Pro)
Assigned to: CPU Architecture:Any

[10 May 2005 15:38] karyadi .
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
[15 May 2005 8:20] Vasily Kishkin
Could you write structure of table ?  I would like to know a type of field - date1.
Do you use "datetime" or "date" type ?
[9 Jun 2005 10:16] KAZANCI ibrahim
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.
[9 Jun 2005 10:20] KAZANCI ibrahim
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...
[9 Jun 2005 11:49] KAZANCI ibrahim
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.
[15 Jun 2005 23:00] Bugs System
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".
[3 Apr 2007 10:51] Tonci Grgin
This is the duplicate of Bug#18084. Please see available workarounds there.