MySQL Bugs: #50180: TinyInt(1) causes error with client cursor but not with server cursor

Bug #50180	TinyInt(1) causes error with client cursor but not with server cursor
Submitted:	8 Jan 2010 10:56	Modified:	19 Oct 2012 10:32
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	adUseClient, adUseServer, Automation type not supported in VBScript, boolean, TinyInt(1), type mismatch

Description:
An Access database was converted to MySQL using the migration kit. As part of the
migration, Boolean fields were converted to TinyInt(1) with values of -1 and 0.

ASP page obtains recordset (rs) using CursorLocation = adUseServer.

When testing value of boolean fields (e.g. if rs("Bool1").value = true then DoSomething),
code runs fine.

Typename(rs("Bool1").value) & ", " & Vartype(rs("Bool1").value) returns Integer, 2

If the CursorLocation = adUseClient, testing value of boolean fields now causes data type
mismatch error:

Microsoft VBScript runtime  error '800a000d'
Type mismatch 

Typename(rs("Bool1").value) & ", " & Vartype(rs("Bool1").value) fails with:

Microsoft VBScript runtime  error '800a01ca'
Variable uses an Automation type not supported in VBScript: 'typename' 

This is the case with both the following MySQL drivers:
DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306; DATABASE=x; USER=x;
PASSWORD=x; OPTION=4;

DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; PORT=3306; DATABASE=x; USER=x;
PASSWORD=x; OPTION=4;

How to repeat:
Create a table (e.g. TempTable) with a TinyInt(1) field (e.g. Bool1).

Run the following ASP code:

<%
    Dim oConn
    Dim rs
    Dim MySQLConnStr
    Dim SQLStr

    MySQLConnStr = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; PORT=3306;
DATABASE=x; USER=x; PASSWORD=x; OPTION=4;"
    
    set oConn = Server.CreateObject ("ADODB.Connection")
    set rs = Server.CreateObject ("ADODB.Recordset")
    oConn.CursorLocation = 2 ' adUseServer
    oConn.Open(MySQLConnStr)

    SQLStr = "SELECT Bool1 FROM TempTable"
    rs.open SQLStr, oConn, 3, 1

    if rs("Bool1").value = true then response.write "Server Cursor: Bool1 = true<br />"
    if rs("Bool1").value = false then response.write "Server Cursor: Bool1 = false<br
/>"

    rs.close

    if oConn.State = 1 then oConn.close
    set rs = nothing
    set dbCommand = nothing
    set oconn = nothing

%>

Then try oConn.CursorLocation = 3 ' adUseClient

Suggested fix:
Client cursor should evaluate TinyInt(1) in the same way as Server cursor.

Verified with MySQL Connector/ODBC 5.1.6.

iguess the problem is in the  comparition of boolean and numeric values.
both false and true eual -1

try:
select (false=-1) as feqm1, true =-1 as teqm1, true=0 as teqz, false=0 as feqz,null=0 as neqz, null=-1 as neqm1,null=false as neqf, null=true as neqt

In response to Shimon Doodkin, the Bool1 field of the TempTable table does not contain true and false.  It actually contains -1 and 0.  TinyInts are numbers and not boolean data types.

As a result, when we compare the -1 and 0 returned from TempTable (CursorLocation set to adUserServer) with the VBScript true and false (which evaluate to -1 and 0 respectively), we have no problems.

The problem only arises when the CursorLocation is set to adUseClient.  When this happens, the driver is affecting the data type of the TinyInt so it returns a Type mismatch when compared to VBScript true and false.

The choice of cursor location shouldn't affect the data types of the variables returned.

ODBC Trace for the case with the error

Attachment: SQL_TEST_VB_ERROR.LOG (application/octet-stream, text), 62.02 KiB.

ODBC Trace for the case with no error

Attachment: SQL_TEST_VB_NO_ERROR.LOG (application/octet-stream, text), 62.02 KiB.

To make the comparison of two logs easier I replaced the buffers and handlers addresses by 0x00000000.
All ODBC functions return identical results except for this call (-11048):

wscript.exe" bu 0000-0000	EXIT  SQLColAttributesW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x00000000
		UWORD                        1 
		UWORD                       10 <SQL_DESC_UPDATABLE>
		PTR                0x00000000
		SWORD                        0 
		SWORD *             0x00000000 (-11048)
		SQLLEN *            0x00000000 (2)

However, the difference is not relevant because the requested attribute is numeric and the output buffer for the string length has not been modified.

So, it must be something in ADODB internals because from ODBC point of view the driver returns identical data and metadata.

Conclusion: it can be a bug, but not in MySQL connector/ODBC.

Thanks for following this up.  I doubt if the ADODB issue will ever be analysed or fixed so we'll live with the issue.