MySQL Bugs: #27051: ODBC Connector returns date columns as and array of bytes to ASP

Bug #27051	ODBC Connector returns date columns as and array of bytes to ASP
Submitted:	12 Mar 2007 15:16	Modified:	27 Jul 2007 8:51
Reporter:	Filip Norrgård	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.14	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	ASP, date, ODBC, windows

Description:
The latest MySQL ODBC Connector seems to return the DATETIME columns as an array of bytes variable to an ASP (not ASP.NET!) web page, as compared to the expected date variable which the previous version did manage to return. 

The MySQL server is the community edition and version 4.1.21 running on a Windows 2003 Server and the script has been tested and concluded not to be working with the latest ODBC Connector on both Windows 2003 Server and Windows XP Pro (all with the latest updates installed).

Tested to see which type of variables the ODBC driver returned to the ASP script using VarType (http://www.w3schools.com/vbscript/func_vartype.asp) and TypeName (http://www.w3schools.com/vbscript/func_typename.asp) and found out that the variable type is incorrect to the expected result. The same code is in the "how to repead" field for your testing.

How to repeat:
Copy the code below and save as an ASP file (file extension is ".asp") and run in IIS with the MySQL ODBC connector installed. Uncommenting the commented line on line 30 will render an type mismatch in 3.51.14 version of the ODBC connector but not in previous versions of the driver.
The "expire" column in the "subscription" table should be a column set to DATETIME and allow NULLs to exist. 

======= Copy below me! ========

<html>
<head>
<title>Test Page</title>
</script>
</head>
<body bgcolor="#FFFFFF">
<%
						
Set db = Server.CreateObject("ADODB.Connection")
db.Open "Driver={MySQL ODBC 3.51 Driver};" & _ 
"Server=127.0.0.1;" & _
"Database=testservice;" & _
"Uid=root;" & _
"Pwd=abc123;" & _
"OPTION=16386;"

rsSql="SELECT ifnull(expire,'') as myexpire FROM subscription"

set rs = db.Execute (rsSql)

if rs.eof=true then
	Response.Write "Nothing here!"
end if
do while rs.eof=false 

		Response.Write("== " &  VarType(rs("myexpire")) & " ==")
		
		Response.Write(" Type " & TypeName(rs("myexpire")))
		
		'This causes type mismatches in ASP 
		'Response.Write(rs("myexpire"))

	rs.movenext
loop
rs.Close
set rs = nothing
db.Close
set db=nothing%> 

</body>
</html>

I did some tests in C# Windows Applications using Visual Studio 2005 and I get the array of bytes sometimes too.
Also, if I select ONLY one field and the field is DATE (not DATETIME), then the month/day/year values are wrong.  Adding in some other non-DATE field in the select corrects the problem.

Nevermind about the DATE field being wrong.  That was an old MySQL server problem.  The array of bytes is still an issue, though.

Experiencing this same issue using ASP 3.0 with any fields that use encoded strings.

Hi Filip and thanks for your report. I boiled down this test case to VBS script (my table has DATE column and it's not empty):
Set db = CreateObject("ADODB.Connection")
db.Open "Driver={MySQL ODBC 3.51 Driver};" & _ 
"Server=127.0.0.1;" & _
"Database=solusd;" & _
"Uid=root;" & _
"Pwd=;" & _
"OPTION=16386;"

'rsSql="SELECT DISTINCT Datum1 as myexpire FROM dnevnik WHERE Godina = 2005"
rsSql="SELECT ifnull(Datum1,'') as myexpire FROM dnevnik WHERE Godina = 2005"
set rs = db.Execute (rsSql)
WScript.Echo("== " &  VarType(rs("myexpire")) & " ==")
WScript.Echo(" Type " & TypeName(rs("myexpire")))
'This causes type mismatches in ASP 
WScript.Echo(rs(0))
rs.Close
set rs = nothing
db.Close
set db=nothing

Environment:
 - MySQL server 5.0.38BK on WinXP Pro SP2 localhost
 - MyODBC 3.51.14 GA

without "ifnull"
[ENTER][SQLDescribeCol][.\results.c][235]
|   [INFO][SQLDescribeCol][.\results.c][261]col: 1
|   [INFO][SQLDescribeCol][.\results.c][262]type: 9
|   [INFO][SQLDescribeCol][.\results.c][263]precision: 10
|   [INFO][SQLDescribeCol][.\results.c][264]decimals: 0

Field   1:  `myexpire`
Catalog:    `def`
Database:   `solusd`
Table:      `dnevnik`
Org_table:  `dnevnik`
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   0
Flags:      BINARY

with "ifnull"
ENTER][SQLDescribeCol][.\results.c][235]
|   [INFO][SQLDescribeCol][.\results.c][261]col: 1
|   [INFO][SQLDescribeCol][.\results.c][262]type: -4
|   [INFO][SQLDescribeCol][.\results.c][263]precision: 16777215
|   [INFO][SQLDescribeCol][.\results.c][264]decimals: 31

Field   1:  `myexpire`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      NOT_NULL BINARY

It is the server that returns different types in this case (not DATE but VAR_STRING and packed (Flags: BINARY, +1 for Greg)) causing the error in comparison later. This behavior is described in manual under "IFNULL" function:

IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used. The default result value of IFNULL(expr1,expr2)  is the more “general” of the two expressions, ...

You will have to find some other way of writing that query if you want DATE type returned for further processing.

I think this problem is described in Bug#10491 (in general):
"How can connector distinguish between "SHOW CREATE TABLE", where it should treat BINARY as UTF8 and "SELECT varbinary_col FROM some_table", where it really should be binary."

I will put this report in "Verified" state to see what others think of it. After all, script returns "" as a result of query with IFNULL which should not occur.

MyODBC 3.51.14 logs

Attachment: sql-log.zip (application/x-zip-compressed, text), 6.66 KiB.

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/

Explanation: I retested with MySQL server 5.0.44BK and MyODBC 3.51.17GA on WinXP Pro SP2. The problem no longer exists. My test case returns 8 (= vbString - Indicates a string) which is fully correct and in accordance with IFNULL behavior described in manual.

Following on my remarks above I don't think this will ever be resolved in c/ODBC due to metadata returned by server:
mysql> SELECT ifnull(Datum1,'') as myexpire FROM dnevnik WHERE Godina = 2005 LIM
IT 1;
Field   1:  `myexpire`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      NOT_NULL BINARY
---
SQLExecDirect:
				In:				hstmt = 0x00852098, 
										szSqlStr = "SELECT ifnull(Datum1,'') AS myexpire FROM solusd.dnevn...", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"myexpire"
0x2005-01-03
1 row fetched from 1 column.

thus your dates are returned as 0x2005-12-12 (for example). To overcome this problem please use CAST to a specific type of your choice:
mysql> SELECT CAST(ifnull(Datum1,'') as DATE) AS myexpire  FROM solusd.dnevnik W
HERE Godina = 2005 LIMIT 1;
Field   1:  `myexpire`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      BINARY
---
SQLExecDirect:
				In:				hstmt = 0x00852098, 
										szSqlStr = "SELECT CAST(ifnull(Datum1,'') AS DATE)  AS myexpire FR...", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"myexpire"
2005-01-03
1 row fetched from 1 column.

As you can see this corrects the problem.