MySQL Bugs: #37342: ODBC TIMESTAMP string format not handled properly by ODBC driver

Bug #37342	ODBC TIMESTAMP string format not handled properly by ODBC driver
Submitted:	11 Jun 2008 13:25	Modified:	8 Jul 2008 9:54
Reporter:	Anders Karlsson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.1.4r1107	OS:	Any
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
When passing a TIMESTAMP or DATE to MyODBC, in the ODBC format: {d <date>} or {ts <tiemstamp>}, this is not handled properly by the MySQL ODBC driver. The issue is that the string that this represents is copied once into the SQL statement, and then it is added again, as an escaped string.

How to repeat:
Pass a bind variable in the {ts <datetime>} parameter to SQLBindParameter and then execute the statement. The parameter needs to be set as one of the TIMESTAMP datatypes for this to happen.

Suggested fix:
The issue is in the file execute.c on line 500 to 512. Instead of skipping handling as a string, the case statement breaks, which will mean that the next statement will the one just after the switch, which will again add the string to the statement on line 591 to 596. The fix is to, in the case when handling TIMESTAMP etc datatypes, skipping the following string processing.
So change this:
execute.c line 500:
if (data[0] == '{')       /* Of type {d date } */
{
  to= add_to_buffer(net, to, data, length);
  break;
}
/* else treat as a string */

to this:
execute.c line 500:
if (data[0] == '{')       /* Of type {d date } */
{
  to= add_to_buffer(net, to, data, length);
  goto out;
}
/* else treat as a string */

Hi Anders and thanks for your report.

When using date constants in SQL queries, the {d 'date value'} syntax is no longer needed for date comparisons. For example, the following query

  SELECT *
  FROM   invoice
  WHERE  invoice_date > {d '2008-06-01'}

can now be written as

  SELECT *
  FROM   invoice
  WHERE  invoice_date > '2008-06-01'

The main point being that ODBC standard changed in that way that it now allows dates and times SQL server can understand to be passed in form server understands (see http://msdn.microsoft.com/en-us/library/ms180878.aspx for example) thus your request seems obsolete to me.
Anyway, if you would like, change severity to S4 "Feature request" and I can set it to verified then.

Tonci, this happens when {dt '...'} is used as a parameter to a statement, not directly within the statement. It's a real bug.

Fix problems with SQL_TYPE_TIME and SQL_TYPE_TIMESTAMP parameters

Attachment: bug37342.patch (text/plain), 3.46 KiB.

Thanks Jim.

Patch committed as rev 1119 and will be released in 5.1.5.

An entry has been added to the 5.1.5 Changelog:

ODBC TIMESTAMP string format is not handled properly by the MyODBC driver. When passing a TIMESTAMP or DATE to MyODBC, in the ODBC format: {d <date>} or {ts <timestamp>}, the string that represents this is copied once into the SQL statement, and then added again, as an escaped string.