Bug #16917 MyODBC doesn't return ASCII 0 characters for TEXT columns
Submitted: 30 Jan 2006 22:21 Modified: 20 May 2007 6:30
Reporter: Bogdan Degtyariov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Any
Assigned to: Jim Winstead
Tags: SQLSetPos

[30 Jan 2006 22:21] Bogdan Degtyariov
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
[9 May 2007 22:54] Jim Winstead
patch to fix bug

Attachment: bug16917.patch (application/octet-stream, text), 2.84 KiB.

[9 May 2007 23:03] Jim Winstead
The problem is that in handling SQLSetPos(..., SQL_POSITION, ...), we call mysql_data_seek() and then use stmt->result->data_cursor->data to get the current values, instead of using a call to mysql_fetch_row(). This makes use get a NULL from mysql_fetch_lengths() and then we end up calling strlen().
[11 May 2007 17:11] Jim Winstead
Fixed for 3.51.16.
[20 May 2007 6:30] MC Brown
A note has been added to the 3.51.16 changelog.