MySQL Bugs: #1026: Problem with MyODBC when Inserting datetime walues trough BDE

Bug #1026	Problem with MyODBC when Inserting datetime walues trough BDE
Submitted:	11 Aug 2003 0:31	Modified:	30 May 2013 7:58
Reporter:	Denis Kolman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.06	OS:	Windows (Win32 (any))
Assigned to:		CPU Architecture:	Any

Description:
I have an older application that works fine with Oracle 8 (BDE) and now I
must port this application to MySQL 4.01. Since I'm not in position to
change the way application connects to database (it is a rather large
application and a change to OLEDB and ADO could take a few months) I am
forced to use MyODBC 3.51.6 and BDE.

Everything works OK untill I use a TUpdateSQL component in combination with
Datetime database fileds ( with TQuery componets works OK). When commiting
changes, I get an 'Operation not applicable' error.

For example:

Table a is Innodb type : id - decimal

datevalue - datetime

Query1.Sql.Text :='select id,datevalue from a'

UpdateSQL1.InsertSQL.Text := 'insert into a (id, datevalue) values
(:id,:datevalue)'

Query1.AplayUpdates works fine when UpdateObject is not assigned, but raises
the EBDEngineError Operation not applicable' when I assign UpdateSql1 as
UpdateObject

TIA

Denis Kolman

How to repeat:
Just write an sql insert statement that includes datetime fields and try to execute. For instance Query1.SQL.Text='insert into a (a_number,a_datetime)values (:a_number,:a_datetime)' and assign values to Query1.Params for instance (1,date()) and execute. 

Suggested fix:
Try to include a database variable that sets the default datetime format.

Sorry, I forgot to write that I'm using Delphi 6 Enterprise with SP2

I did some tests with Builder C++ 4.0 Pro using the data access controls
mentioned without to reproduce the issue. However will be best if you
send for us a small project test case for to try to reproduce it.

You can send it (into a zipped file and making reference to this bug
number) at:

ftp://support.mysql.com/pub/mysql/secret/

Thanks in advance.

i'm using delphi 5, mysql 4.1.10a
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'm using delphi 5 and suggested Tformatstring is not defined in this version. i
cant solve problem.
i used it with other mysql 4 versions. there is no difference.

this is an example:

        qryMesaj.Close;
        qryMesaj.SQL.Clear;
        qryMesaj.SQL.Add('SELECT * FROM Messages');
        qryMesaj.SQL.Add('WHERE Date <=:QDate');
        qryMesaj.ParamByName('QDate').AsDateTime := Date; //--> error here
        qryMesaj.Open;

same query working good with mssql.

i'm reading about myodbc & datetime parameter problem on forums a lot but there
is not a real solution.

i forgot to tell myodbc version. 3.51.11.00. i think above codes is enough to reproduce the error. if not i can try to send a little test program to reproduce error... ftp://support.mysql.com/pub/mysql/secret/

(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,[rfReplaceAll]),'(','',[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.

Solution for that problem is to force MySQL ODBC driver to present ODBC version 3 interface to apps no matter which they wanted.

It can be done by loading c:\windows\system32\myodbc3.dll into hex editor and replacing string :

75 10 FF 75 08 89 38 E8 2B FF FF FF 59 E9 93 00 00 00

with new string :

8B 7D 08 C7 07 03 00 00 00 33 FF 90 90 90 90 90 90 90

Hope, that will work for you (it works for me :)

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/