Bug #41081 MyODBC Connector Converts Decimal Null to Zero
Submitted: 27 Nov 2008 20:25 Modified: 4 Aug 2009 13:31
Reporter: Graham White Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1 OS:Microsoft Windows (Server 2003)
Assigned to: Jess Balint
Tags: connector, decimal, MyODBC, windows
Triage: D2 (Serious)

[27 Nov 2008 20:25] Graham White
Description:
I recently upgraded a classic ASP application to new hardware. The server is running Windows Server 2003 Standard (IIS 6). The version of MySQL i am connecting to is v 5.0.67-community. I am using a DSN-less connection, here is my string:
Driver={MySQL ODBC 5.1 Driver}; Server=x; Port=3306; Option=4; Socket=; Stmt=; Database=x; Uid=x; Pwd=x;

Driver is 5.01.04 in the ODBC Data Source Administrator.

In my database, i have a table that is used to store dollar amounts. when i first designed the table, i researched the documentation and found that it was recommended to store dollar figures as decimals in MySQL. This table in particular uses nulls in the amount field in order to indicate that an amount has not yet been entered (as opposed to 0, which is a legitimate entry).

The problem is, when i select a row from the table, the null value in the amount field gets converted to a zero value, as opposed to returning a null. In MySQL command line interface (and the GUI), the value is correctly returned as null.

Further, if i change the connection string to downgrade to the 3.51 driver, the value is correctly returned to my application as a null.

i was confused where this zero was coming from, so i tried a TypeName on the variable i was setting it to and got this error:
Variable uses an Automation type not supported in VBScript: 'TypeName'

Suggestions welcome...

How to repeat:
CREATE TABLE `test` (
  `id` int(10) NOT NULL auto_increment,
  `tblInt` int(10) default NULL,
  `tblDbl` double default NULL,
  `tblText` text,
  `tblDateTime` datetime default NULL,
  `tblDec` decimal(10,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
insert into test values (1,null, null, null, null, null )

now in asp:

Set oc = Server.CreateObject("ADODB.Connection")  
oc.Open "Driver={MySQL ODBC 5.1 Driver}; Server=localhost; Port=3306; Option=4; Socket=; Stmt=; Database=test; Uid=test; Pwd=test;"
oc.CursorLocation = 3
strSQL = "SELECT tblInt FROM test"
Set rs = oc.execute( strSQL  )
Do While Not rs.EOF
    strField = rs(0)
    response.write( "TypeName: " & TypeName(strField) & " = " )
    If IsNull(strField) Then
        response.write( "i am null" )
    else
        response.write( rs(0) )
    End If
    response.write vbCrLf 
    rs.MoveNext
Loop
Set rs = nothing

response.write vbCrLf 

strSQL = "SELECT tblDbl FROM test"
Set rs = oc.execute( strSQL  )
Do While Not rs.EOF
    strField = rs(0)
    response.write( "TypeName: " & TypeName(strField) & " = " )
    If IsNull(strField) Then
        response.write( "i am null" )
    else
        response.write( rs(0) )
    End If
    response.write vbCrLf 
    rs.MoveNext
Loop
Set rs = nothing

response.write vbCrLf 

strSQL = "SELECT tblDec FROM test"
Set rs = oc.execute( strSQL  )
Do While Not rs.EOF
    strField = rs(0)
    'response.write( "TypeName: " & TypeName(strField) & " = " ) ' NOTE THIS LINE THROWS AN ERROR: Brief Description: Variable uses an Automation type not supported in VBScript: 'TypeName'
    If IsNull(strField) Then
        response.write( "i am null" )
    else
        response.write( rs(0) )
    End If
    response.write vbCrLf 
    rs.MoveNext
Loop
Set rs = nothing

response.write vbCrLf 

strSQL = "SELECT tblText FROM test"
Set rs = oc.execute( strSQL  )
Do While Not rs.EOF
    strField = rs(0)
    response.write( "TypeName: " & TypeName(strField) & " = " )
    If IsNull(strField) Then
        response.write( "i am null" )
    else
        response.write( rs(0) )
    End If
    response.write vbCrLf 
    rs.MoveNext
Loop
Set rs = nothing

response.write vbCrLf 

strSQL = "SELECT tblDateTime FROM test"
Set rs = oc.execute( strSQL  )
Do While Not rs.EOF
    strField = rs(0)
    response.write( "TypeName: " & TypeName(strField) & " = " )
    If IsNull(strField) Then
        response.write( "i am null" )
    else
        response.write( rs(0) )
    End If
    response.write vbCrLf 
    rs.MoveNext
Loop
Set rs = nothing

response.write vbCrLf
[27 Nov 2008 20:27] Graham White
NOTE the output of the code above is:

TypeName: Null = i am null

TypeName: Null = i am null

0

TypeName: Null = i am null

TypeName: Null = i am null
[8 Dec 2008 21:28] Jess Balint
patch + test

Attachment: bug41081.diff (application/octet-stream, text), 2.08 KiB.

[8 Dec 2008 21:31] Jess Balint
ADO was setting SQL_DESC_PRECISION after SQLBindCol() and then SQL_DESC_DATA_PTR again. We were erroneously clearing SQL_DESC_OCTET_LENGTH_PTR and SQL_DESC_INDICATOR_PTR which robbed ADO from knowing it was NULL.

SQLSetDescField():

If the application changes the data type or attributes after setting the SQL_DESC_DATA_PTR field, the driver sets SQL_DESC_DATA_PTR to a null pointer, unbinding the record.
[29 Apr 2009 15:32] Matteo Canella
How can I apply this patch?
[8 May 2009 20:56] x x
Yes, is the patch available?
[2 Aug 2009 22:56] Jess Balint
This will be released in 5.1.6

The patch is available for testing in nightly builds:
http://downloads.mysql.com/snapshots.php
[4 Aug 2009 13:31] Tony Bedford
An entry was added to the 5.1.6 changelog:

When a column of type DECIMAL containing NULL was accessed, MySQL Connector/ODBC returned a 0 rather than a NULL.