Bug #27117 Usage of ODBC/BLOB/VB6 Error 80004005 bug #19065
Submitted: 14 Mar 2007 7:13 Modified: 22 Mar 2007 8:12
Reporter: Thomas Hermann Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.0 /odbc 3.51.12.00 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any
Tags: BLOB, bug #19065, Error 80004005, ODBC, VB6

[14 Mar 2007 7:13] Thomas Hermann
Description:
with reference to:
Accessing MySQL BLOB columns using Visual Basic 6, By Mike Hillyer

The update in this sample will return an error. The general error number 80004005 can be interpreted in many ways. Since I referenced "ODBC Driver & DataSource Name Functions" in this project this error message reveals the real problem:
---------------------------------------------------------------------------------
Run-time-error '-2147467259 (80004005)':
[MySQL][ODB C3.51 Driver][mysqld-5.0.27-community-nt]You have an error in your SQL sysntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index=6 AND picname='new3' AND picture")' at line 1.
---------------------------------------------------------------------------------
This error is caused by just the rs.Update-statement, not containing any SQL-statement at all, i.e. this SQL is built somewhere within the driver. 
The complete code for demonstration to be found below. 

How to repeat:
Option Explicit
'--------------- Additional Project References ----------------------------------------------------
'OLE Automation
'Microsoft ActixeX Data Objects 2.8 Library
'ODBC Driver & DataSource Name Functions
'----------------- ODBC Driver Installed------------------------------------------------------
'MySQL ODBC 3.51 Driver Version 3.51 12 00
'---------------------------------------------------------------------------------------
'--------------- MySQL Database --------------------------------------------------------
'CREATE DATABASE `mysqlblob` /*!40100 DEFAULT CHARACTER SET latin1 */;
'CREATE TABLE testbed(
'index INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
'picname VARCHAR(45),
'picture BLOB);
'ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=mysqlblob;UID=root;PWD=xenia;OPTION=16427"

Private Sub cmdStart_Click()
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=mysqlblob;UID=root;PWD=xenia;OPTION=16427"
cx.Open
If Err <> 0 Then
    MsgBox Error, 0, "Error at Open Connection"
End If
I = I + 1
rs.Open "Select * from testbed Where 1=0", cx, adOpenStatic, adLockOptimistic
rs.AddNew
'If Err <> 0 Then
'    MsgBox Error, 0, "Error at OPEN"
'End If
    Set stm = New ADODB.Stream
    stm.Type = adTypeBinary
    stm.Open
    stm.LoadFromFile App.Path & "\testpicture.jpg"
    rs!picname = "mitADD" & CStr(I)
    rs!Picture = stm.Read
    rs.Update
    If Err <> 0 Then
        MsgBox Error, 0, "Error at Create"
    End If
    stm.Close
    Set stm = Nothing
rs.Close
'------------------ Published Sample --------------------------------------------------------
'rs.Open "SELECT * FROM files WHERE files.file_id = 1", conn, adOpenStatic, adLockOptimistic
    'mystream.Open
    'mystream.LoadFromFile "c:\\updateimage.gif"
    'rs!file = mystream.Read
    'rs.Update
    'mystream.Close
'rs.Close
'------------------ My Code ----------------------------------------------------------------
rs.Open "Select * from testbed Where testbed.picname='" & "mitADD" & CStr(I) & "'", cx, adOpenStatic, adLockOptimistic
    Set stm = New ADODB.Stream
    stm.Type = adTypeBinary
    stm.Open
    stm.LoadFromFile App.Path & "\testpicture.jpg"
    rs!picname = "Updated" & CStr(I)
    rs!Picture = stm.Read
    rs.Update
    If Err <> 0 Then
        MsgBox Error, 0, "Error at Update"  'Error condition also closes the recordset
    End If
    stm.Close
    Set stm = Nothing
rs.Close
End Sub
[20 Mar 2007 14:29] Tonci Grgin
Thomas, thanks for your report.
I would like to see full test case (with DDL script) attached to this report.
[20 Mar 2007 14:47] Thomas Hermann
[20 Mar 15:29] Tonci Grgin

Thomas, thanks for your report.
I would like to see full test case (with DDL script) attached to this
report.

Hi, Tonci, how can I submit a ZIP file?
[20 Mar 2007 14:49] Thomas Hermann
This file contain everything

