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
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