Description:
According to the manual, "Before server 5.0.3, BIT is a synonym for TINYINT(1)." In this case adBoolean was a sensible mapping in ADO. However, currently the BIT type is available up to sizes of BIT(64).
As it is today MyODBC 3, and 5 handle the case of a BIT field larger than 1 differently. With the test code below, a value of b'11' results in FALSE for version 3.51 and TRUE for 5.0.
Ultimately the way this situation should be handled is that BIT(1) fields are mapped to adBoolean, and anything >1 should be mapped to something more appropriate to the type. I believe this is the way J does it.
How to repeat:
mysql> CREATE TABLE bit (C1 INT PRIMARY KEY, C2 BIT(2));
mysql> INSERT INTO BIT VALUES (1, b'11');
VB code:
Sub BitTest()
On Error GoTo EH
Dim strSQL As String
Dim strConn As String
Dim connection1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim fld As Field
Dim fldName As String
Dim lngType As Long
Set connection1 = New ADODB.Connection
Set recset1 = New ADODB.Recordset
strSQL = "SELECT * FROM bit"
strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;DATABASE=ado" & _
";USER=root;PASSWORD=mypass;OPTION=35;"
' strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
"SERVER=localhost;DATABASE=ado" & _
";UID=root;PWD=mypass;OPTION=3;"
' open connection
connection1.ConnectionString = strConn
connection1.CursorLocation = adUseServer
connection1.Open
' open recordset
recset1.Open strSQL, connection1, adOpenForwardOnly, _
adLockOptimistic, adCmdText
If Not recset1.EOF Then
recset1.MoveFirst
End If
If recset1.Supports(adUpdate) Then
While Not recset1.EOF
For Each fld In recset1.Fields
lngType = CLng(fld.Type)
fldName = fld.Name
If lngType = adBoolean Then
MsgBox "name: " & fld.Name & "::" & fld.Value
End If
Next
recset1.MoveNext
Wend
End If
recset1.Close
Set recset1 = Nothing
connection1.Close
Set connection1 = Nothing
Exit Sub
EH:
MsgBox Err.Number & ":" & Err.Description
MsgBox "failed on column " & fldName
End Sub
Description: According to the manual, "Before server 5.0.3, BIT is a synonym for TINYINT(1)." In this case adBoolean was a sensible mapping in ADO. However, currently the BIT type is available up to sizes of BIT(64). As it is today MyODBC 3, and 5 handle the case of a BIT field larger than 1 differently. With the test code below, a value of b'11' results in FALSE for version 3.51 and TRUE for 5.0. Ultimately the way this situation should be handled is that BIT(1) fields are mapped to adBoolean, and anything >1 should be mapped to something more appropriate to the type. I believe this is the way J does it. How to repeat: mysql> CREATE TABLE bit (C1 INT PRIMARY KEY, C2 BIT(2)); mysql> INSERT INTO BIT VALUES (1, b'11'); VB code: Sub BitTest() On Error GoTo EH Dim strSQL As String Dim strConn As String Dim connection1 As ADODB.Connection Dim recset1 As ADODB.Recordset Dim fld As Field Dim fldName As String Dim lngType As Long Set connection1 = New ADODB.Connection Set recset1 = New ADODB.Recordset strSQL = "SELECT * FROM bit" strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=localhost;DATABASE=ado" & _ ";USER=root;PASSWORD=mypass;OPTION=35;" ' strConn = "DRIVER={MySQL Connector/ODBC v5};" & _ "SERVER=localhost;DATABASE=ado" & _ ";UID=root;PWD=mypass;OPTION=3;" ' open connection connection1.ConnectionString = strConn connection1.CursorLocation = adUseServer connection1.Open ' open recordset recset1.Open strSQL, connection1, adOpenForwardOnly, _ adLockOptimistic, adCmdText If Not recset1.EOF Then recset1.MoveFirst End If If recset1.Supports(adUpdate) Then While Not recset1.EOF For Each fld In recset1.Fields lngType = CLng(fld.Type) fldName = fld.Name If lngType = adBoolean Then MsgBox "name: " & fld.Name & "::" & fld.Value End If Next recset1.MoveNext Wend End If recset1.Close Set recset1 = Nothing connection1.Close Set connection1 = Nothing Exit Sub EH: MsgBox Err.Number & ":" & Err.Description MsgBox "failed on column " & fldName End Sub