| Bug #30235 | Bad result expected in DATETIME Function with MyODBC | ||
|---|---|---|---|
| Submitted: | 3 Aug 2007 21:36 | Modified: | 4 Oct 2007 0:02 |
| Reporter: | DOUGLAS HERRERIAS | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.17, 3.51.19 | OS: | Windows |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
| Tags: | date, MyODBC | ||
[19 Aug 2007 14:39]
Tonci Grgin
Hello Douglas and thanks for your report.
Indeed there seems to be a problem with this, much like the one that made us release 3.51.19:
Server version: 5.0.48-pb1038-log MySQL Pushbuild Edition, build 1038
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field;
Field 1: `field`
Catalog: `def`
Database: ``
Table: ``
Org_table: ``
Type: STRING
Collation: binary (63)
Length: 29
Max_length: 19
Decimals: 31
Flags: BINARY
+---------------------+
| field |
+---------------------+
| 2007-08-03 17:32:00 |
+---------------------+
odbcte32.exe test:
Full Connect(Use Driver)
Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3
Successfully connected to DSN 'myodbc1cln'.
SQLExecDirect:
In: hstmt = 0x008B19C0, szSqlStr = "SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINU...", cbSqlStr = -3
Return: SQL_SUCCESS=0
Get Data All:
"field"
0x2007-08-03 17:32:00
1 row fetched from 1 column.
As you can see, the result is returned as "0x2007-08-03 17:32:00" so you should trim leading "0x" or explicitly cast value to something suitable until this is fixed.
Accompanying SQL log shows the problem:
odbcte32 111c-bb0 EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)
HSTMT 008B1C60
UWORD 1
UCHAR * 0x0009B9A0
SWORD 100
SWORD * 0x00000000
SWORD * 0x0009BA04 (-2) << SQL_BINARY
SQLULEN * 0x0009BA08 (58)
SWORD * 0x00000000
SWORD * 0x00000000
odbcte32 111c-bb0 ENTER SQLFetch
HSTMT 008B1C60
odbcte32 111c-bb0 EXIT SQLFetch with return code 0 (SQL_SUCCESS)
HSTMT 008B1C60
odbcte32 111c-bb0 ENTER SQLGetData
HSTMT 008B1C60
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x0009BDA8
SQLLEN 174
SQLLEN * 0x0006F5FC
odbcte32 111c-bb0 EXIT SQLGetData with return code 0 (SQL_SUCCESS)
HSTMT 008B1C60
UWORD 1
SWORD 1 <SQL_C_CHAR>
PTR 0x0009BDA8 [ 19] "2007-08-03 17:32:00"
SQLLEN 174
SQLLEN * 0x0006F5FC (19)
The SQL log is in accordance to metadata sent by server (field has Collation: binary (63) and FLAG: BINARY set) and on trail of bug#10491...
[19 Aug 2007 14:40]
Tonci Grgin
Adding 3.51.19 to version, raising severity to S2.
[4 Oct 2007 0:02]
Jim Winstead
This is not a bug in the ODBC driver, but a server limitation. The type returned by DATE_SUB() is a binary string if the first argument to DATE_SUB() is a string. You can avoid this by wrapping the first argument in the DATE() function, which will cause DATE_SUB() to return a DATETIME, and things will work correctly. I wasn't able to dig up the bug filed against the server for these issues, but opened a new bug (Bug #31384) for the specific problem of DATE_ADD() and DATE_SUB() returning an unhelpful data type.
Description: For example: SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field This should return: '2007-08-03 17:32:00' but returns: ????????? It happens for other MySQL date functions like DATE_ADD. I'm using MS Windows XP SP2, VB6 with ADO 2.8, MyODBC 3.51.17 Server: MySQL 5.0.45 for Linux In MyODBC version 3.51.12 this problem doesn't happen. How to repeat: Dim cnn As ADODB.Connection Dim Vcon as string Dim rs As ADODB.Recordset, vResult as string Vcon = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=192.168.0.1;" & _ "DATABASE=mydb;" & _ "USER=root;" & _ "PASSWORD=1234;" & _ "PORT=3306;" & _ "OPTION=" & 1 + 2 + 8 + 32 Set cnn = New ADODB.Connection cnn.Open Vcon Set rs = New ADODB.Recordset With rs .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .ActiveConnection = cnn .Source = "SELECT DATE_SUB('2007-08-03 17:33:00', INTERVAL 1 MINUTE) AS field" .Open End With vResult = rs("field").Value