Attachment: MYSQLBLOB.zip (application/x-zip-compressed, text), 47.48 KiB.

[21 Mar 2007 15:54] Tonci Grgin
Thomas, what happens if you:
  - Change: "index INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY" to "ndx INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY"?
  - Use: rs.Properties("Update Criteria").Value = 0 'adCriteriaKey
  - Upgrade to MyODBC 3.51.14

Please, attach general query log from server around the time of error.
[21 Mar 2007 19:06] Thomas Hermann
Hallo Tonci,
I have made the recommended changes step by step:

1. changing the index field from Bigint to Integer as requested.
   This made no change, still the same error message
2. rs.Properties has no "values" there are properies "count" and "item" and
   a "refresh" method in ADODB.Resocrdset. What did you want me to do and where?
3. De-installed 3.51.12 and installed 3.51.14 as requsted.
   This made no change, still the same error message

If you read the error message thoroughly you will find that it displays an
SQL statement which is does not exist in the sample programm. Where ist in generated?
If you remove the line: " rs!picname = "mitADD" & CStr(I)" , then the error
message will still contain an update request for 'picname'. It even has an update request fon 'index'. I still suspect the problem is where this mysterious SQL statement is generated.
[21 Mar 2007 20:17] Tonci Grgin
Thomas, you missunderstood me:
 1) You have column named "INDEX" (at least in test sent to me). Try naming it "NDX"
 2) Please check on http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1

Now, what happens?

I too do have problems with your test case (I'm getting phantom passwords thus I'm not able to connect at all!) and wonder about "I still suspect the problem is where this mysterious SQL statement is generated.". So I'm making clean test now.

As for point 3), 3.51.14 is our first version fully tested against ADO conformance tests so it's a good thing to upgrade 3.51.12.
[21 Mar 2007 21:06] Tonci Grgin
Thomas, conside following 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 BLOB)")
        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)
        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()
[21 Mar 2007 21:07] Tonci Grgin
... and it's result

Attachment: Bug27117.jpg (image/jpeg, text), 115.45 KiB.

[21 Mar 2007 22:31] Thomas Hermann
Hi Tonci,
as per your request to naming the first column NDX, strange things happen.
The error at rs.Update changes to:

Run-time-error '-2147467259(80004005)
(Unfortunately now in German)
"Fehler bei einer abfragebasierten Aktualisierung, da die zu aktualisierende
Zeile nicht gefunden wurde."
(Translated to English:)
"Error at query based update, since the line(record?) could not be found."

This seems unbelievable, since only the name of the column with the primary key has changed and nothing else. That makes me shiver. What else do I have to expect.

I send the screenshot of the error message as a jpg-file
[21 Mar 2007 22:32] Thomas Hermann
Error message related to naming primary key column NDX

Attachment: MyBlobErMsg1.JPG (image/jpeg, text), 15.04 KiB.

[21 Mar 2007 23:49] Thomas Hermann
Thank you, Tonci!
It works!
I have modified your code a little bit in order to make it closer to the project I am working on currently. When you press the START button it will produce 10 records with 10 different names.

I am also sending the VB6-Files in case they are of any value.
[21 Mar 2007 23:50] Thomas Hermann
the vbp-file

Attachment: TONCISQLBlob.vbp (application/octet-stream, text), 1.11 KiB.

[21 Mar 2007 23:50] Thomas Hermann
the frm-file

Attachment: TONCISQLBLOB.frm (application/octet-stream, text), 3.50 KiB.

[21 Mar 2007 23:51] Thomas Hermann
the vbw.file

Attachment: TONCISQLBlob.vbw (application/octet-stream, text), 57 bytes.

[21 Mar 2007 23:52] Thomas Hermann
the test picture

Attachment: testpicture.jpg (image/jpeg, text), 23.17 KiB.

[22 Mar 2007 8:12] Tonci Grgin
Thomas, glad that your code works now.

As for "Fehler bei einer abfragebasierten Aktualisierung, da die zu aktualisierende Zeile nicht gefunden wurde." it is to be expected :) That's why I
 - posted URL to ADO updates explanation
 - asked for general query log from server to see what reaches it
No reason to be concerned.

Please, take a look at that ADO page and general query log with "wrong" and with "rigt" code to see how ADO behaves.