MySQL Bugs: #18084: DateTime parameter substitution does not work in Delphi

Bug #18084	DateTime parameter substitution does not work in Delphi
Submitted:	8 Mar 2006 19:08	Modified:	26 Dec 2006 16:47
Reporter:	Nathaniel R Bronson III	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.12.00	OS:	Windows (Windows XP SP2)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
Problem occurs with Delphi 7, Delphi 2005, Delphi 2006.

Problem occurs with latest MyODBC driver, 3.51.12.00.

Problem only occurs on Windows XP. It is interesting to note that the problem does NOT occur on Windows 2000.

This problem DOES appear to have been reported, but earlier reports have gone inactive. Issue numbers 1026 and 10518 both describe this problem, here is a brief description wth some additional information:

When writing a query which includes parameter substitution, one cannot successfully use a datetime parameter. Any atempt to substitute a datetime parameter will result in a message 'Operation not applicable'.

I have created ODBC trace logs. I was hoping that the specific communication between BDE (a database layer in Delphi) and ODBC could be trapped and would illuminate the problem. It may be interesting to note that when the program tries to execute the step to substitute the parameter that nothing happens in the trace logs. Nothing is sent to ODBC. I would guess that when BDE establishes a connection with ODBC, BDE is collecting information from the driver about what is supported and it determines that this type of field is not supported.

I can provide odbc trace logs of a successful program using an earlier 2.50.x driver if needed. I can also provide trace logs using the 3.51.12 driver.

It would be relatively easy to provide a setup.exe program which could install a simple program that would reproduce this problem if that is needed.

How to repeat:
Let us assume we have the following table:

CREATE TABLE `MyLog` (
`LogID` int(10) unsigned NOT NULL auto_increment,
`LogDate` datetime default NULL,
`Comment` varchar(255) default NULL,
PRIMARY KEY (`LogID`)
) TYPE=MyISAM;

In delphi, one can use a query object and syntax as follows:

MyQuery.Close;
MyQuery.Clear;
MyQuery.SQL.Add('select * from MyLog where LogDate = :MyDate');
MyQuery.ParamByName('MyDate').AsDateTime := SomeDate;
MyQuery.Open;

Any other type of parameter substitution (as in the ParamByName method above) will work. However, the use of DateTime fails with the 'Operation not applicable' message.

Suggested fix:
This is a big problem for us, we have a reasonably large legacy application that is currently compatible with MySQL 4.x using the older 2.50 driver, but we have a need to move to MySQL 5 and the 2.50 driver does not fully support the new features we need in MySQL 5.

This is not a problem on Windows 2000, but is a problem on Windows XP SP2.

Any help to resolve this would be greatly appreciated.

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

