Description:
MySQL Connector/ODBC can store ASCII 0 characters into the TEXT coulmns, but can't read them correctly.
How to repeat:
Run the following VB code:
Sub issue5183()
'VB6 SP4 , ADO 2.7
On Error GoTo TestMySQL_Error
Dim iFields As Integer
Dim strSQL As String, strConnect As String
Dim cnTest As ADODB.Connection
Dim rsTest As ADODB.Recordset
' 'CREATE DATABASE TEST' run from MySql client
strConnect = "Provider=MSDASQL;" & _
" DRIVER=MySQL ODBC 3.51 Driver;" & _
" SERVER=localhost;" & _
" DATABASE=test;" & _
" UID=root; PWD=;" & _
" OPTION=35"
'Open ADODB connection
Set cnTest = New ADODB.Connection
cnTest.Mode = adModeReadWrite
cnTest.CommandTimeout = 10000
cnTest.ConnectionTimeout = 20
cnTest.Open strConnect
'create table
strSQL = "`System ID` INT NOT NULL" & _
", `BLOB Data` BLOB NULL" & _
", `TEXT Data` MEDIUMTEXT NULL" & _
", `Character Data` VARCHAR(255) NULL"
strSQL = "CREATE TABLE `Test Table` (" & strSQL & ")"
cnTest.Execute strSQL
'------ These work for Chr$(1 to 255) -----------------
'add data to BLOB,MEDIUMTEXT,CHAR
strSQL = "INSERT INTO `Test Table` (`System ID`,`BLOB Data`,`TEXT Data`,`Character Data`)" & _
" VALUES (1, 'Test1\tData','Test2\tData','Test3\tData')"
cnTest.Execute strSQL
'------------------------------------------------------
'------ but don't work for Chr$(0) (see error below each) -----------------
'add data to BLOB,MEDIUMTEXT,CHAR
strSQL = "INSERT INTO `Test Table` (`System ID`,`BLOB Data`,`TEXT Data`,`Character Data`)" & _
" VALUES (2, 'Test1\0Data','Test2\0Data','Test3\0Data')"
cnTest.Execute strSQL
'------------------------------------------------------------------------
strSQL = "SELECT `System ID`,`BLOB Data`,hex(`BLOB Data`) As hBD,`TEXT Data`,hex(`TEXT Data`) AS hTD,`Character Data`,hex(`Character Data`) AS hCD FROM `Test Table`"
Set rsTest = New ADODB.Recordset
rsTest.Open strSQL, cnTest, adOpenStatic, adLockReadOnly
Do While Not rsTest.EOF
Debug.Print rsTest("System ID")
Debug.Print "LEN=", Len(rsTest("BLOB Data")), " > " & rsTest("BLOB Data") & "<>" & rsTest("hBD") & "<"
Debug.Print "LEN=", Len(rsTest("TEXT Data")), " > " & rsTest("TEXT Data") & "<>" & rsTest("hTD") & "<"
Debug.Print "LEN=", Len(rsTest("Character Data")), " > " & rsTest("Character Data") & "<>" & rsTest("hCD") & "<"
rsTest.MoveNext
Loop
rsTest.Close: Set rsTest = Nothing
cnTest.Close: Set cnTest = Nothing
'OUTPUT T e s t 1 \t D a t a
'==================================================
'\t
'BLOB >?????< >54 65 73 74 31 09 44 61 74 61<
'TEXT >Test2 Data<>54 65 73 74 32 09 44 61 74 61<
'Char >Test3 Data<>54 65 73 74 33 09 44 61 74 61<
'\0
'BLOB unprintable >54 65 73 74 31 00 44 61 74 61<
'TEXT >Test2< >54 65 73 74 32 00 44 61 74 61<
'Char >Test3 Data< >54 65 73 74 33 00 44 61 74 61<
Exit Sub
TestMySQL_Error:
Debug.Print Err.Number
Debug.Print Err.Source
Debug.Print Err.Description
Debug.Print strSQL
MsgBox "Error"
End
Resume
End Sub
Description: MySQL Connector/ODBC can store ASCII 0 characters into the TEXT coulmns, but can't read them correctly. How to repeat: Run the following VB code: Sub issue5183() 'VB6 SP4 , ADO 2.7 On Error GoTo TestMySQL_Error Dim iFields As Integer Dim strSQL As String, strConnect As String Dim cnTest As ADODB.Connection Dim rsTest As ADODB.Recordset ' 'CREATE DATABASE TEST' run from MySql client strConnect = "Provider=MSDASQL;" & _ " DRIVER=MySQL ODBC 3.51 Driver;" & _ " SERVER=localhost;" & _ " DATABASE=test;" & _ " UID=root; PWD=;" & _ " OPTION=35" 'Open ADODB connection Set cnTest = New ADODB.Connection cnTest.Mode = adModeReadWrite cnTest.CommandTimeout = 10000 cnTest.ConnectionTimeout = 20 cnTest.Open strConnect 'create table strSQL = "`System ID` INT NOT NULL" & _ ", `BLOB Data` BLOB NULL" & _ ", `TEXT Data` MEDIUMTEXT NULL" & _ ", `Character Data` VARCHAR(255) NULL" strSQL = "CREATE TABLE `Test Table` (" & strSQL & ")" cnTest.Execute strSQL '------ These work for Chr$(1 to 255) ----------------- 'add data to BLOB,MEDIUMTEXT,CHAR strSQL = "INSERT INTO `Test Table` (`System ID`,`BLOB Data`,`TEXT Data`,`Character Data`)" & _ " VALUES (1, 'Test1\tData','Test2\tData','Test3\tData')" cnTest.Execute strSQL '------------------------------------------------------ '------ but don't work for Chr$(0) (see error below each) ----------------- 'add data to BLOB,MEDIUMTEXT,CHAR strSQL = "INSERT INTO `Test Table` (`System ID`,`BLOB Data`,`TEXT Data`,`Character Data`)" & _ " VALUES (2, 'Test1\0Data','Test2\0Data','Test3\0Data')" cnTest.Execute strSQL '------------------------------------------------------------------------ strSQL = "SELECT `System ID`,`BLOB Data`,hex(`BLOB Data`) As hBD,`TEXT Data`,hex(`TEXT Data`) AS hTD,`Character Data`,hex(`Character Data`) AS hCD FROM `Test Table`" Set rsTest = New ADODB.Recordset rsTest.Open strSQL, cnTest, adOpenStatic, adLockReadOnly Do While Not rsTest.EOF Debug.Print rsTest("System ID") Debug.Print "LEN=", Len(rsTest("BLOB Data")), " > " & rsTest("BLOB Data") & "<>" & rsTest("hBD") & "<" Debug.Print "LEN=", Len(rsTest("TEXT Data")), " > " & rsTest("TEXT Data") & "<>" & rsTest("hTD") & "<" Debug.Print "LEN=", Len(rsTest("Character Data")), " > " & rsTest("Character Data") & "<>" & rsTest("hCD") & "<" rsTest.MoveNext Loop rsTest.Close: Set rsTest = Nothing cnTest.Close: Set cnTest = Nothing 'OUTPUT T e s t 1 \t D a t a '================================================== '\t 'BLOB >?????< >54 65 73 74 31 09 44 61 74 61< 'TEXT >Test2 Data<>54 65 73 74 32 09 44 61 74 61< 'Char >Test3 Data<>54 65 73 74 33 09 44 61 74 61< '\0 'BLOB unprintable >54 65 73 74 31 00 44 61 74 61< 'TEXT >Test2< >54 65 73 74 32 00 44 61 74 61< 'Char >Test3 Data< >54 65 73 74 33 00 44 61 74 61< Exit Sub TestMySQL_Error: Debug.Print Err.Number Debug.Print Err.Source Debug.Print Err.Description Debug.Print strSQL MsgBox "Error" End Resume End Sub