VB6 MySQL BLOB

I am trying to put an image into a MySQL BLOB field.

I have tried :

mystream.LoadFromFile "C:\Temp\JClIRA.jpg"

rs!Image = mystream.Read

and I have tried AppendChunk.

All the code executes without an error, but the field remains {null}

If I debug.print rs("Image") I see a large chunk of data.
If I debug.print Len(rs("Image")) I get something like 24654
But the field *stll* ends up {null}

If I use TOAD and Load Fom File into the field, it works, and I can then read the record and save it to a file, and that works.

Why will it not update the record properly?

Moved from CodeBank, which is for finished code snippets rather than questions.

That's not enough information... what does all of the rest of the code look like? Your SQL? The Update?

-tg

OK, I have tried two different methods with the same result.

The target is a MySQL table with the field "Image" defined as BLOB

First:

Dim rs As New ADODB.Recordset
Dim mystream As New ADODB.Stream
mystream.Type = adTypeBinary

rs.Open "SELECT Image FROM tblDRSImages WHERE ID = 0", cnCP, adOpenDynamic, adLockOptimistic

rs.AddNew

mystream.Open
mystream.LoadFromFile "C:\Temp\JClIRA.jpg"

rs!Image = mystream.Read

rs.Update
rs.Close
mystream.Close

And:

Dim file_num As String
Dim file_length As Long
Dim bytes() As Byte
Dim num_blocks As Long
Dim left_over As Long
Dim block_num As Long
Dim sFileName As String
Dim rs As New ADODB.Recordset
Const BLOCK_SIZE = 16384

rs.Open "SELECT Image FROM tblDRSImages WHERE ID = 0", cnCP, adOpenDynamic, adLockOptimistic

rs.AddNew

sFileName = "C:\Temp\JClIRA.jpg"
file_num = FreeFile
Open sFileName For Binary Access Read As #file_num

file_length = LOF(file_num)

If file_length > 0 Then
num_blocks = file_length / BLOCK_SIZE
left_over = file_length Mod BLOCK_SIZE


ReDim bytes(BLOCK_SIZE)
For block_num = 1 To num_blocks
Get #file_num, , bytes()
rs("Image").AppendChunk bytes()
Next block_num

If left_over > 0 Then
ReDim bytes(left_over)
Get #file_num, , bytes()
rs("Image").AppendChunk bytes()
End If

Close #file_num
End If

rs.Update
rs.Close

In both cases the result is a {Null} value in the field.

