dcsimg
Results 1 to 14 of 14

Thread: VB6 MySQL BLOB

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    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?

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    33,929

    Re: VB6 MySQL BLOB

    Moved from CodeBank, which is for finished code snippets rather than questions.
    My usual boring signature: Nothing

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: VB6 MySQL BLOB

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    Re: VB6 MySQL BLOB

    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

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: VB6 MySQL BLOB

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    Re: VB6 MySQL BLOB

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    Re: VB6 MySQL BLOB

    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

  8. #8
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,322

    Re: VB6 MySQL BLOB

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

  9. #9

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    Re: VB6 MySQL BLOB

    Yes,
    mystream.Type = adTypeBinary

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

  10. #10

    Thread Starter
    New Member
    Join Date
    Jan 2019
    Posts
    12

    Re: VB6 MySQL BLOB

    Yes,
    mystream.Type = adTypeBinary

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

  11. #11
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: VB6 MySQL BLOB

    Quote Originally Posted by Hekla View Post
    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,322

    Re: VB6 MySQL BLOB

    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.
    Attached Files Attached Files

  13. #13
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,381

    Re: VB6 MySQL BLOB

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  14. #14
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,322

    Re: VB6 MySQL BLOB

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width