This is not a bug.
Written as presented in report, code does not get to pass through Delphi parser (see //MARKER below, that's where his code breaks), i.e. execution is breaked before it actually reaches ODBC driver, or even Query.Open command. This is because, I presume, you're used to BDE, and Delphi substitutes BDE query params on the fly.
In such cases it is best to declare param before execution of the query as follows:

procedure TForm1.Button2Click(Sender: TObject);
begin
//Make a table
SQLConnection1.ExecuteDirect('CREATE TABLE IF NOT EXISTS `MyLog` ('+
'`LogID` int(10) unsigned NOT NULL auto_increment,'+
'`LogDate` datetime default NULL,'+
'`Comment` varchar(255) default NULL,'+
'PRIMARY KEY (`LogID`)'+
') TYPE=MyISAM;');

//Prepare query
SQLQuery2.Close;
SQLQuery2.Sql.Clear;
SQLQuery2.Params.Clear;

// PREPARE PARAMS ////////
SQLQuery2.Params.Add;
SQLQuery2.Params[0].DataType := ftDateTime;
SQLQuery2.Params[0].Name := 'MyDate';
SQLQuery2.Params[0].ParamType := ptInput;
// END OF PREPARE PARAMS SECTION

//Now it will run
SQLQuery2.SQL.Add('select * from MyLog where LogDate = :MyDate');
//MARKER
SQLQuery2.ParamByName('MyDate').AsDateTime := Now;
SQLQuery2.Open;

end;

This is a bug, because it works with the 2.x myODBC driver just fine.
(It also works with Oracle, Postgres, ms/sql, and interbase)

It is true that the line of code that is complaining is not making an ODBC call, but I suspect the problem is related to a previous ODBC call.

Changing status to "Verified".

I'm having the same problem.   Any progress on this?  or an estimated date?

Hi there,

It is a problem caused by what Mysql wants in the mean of Date format. And if your regional settings and Mysql wants not match you have that error message.
For example MYsql wants 'yyyy-mm-dd' and your regional setting is 'mm-dd-yyyy'
u got this error. 

Solution:

procedure TForm1.Button1Click(Sender: TObject);
var
Fd     : TFormatSettings;//date format
begin

    GetLocaleFormatSettings(GetThreadLocale,Fd);
    Fd.ShortDateFormat := 'yyyy-mm-dd';
    query1.Close;
    query1.SQL.Clear;
    query1.SQL.Add('Select * from instant');
    query1.SQL.Add('where tarih > '+ QuotedStr(DateToStr(datetimepicker1.Date,Fd)));
    query1.Open;
end;

that is all.

The most recent proposed work-around does NOT match our problem: Datetime substitution using TParams in BDE causes a runtime error in the 3.x ODBC driver, but works properly in the 2.x ODBC driver.

The person responding should have been able to read/run the attached example and see what our problem actually is. In fact they can run the example, and see the failure.

The problem is related to wrong env. settings in BDE. It sets SQL_ATTR_ODBC_VERSION attribute to SQL_OV_ODBC2. However, it does not follow the data mapping for ODBC standard version 2. 
For instance: in spite the fact of using ODBC 2 the client application calls SQLSetEnvAttr with SQL_TYPE_TIMESTAMP (93) attribute that exists only for ODBC 3. In ODBC 2 it hasn't been defined at all. There is another parameter SQL_TIMESTAMP (11) which should be used instead of SQL_TYPE_TIMESTAMP. As a consequence of such mix of standards, MyODBC driver returns empty result set accordingly to ODBC 2. If explicitly set the ODBC version to 3 everything works well.
As this is not a fault of MyODBC driver the status of this report is being changed to "NOT A BUG"

Sorry, in the previous message SQLSetEnfAttr should be replaced by SQLGetTypeInfo

Ok, you write - it is "Not a Bug"
But what can do Delphi programmers who work with MySQL with ODBC? :(
You write - it is the BDE error. But last version of BDE was in 2001 (Borland will not change BDE).

Yes, programmers can change all:
Query1.ParamByName('Date').asDateTime:=Now;
to
Query1.ParamByName('Date').asString:=FormatFloat('yyyy-mm-dd hh:nn:ss',Now);
may by it is NOT normal way!

I try to create new server for my old database. It normaly worked with MyODBC 2.5 , but I want to instal MySQL5 with MyODBC 3.5 support. It not posible to change all Params from DataTime to String. In my program more then 6000 datetime params (10 years ago this project was writed for MS SQL Server , and 5 years ago it without problem changed from MS-SQL to MySQL3).

May be you can write special ODBC driver for BDE users?
Or may be anybody know what can do programmer if he must use .asDateTime params at standart TQuery components in his project?

You know reason why BDE not work with MyODBC 3.5 (but BDE normaly work with MyODBC 2.5) it meens you can create the exception for BDE's DateTime field... :)

Let me say I love Borland tools and have used it for years now.

> But what can do Delphi programmers who work with MySQL with ODBC? :(
This is, by my opinion, grave issue for us. Hope we'll find solution soon.

> You write - it is the BDE error. But last version of BDE was in 2001.
I know BDE is long dead but I realy don't know what would you have us do about it? Reverse engeneer BDE or "corrupt" MyODBC code so that it works with something abandoned?

> Yes, programmers can change all:
Yes they may.

>It normaly worked with MyODBC 2.5
MyODBC 2.5 didn't follow ODBC specs thus is abandoned (like BDE). It's no wonder they work together.

> May be you can write special ODBC driver for BDE users?
My proposition would be to write dbExpress driver. Available ones, ah hmm, don't work that good...

> You know reason why BDE not work with MyODBC 3.5 (but BDE normaly work with
MyODBC 2.5) it meens you can create the exception for BDE's DateTime field... :)
No we can't, see Bogdan's explanation.

Thanks for your interest in MySQL.

While I appreciate the stance that this is not a bug in MyODBC, many people would be made very happy if a real work-around could be found.

Would it not be possible to add an option to the ODBC driver that will allow this to work? If the only problem is that ONE value is incorrectly used by the BDE then surely it would not be too terrible to add an option to the effect of "allow ODBC 3 timestamp type in ODBC 2 (fix BDE bug)", or something like that? All it would do is "remap" that one ODBC 3 constant to the ODBC 2 one if the option is chosen. 

There are already a large number of checkbox options on the MyODBC DSN properties page. Would adding one more option and a few checks in the driver be hard?

As crummy and old as the BDE is, it is something that many people are stuck using for some time yet. I am in the same situation and have encountered this problem. All of us would REALLY appreciate a workaround.

Hi Jerome.

I understand BDE very well, been using it since Delphi 1.0a and Paradox don't_know_which! It has never worked properly, let me just mention locking (damn .net file) and concurrent use, transactions ... so this is just another of BDE bugs. Unfortunately, this problem is not as easy as it looks. I'll take a chance that I might be wrong here and say that if client represents itself as being ODBC API v2 compliant driver takes entirely different execution path! ODBC API forces driver to be backwards compatible, not forward. And that's exactly what BDE does, presents itself as v2 and calls v3 functions! Totally unacceptable. If it were the other way around, MyODBC would have to support it.
So, for us to "fix" this would mean a lot of work *outside* guidelines and specs we should follow (probably introducing new problems with properly written clients)! I think you're asking too much.
By my opinion, the proper way for everybody to go would be dbExpress driver as dbExpress is announced as BDE substitute... Unfortunately MySQL doesn't provide one but I've heard there are some good ones, although not for free.

However, I'll forward your discussion to others to see if we can come up with something.

Thanks for your interest in MySQL!

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 :)

Michal, thanks for your input I'm sure it will be helpful to someone. But the real question here is where is the point where we have to draw a line and say this client is just not good enough? I love Delphi and I argued we should fix this BDE misbehavior but I was outnumbered and without any good argument in any case.

Again, have a nice holidays and thanks for your interest in MySQL!

Hi folks,

After reading all the comments regarding this bug I have to add the following:

- I agree that this is BDE originated bug, but is still a bug.
- The statements with datetime parameter DO work with version 3.51.02. When I switched from 2.50.x to 3.51.x I had the same problem and reported the bug. I don't remember the name of the person who fixed it, but definitively worked in 3.51.02, anyone can try but only with servers 3.23.x or 4.0.x
- I think that it could be a good idea to check why 3.51.02 works and what are the code differences between the current version and 3.51.02. 

Those were my two cents....
DiegoL

Diego, 3.51.02 is a very old version.
Version 3.51.11 worked correctly. This bug wasn't reproduced.

http://downloads.mysql.com/archives/mysql-connector-odbc-3.51/MyODBC-3.51.11-1-win.exe

Igor, thanks for sharing info with others.

Diego; it is hard enough to follow Microsoft "specs" (yes with ") let alone fix stuff in numerous different clients... Connectors team must take in account fixes made because of MySQL server too and remove them from build after they are fixed in proper place. Now, you are asking us to do tips & tricks in MyODBC while Code Gear does nothing in BDE? I don't find bugsdb to be a proper place for this discussion. Maybe some CodeGear forum?

As I have dealings with CodeGear, and respect Delphi above all other dev tools, I can say that BDE is obsolete and abandoned. So please move on, it was not a good thing to work with since the start (I used BDE since before Delphi, with Paradox).