If I do DEbug.print rs("Image!) I see a large block of data,
If I do Debug.Print LEN(rs("Image")) I get 24356

But the resultant field is still {Null}

Using TOAD I can edit the record and Load From File and it populates the field correctly, and the following code works perfectly:

rs.Open "SELECT Image FROM tblDRSImages WHERE ID = 5", cnCP

mystream.Open
mystream.Write rs.Fields(0)
mystream.SaveToFile "C:\Temp\JClIRA_TEST.jpg", adSaveCreateOverWrite

rs.Close
mystream.Close

I see that you're initially selecting no records, then adding a new one before setting the image field.... what about ID... what about the rest of the fields in the row? I'm guessing that ID is the PKey, right? That's probably the problem right there... You're not giving it the info it needs in order to update the field properly. Even if ID is an autoincrement field, it still needs to be part of the select so thaat the recordset has it. At least that's the way I've always done it.

-tg

Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID

The opened the record and both methods *apparently* work and the record does contain data.

*but* -

The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.

rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP

mystream.Open
mystream.Write rs.Fields("Image")
mystream.SaveToFile "C:\Temp\JClIRA_TEST.jpg", adSaveCreateOverWrite


rs.Close
mystream.Close

Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID

The opened the record and both methods *apparently* work and the record does contain data.

*but* -

The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.

rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP

mystream.Open
mystream.Write rs.Fields("Image")
mystream.SaveToFile "C:\Temp\JClIRA_TEST.jpg", adSaveCreateOverWrite


rs.Close
mystream.Close

Have you tried changing the Stream's type to binary before calling LoadFromFile ?

Yes,
mystream.Type = adTypeBinary

But it also has the same issue using the AppendChunk bytes() method to create the record.

Yes,
mystream.Type = adTypeBinary

But it also has the same issue using the AppendChunk bytes() method to create the record.

Quote:

---

Originally Posted by Hekla

Thanks - that worked, I ran an INSERT query first to create the record and retrived the LAstInsrtID

The opened the record and both methods *apparently* work and the record does contain data.

*but* -

The next code to test it by opening the record and writing it to a file also works but the resulting file is corrupt.
Looking at the record in TOAD it also will not show the image.

rs.Open "SELECT * FROM tblDRSImages WHERE ID = " & lRID, cnCP

mystream.Open
mystream.Write rs.Fields("Image")
mystream.SaveToFile "C:\Temp\JClIRA_TEST.jpg", adSaveCreateOverWrite


rs.Close
mystream.Close

---

Yeah, now that you mentioned it, I seem to remember now that's how we ended up doing it with SQL Server & Sprocs... we'd insert the record first, using a dummy marker for the BLOB &H011x ... or something like that... then make a second pass with the stream/byte array and do an UPDATE to set the BLOB field. (gads, that was like some nearly 20+ years ago for me, where has the time gone!? I'm surprised I even remembered that.) It was a real pain in the tookas. What was weird - if I remember right - was that if we inserted the binary data straight through an insert in a SQL script, it would be fine... it was only ever an issue through ADO ... I don't know that we ever found out what the reason for it was. Getting the data was far easier. Go figure.


-tg

Not sure what else to suggest. Working fine here creating and populating a new Jet 4.0 MDB.

Code:

---

Private Function PopulateDb() As Boolean
    'Returns True on failure.

    Dim InsertRS As ADODB.Recordset
    Dim Fields As Variant
    Dim PicStream As ADODB.Stream
    Dim DirItem As String
    Dim DotPos As Long
    Dim FileName As String
    Dim FileExt As String

    On Error Resume Next
    GetAttr PICFOLDER
    If Err = 0 Then
        On Error GoTo 0
        Set InsertRS = New ADODB.Recordset
        InsertRS.Open "Pictures", Conn, adOpenForwardOnly, adLockOptimistic, adCmdTable
        Fields = Array(1, 2, 3)
        Set PicStream = New ADODB.Stream
        PicStream.Type = adTypeBinary
        DirItem = Dir$(PICFOLDER & "\*.*", vbNormal)
        Do While Len(DirItem) > 0
            If (GetAttr(PICFOLDER & "\" & DirItem) And vbDirectory) = 0 Then
                DotPos = InStrRev(DirItem, ".")
                If DotPos > 0 Then
                    FileName = Left$(DirItem, DotPos - 1)
                    FileExt = Mid$(DirItem, DotPos + 1)
                    If InStr(SUPPORTED_EXTENSIONS, "$" & LCase$(FileExt) & "$") Then
                        With PicStream
                            .Open
                            .LoadFromFile PICFOLDER & "\" & DirItem
                            InsertRS.AddNew Fields, _
                                            Array(FileName, FileExt, PicStream.Read(adReadAll))
                            .Close
                        End With
                    Else
                        InsertRS.AddNew Fields, Array(FileName, FileExt, Null)
                    End If
                End If
            End If
            DirItem = Dir$()
        Loop
        InsertRS.Close
    Else
        On Error GoTo 0
        MsgBox "No source folder """ & PICFOLDER & """"
        PopulateDb = True
    End If
End Function

---

It is probably cleaner though to just use VB6's native I/O to do a binary read into a Byte array and call it good. Use of an ADO Stream here is really a fallback technique for more the limited VBScript language as used in old ASP pages.

Sad to see so many people still copy/pasting stale old ASP VBScript snippets as VB when VB is so much more powerful.


Most of the attachment size is due to sample picture files.

Code:

---

InsertRS.Open "Pictures", Conn, adOpenForwardOnly, adLockOptimistic, adCmdTable

---

You're opening up the whole table there... so when you then add a row, it has all the pkey info... the way the OP was going about it, ADO didn't have all of the PKey info as far as I could tell... he was only selecting his image field, returning a dummy recordset, adding a row.. so it had no context with which to add the pkey info when he then updated the database. My thought (since I didn't have a way to test it) was to at least include the ID field in the select so the RS had the PKey schema info and could then insert correctly... On the other hand... I do remember having similar issues back in the day when we were doing something similar, and having to do a two-step process, in which we'd insert the record first, get the ID, then do an update to get the binary data in. But that was some time ago, things may have (and probably have) changed since then, and it's possible I'm not remmbering something right, so take it with a grian of salt (or two grains).



As far as reading in the file, I agree... I've always read everything into a byte array, then used appendChunk to get it into the database. Never once did that fail me.


-tg

Field.AppendChunk is another overused ADO feature originally intended mainly for ASP scripts. It may have been necessary way back in ADO 2.0 and before when ASP scripts were the main ADO client. You can still use it in low-memory scenarios but it hasn't been a preferred approach in two decades. AppendChunk of the entire BLOB is just plain hilarious.

I don't think my open type has anything to do with his problem. For Jet 4.0 opening the table directly would be better but I'm not sure his MySQL connector supports that. The original version of the program I posted above use a Jet stored procedure to insert new rows and worked just fine:

Code:

---

CREATE PROC [InsertPic] (NewName TEXT(255), NewExt TEXT(12), NewPic IMAGE) AS
INSERT INTO [Pictures] ([FileName], [FileExt], [FilePic])
VALUES (NewName, NewExt, NewPic)

---

Any of those are preferable to a crude technique like opening an updatable query and adding to that. But even the convoluted approach of an updatable query should still be aware of the table's keys including the primary key.

As far as I can tell he didn't say records failed to update. His problem seems to be data corruption writing the BLOB column of the table. I still think the Stream object is being used incorrectly in his code.