MySQL Bugs: #13481: Cannot save a BLOB file into mysql 4.1.13 to 5.0.X

Bug #13481	Cannot save a BLOB file into mysql 4.1.13 to 5.0.X
Submitted:	26 Sep 2005 12:32	Modified:	27 Aug 2007 11:21
Reporter:	greivin rodriguez	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows XP, Windows 98)
Assigned to:		CPU Architecture:	Any

Description:
With MySQL Version 4.1.12 works pretty good
With MySQL Version 4.1.13 to 5.0.12 do not works, displays the error messaje

"The string cannot be 0 lenght messaje..."

I too used the Appendchunk method... and fails... it was used to upload .dll bynary files to a mysql Server for the clients upgrate the application automatically in each run...

I used this procedure aprox 1.5 years ago and it works pretty good... with a vb6...

How to repeat:
Microsoft Visual Basic 6.0 (SP6) And VBA For Applications (Office 2003 Pro)
With ADO 2.8 ()
MyODBC 3.51.11
-------------------

Dim strStream As Object
Set strStream = CreateObject("adodb.stream")
strStream.Type = 1 'adTypeBinary = 1
strStream.Open
strStream.LoadFromFile FileName
irs(blobFieldName).value = strStream.Read
irs.update '--> Error, the string cannot be 0 lenght messaje
strStream.Close
Set strStream = Nothing

Suggested fix:
N/R

Could you please provide a complete test case (not a partial code)
with preference it can be used with Access.

Thanks in advance.

Option Explicit

'DDL For table in test database
'CREATE TABLE `file_table` (
'  `filename` varchar(64) NOT NULL default '' COMMENT 'Id of File',
'  `filedata` longblob NOT NULL COMMENT 'Bynary File Data',
'  PRIMARY KEY  (`filename`)
') ENGINE=MyISAM DEFAULT CHARSET=latin1;

Private Sub Command1_Click()
    Dim iTmpCnn As String
    iTmpCnn = "Provider=MSDASQL.1;Extended Properties=""DRIVER={MySQL ODBC 3.51 Driver};DESC=;DB=test;SERVER=127.0.0.1;UID=root;PASSWORD=;PORT=3306;SOCKET=;OPTION=18475;STMT=;"""
    
    MsgBox "The save was " & Save2BLOB("testfile.dat", "C:\2MySQL\new.rtf", iTmpCnn)
End Sub

Private Function Save2BLOB(pFileName As String, pSourceName As String, pCnn As String) As Boolean
    Dim iCnn As ADODB.Connection
    Dim iRs As ADODB.Recordset
    Dim strStream As ADODB.Stream
    'This code works uploading aprox 100 files each day... (rtf docs) arround 1.5 years...
    Save2BLOB = False
    On Error Resume Next
    Set iCnn = New ADODB.Connection
    iCnn.Open pCnn
    If Err.Number = 0 Then
        Set iRs = New ADODB.Recordset
        'Works pretty good with 4.0.12 or later...
        iRs.Open "SELECT filename, filedata FROM file_table WHERE filename = '';", iCnn, adOpenStatic, adLockOptimistic 'Get Empty recordset... for add new record..
        If Err.Number = 0 Then
            Set strStream = New ADODB.Stream
            iRs.AddNew
                strStream.Type = 1 'adTypeBinary
                strStream.Open
                strStream.LoadFromFile pSourceName
                iRs("filename").Value = pFileName
                iRs("filedata").Value = strStream.Read
            iRs.Update
            If Err.Number = 0 Then
                Save2BLOB = True
                'Save is OK!
            Else
                MsgBox "Error: " & Err.Number & " - " & Err.Description
                ' -2147467259 ODBC Error display... with newer versions of MySQL... 4.0.13 of greater
            End If
            strStream.Close 'Close the adodb.stream
            Set strStream = Nothing
            iRs.Close
            Set iRs = Nothing
        End If 'If Err.Number = 0 Then (iRs)
        iCnn.Close
        Set iCnn = Nothing
    End If 'If Err.Number = 0 Then (iCnn)
    Set strStream = Nothing
End Function

VB6 Project example... and rtf sample file

Attachment: 2MySQL.zip (application/zip, text), 2.92 KiB.

I tested on 4.1.15 the test case and had the follow message "[Microsoft][ODBC Driver Manager] Invalid string or buffer length" after iRs.Update(). strStream read file correctly.

I can't migrate to mysql 5.0.13 RC, because this feature is estrictly necesary on my program... The error is allocated between MyOBDC 3.51.11-2 and MySQL 4.0.13 to MySQL 5.0.13 rc.
I use actually MySQL 4.0.12 with MyODBC 3.51.11-2, this release haven't the bug...

I was not able to reproduce the bug on C program. I guess it is ODBC connector error. My C test case is attached.

C test case

Attachment: test.c (text/plain), 5.06 KiB.

I test then same example that was provide, but in the table definition i changed the longblob field to mediumblod and the bug disappear... (Persist with the longblob type)

This bug is really caused by MyODBC Driver, with MySQL version 4.0.13 And later And MySQL 5.0

I tested on:
  MySQL Server for SuSE Linux Enterprise Server 9.1
  MySQL Server for Windows.

Thanx

I'm have the exact same error.

This may be a duplicate of Bug #12805.

Hi all. I believe this is the problem with ADO updates trying to match values of all fields (as is default behavior). Consider following (working!) code:

        Dim I As Long
        Dim cx As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim stm As ADODB.Stream
        cx.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PWD=***;OPTION=16427"
        cx.Open()

        cx.Execute("DROP TABLE IF EXISTS bug27117")
        cx.Execute("CREATE TABLE bug27117(ndx INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,picname VARCHAR(45),picture LONGBLOB)")
        cx.Execute("INSERT INTO bug27117 (ndx) VALUES (NULL)")

        I = I + 1

        rs.CursorLocation = CursorLocationEnum.adUseClient
        rs.Properties("Update Criteria").Value = 0 'adCriteriaKey
        rs.Open("Select * from bug27117", cx, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic)

        rs.AddNew()

        stm = New ADODB.Stream
        stm.Type = StreamTypeEnum.adTypeBinary
        stm.Open()
        stm.LoadFromFile(Application.StartupPath & "\testpicture.jpg")
        rs.Fields("picname").Value = "mitADD" & CStr(I)
        rs.Fields("picture").Value = stm.Read

        rs.Update()
        stm.Close()
        stm = Nothing
        rs.Close()

main line being "rs.Properties("Update Criteria").Value = 0 'adCriteriaKey". Please add this to your code and retest.

Test case is taken from Bug#27117 and you can check more on "Update Criteria" there.

Jim, I will retest Bug#12805 now.