Bug #61922 LONGBLOB data problem with ADO Recordset Update
Submitted: 20 Jul 2011 2:32 Modified: 20 Jul 2011 21:58
Reporter: John Smith Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:mySQL 5.1, MyODBC 5.1 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: ADO, odbc 5.1

[20 Jul 2011 2:32] John Smith
Description:
Data inserted into a LONGBLOB (also other BLOB fields) using the ADODB object gets corrupted when calling Update of the recordset, but using a UPDATE SQL Statement works. This code was made in VB6 but also happens in vb.net (and probly anything using the ADODB object)

I inserted "1111" and it has the right data till the tbl.update statement.

How to repeat:
Dim db As Connection
Dim tbl As Recordset
Dim Data() As Byte
Dim i As Long

Set db = New Connection
db.ConnectionString = "Driver=MySQL ODBC 5.1 Driver;Server=ipaddress;Database=test;Uid=root;Pwd=password;"
db.Open

Set tbl = New Recordset
tbl.Open "SELECT * FROM test123;", db, adOpenKeyset, adLockOptimistic
tbl.MoveFirst

ReDim Data(2)
For i = 0 To 2
    Data(i) = Asc("1")
Next

tbl.Fields("TestFile").Value = Data

For i = 0 To 2
    MsgBox "Right data before Update!" & vbCrLf & Chr(tbl.Fields("TestFile").Value(i))
Next

tbl.Update

For i = 0 To 2
    MsgBox "Not the right data!" & vbCrLf & Chr(tbl.Fields("TestFile").Value(i))
Next

tbl.Close

MsgBox "Works with 'UPDATE SQL'"

db.Execute "UPDATE test123 SET TestFile=1111 WHERE UIndex=55;"

db.Close
[20 Jul 2011 2:43] John Smith
Forgot to add the Database Info

CREATE DATABASE Test;
CREATE TABLE Test123 (UIndex INT AUTO_INCREMENT PIRMARY KEY, TestFile LONGBLOB)
INSERT INTO Test123 (TestFile) VALUES ('2222')
[20 Jul 2011 10:10] Valeriy Kravchuk
What exact version of MyODBC, 5.1.x, do you use?
[20 Jul 2011 14:31] John Smith
It is ODBC 5.1.3
[20 Jul 2011 15:00] John Smith
OK I just upgraded to 5.1.8 (didn't relise that a newer ODBC driver was out) but it seems to fix it.

So Thank you for the time.
[20 Jul 2011 15:15] John Smith
OK There is still something wrong because it updated once, but now it dosn't update any LONGBLOB fields.

The Value changes for the Recordset Object, but once the Update is called it reverts back to the data that was there.
[20 Jul 2011 15:24] John Smith
Nevermind you can close this I found my problem and it was some left over good from testing
[20 Jul 2011 22:02] Miguel Solorzano
Closing according last comment.