MySQL Bugs: #783: ADO can't access BLOB field after upgrading to mySQL 4.1.0

Bug #783	ADO can't access BLOB field after upgrading to mySQL 4.1.0
Submitted:	2 Jul 2003 10:15	Modified:	28 Jul 2003 5:35
Reporter:	Stephan Goemans	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.0	OS:	Windows (Windows XP)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
After upgrading to mySQL 4.1.0 (from 3.23.52), my commercial Visual-Basic program fails when trying to access a BLOB-field. This is the code which fails:

Private Sub btnPicture_Click()
    Dim myStream As ADODB.Stream
    Set myStream = New ADODB.Stream
     
    myStream.Type = adTypeBinary
    myStream.Open
    myStream.Write rsPictures.Fields("M3PICTURE") '***********Runtime error 3001
    myStream.SaveToFile "temp.img", adSaveCreateOverWrite
    myStream.Close
    Set myStream = Nothing
End Sub

The ADO error 3001 says: "The Application is using arguments that are of the wrong type ,are out of acceptable range,or are in conflict with one another"

My connections string (which hasn't changed after updating to 4.1.0) is:
"driver={MySQL ODBC 3.51 Driver};server=EVO1005V;uid=root;pwd=myPassword;database=KME_ADM;option=16387"

My SQL command to create the "rsPictures" Recordset:
"SELECT M3ID, M3PICTURE FROM MODUL30 WHERE M3ID = " & picID & " ORDER BY M3ID"

ADO parameters:
adUseClient / adOpenStatic / adLockOptimistic

Note that I deleted the 4.1.0 DATA subdirectory and just copied the backup of my 3.23.52 DATA subdirectory back in (C:\mysql).

How to repeat:
********Create a Table using 3.23.52:
CREATE TABLE MODUL30 (M3ID INT(11) UNSIGNED NOT NULL PRIMARY KEY, 
    M3BILD MEDIUMBLOB);
********Fill table with data
********upgrade to mySQL 4.1.0 (from 3.23.52).
********Combine code snippets below to a working VB6 project
********Connect to Database:
public cnConn as ADODB.Connection
Public Sub ConnectToDB()
    Dim i As Integer
    set cnConn = new ADODB.Connection
    On Error GoTo error_Connect
    StrConn = "driver={MySQL ODBC 3.51 Driver};server=EVO1005V;uid=root;" & _                  "pwd=myPassword;database=KME_ADM;option=16387"
    cnKirch.ConnectionTimeout = 5
    cnKirch.ConnectionString = StrConn
    cnKirch.Open
    Exit Sub    
error_Connect:
    If cnKirch.Errors.Count <> 0 Then
       gloErrRet = cnKirch.Errors.Count
       For i = 0 To cnKirch.Errors.Count - 1
          MsgBox "Provider Error: " & cnKirch.Errors.Item(i), vbExclamation
       Next i
    End If
End Sub

********Create Recordset using following VB6 function:
Public Function SVSetDB() As ADODB.Recordset
    Dim rsTemp As ADODB.Recordset
    Dim i As Integer    
    gloErrRet = 0    
    On Error Resume Next    
    Set rsTemp = New ADODB.Recordset
    rsTemp.CursorLocation = adUseClient
    rsTemp.Open gloStrSQL, _
                    cnConn, _
                    adOpenStatic, _
                    adLockOptimistic, _
                    adCmdText
    If cnConn.Errors.Count = 0 Then
        Set SVSetDB = rsTemp
    Else
        gloErrRet = cnConn.Errors.Count
        For i = 0 To cnConn.Errors.Count - 1
            MsgBox "Provider Error: " & cnConn.Errors.Item(i)
        Next i
    End If
End Function

Please change all references of "cnKirch" in the code to "cnConn".

The problem here seems the same as reported in the bug #701:
the mediumblob column is showed on 4.1 as mediumtext:

c:\mysql\bin>mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.57-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> desc modul30\G
*************************** 1. row ***************************
  Field: M3ID
   Type: int(11) unsigned
   Null:
    Key: PRI
Default: 0
  Extra:
*************************** 2. row ***************************
  Field: M3BILD
   Type: mediumblob
   Null: YES
    Key:
Default: NULL
  Extra:
2 rows in set (0.08 sec)

c:\mysql\bin>mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.0-alpha-max-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> desc modul30\G
*************************** 1. row ***************************
    Field: M3ID
     Type: int(11) unsigned
Collation: binary
     Null:
      Key: PRI
  Default: 0
    Extra:
*************************** 2. row ***************************
    Field: M3BILD
     Type: mediumtext character set latin1
Collation: latin1_swedish_ci
     Null: YES
      Key:
  Default: NULL
    Extra:
2 rows in set (0.00 sec)

The bug with BLOBs silently converted to TEXT is fixed. Please test 4.1.1 release (when it will be out) to be sure your ADO problem was indeed caused by this bug.