×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

BLOB in mysql to image control.
2

BLOB in mysql to image control.

BLOB in mysql to image control.

(OP)
Hi..

I know how to add a image file into a blob field of mysql. I also know how to get it back out into a file on windows. but I do not want that. I would like to have a variable that holds the picture So that i can do something like
image1.picture = myblob

this without first creating some temp folder on the system.

the meaning, I have some products in a database and also a picture of it. I want to display these products toghetter with the image wich is stored in a blob file.


hope sombody can help me..
I use Visual Basic 6
ADODB stream

and mysql as a database.

tank you.

greetings
roel.

RE: BLOB in mysql to image control.

I'm not sure if i am understanding your question correctly but...

The following code will load a picture into your image control.  MyPath is a string variable that should look something like this: MyPath = "C:\picture.bmp"

Image1.Picture = LoadPicture(MyPath)

RE: BLOB in mysql to image control.

Sorry you can't do this with default controls.
The standard method is to stream the image to disk then have the control load in the image.  Look below for a link to a good article about is.

Let me explain the "WHY".  

The .picture property != the pictures file format.  It is a DIB (Device Independent Bitmap).  File formats are different depending on colordepth and type (ie GIF,JPEG,BMP,RLE,PNG,etc)  
The LoadPicture() function is responsible for taking different types of image file formats and pulling them in.  It looks inside the file to pull additional information it need to build a picture in a common format, the DIB.

These .picture properties are pointers to the actual images but the controls don't give you any mechanism to pull the whole DIB out and put it into another location.  Also trying to stick a .GIF file into that location would not work since the format of a .GIF file is not the same as a DIB.

In .Net I believe the standard controls are designed to work with streams cutting out the need for physically writing to disk but it is essentially the same.

http://www.experts-exchange.com/Programming/Programming...

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

RE: BLOB in mysql to image control.

(OP)
tank you.

So it is actualy better to just put all immages on the diskdrive so that the program does not have to do the step of streaming it to a file.

A link to the immage in the database will be faster then a blob if i'm correct.

best regards,
Rpem

RE: BLOB in mysql to image control.

2
Contrary to what SemperFi says, it is quite possible to save and retrieve OLE Picture objects to and from databases without using intermediary files and only using simple controls and streams. Here is a simple example. You'll need a form with two picture boxes and a command button. The first picture box should be loaded with a picture:

Option Explicit

Private Sub Command1_Click()
    ' For this example I quickly put an Access DB together, and used the Dataenvironment to access it
    ' You'll need to modify appropriately for your own environment
    
    ' Save Picture to database
    SaveImage Picture1.Picture, DataEnvironment1.rsCommand1
    ' Retrieve picture from database
    Set Picture2.Picture = GetImage(DataEnvironment1.rsCommand1)
End Sub

' Save specified picture into column called "BLOB" in specified recordset
' In the case of this particular example, BLOB is an Access OLE Object data type
Private Function SaveImage(picPicture As Picture, rsImage As ADODB.Recordset)
    Dim pb As PropertyBag
    Dim mstream As ADODB.Stream
    
    
    rsImage.Open
    rsImage.AddNew ' always add image as new row to the table
    
    Set pb = New PropertyBag
    Set mstream = New ADODB.Stream
    pb.WriteProperty "Picture", picPicture
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write pb.Contents
    mstream.Position = 0 ' Back to beginning of stream
    rsImage("BLOB").Value = mstream.Read
    
    rsImage.Update
    rsImage.Close
End Function

Private Function GetImage(rsImage As ADODB.Recordset) As Picture
    Dim pb As PropertyBag
    Dim mstream As ADODB.Stream
        
    rsImage.Open
    rsImage.MoveLast ' in this example we only ever bother getting the last image saved

    Set pb = New PropertyBag
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rsImage("BLOB").Value
    mstream.Position = 0 ' Back to beginning of stream
    pb.Contents = mstream.Read
    Set GetImage = pb.ReadProperty("Picture")
    
    rsImage.Close
End Function

RE: BLOB in mysql to image control.

Hello,

I am having problems using your code.  I needed to change it to work in my situation, and I'm afraid I may have messed something up.

I have a MySQL database running with a 'files' table.  It has a 'file_id', 'file_name', 'file_size', and 'file'.  'file' is the BLOB (mediumblob) column.

I only need to be able to retrieve the blobs to an Image.

Here is how I edited the code:

============[I want the picture to change when I hit this button]==========

Private Sub Command4_Click()
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& "DATABASE=test;" _
& "UID=root;" _
& "PWD=ThisWasEasy;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384
conn.Open

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM files;", conn, adOpenStatic, adLockOptimistic
Set Me.imgPhotograph.Picture = GetImage(rst)
conn.Close
Set conn = Nothing

End Sub

==========[End Code]==========

==========[GetImage Code]==========
Private Function GetImage(rst As ADODB.Recordset) As Picture
    Dim pb As PropertyBag
    Dim stream As ADODB.stream
    
'    rst.Open
    rst.MoveLast
    
    Set pb = New PropertyBag
    Set stream = New ADODB.stream
    stream.Type = adTypeBinary
    stream.Open
    stream.Write rst("File").Value
    stream.Position = 0
    pb.Contents = stream.Read
    Set GetImage = pb.ReadProperty("Picture")
    
'    rst.Close
End Function
==========[End Code]==========

The comments are in the rst.open and rst.close because they raised errors.

When I run the program as is, I get a:

Run-time error '5': Invalid procedure call or argument.

When I click debug, the 'pb.contents = stream.read' line of 'GetImage' is highlighted.

I feel I am somewhat familiar with VB6, but I probably changed something wrong or implemented wrong.  I would appreciate any help on showing me how to fix this.

Thanks!

John

RE: BLOB in mysql to image control.

I'm afraid I'm not overly familiar with mySQL - but there are two things to consider:

1) the data in the 'file' column needs to have been stored as a persisted picture property (i.e my SaveImage function)

2) I believe mySQL has  packet size limitations (maximum row size) that are user-definable and which by default is limited to 1 meg. This can be changed by setting a max_allowed_packet in my.conf

RE: BLOB in mysql to image control.

Thanks again strongm!  I used the proper saving technique and it worked properly.

I have a couple more questions, though.

After saving the picture to the database, I used mystream to save the output to a file.  The size of the output file was 660KB--the size of the original file was 24KB (it is 476x472).  Also, the output file could not be viewed as a JPG.

Is the size issue just a problem when extracting the image, or is the file that bloated when stored inside the database?  Also, is there any way to convert that file back to JPG so that it can be exported to a file?  Is there a way to convert a JPG directly into the database for use with this procedure (without going through an Image?

Thanks!

John

RE: BLOB in mysql to image control.

Because a Picture holds a bitmap (or, more accurately a DIB, as SemperFiDownUnda explained), not any of the compressed formats.

RE: BLOB in mysql to image control.

Good Code Strongm.  

thojohp - JPEG, GIF, PNG, RLE all use some compression mechanism.  JPEG being Lossie, the others being lossless.  

DIB's are memory hoges.  Every pixel uses 4 bytes.  So you have to decide what you want to do.  Can you wear the size cost of storing DIBs in your database and get the benifit of not going throught the intermediate file to convert (unless you want to go play in C++ and make a component that does all this in memory) and just wear the saving the picture back to a GIF as more then a straight dump.  Or you need to go through a file and use the tools VB gives you.  It all really depends on what your requirements are.

Do a search....there are probably 3rd party image controls that do what you want...ie accept a JPEG/GIF/PNG/etc from a stream and do everything needed internally.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

RE: BLOB in mysql to image control.

Although is should be possible to modify the code to use OleLoadPicture rather than a propertybag, thus making it work with any image type that VB's LoadPicture can handle...

RE: BLOB in mysql to image control.

Yep, that works...

RE: BLOB in mysql to image control.

...and here's the code I put together to test the theory:

' Some code used adapted from Brad Martinez, http://www.mvps.org
' specifically, how to turn a byte array into an OLE IStream

' I *was* just going to use the ADO.Stream object directly, but OleLoadPicture doesn't seem to like it
' if we have not somehow locked the memory, hence the (adapted) use of Mr Martinez code

Option Explicit

Private Enum CBoolean
    CFalse = 0
    CTrue = 1
End Enum

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Const S_OK = 0
Private Const sIID_IPicture = "{7BF80980-BF32-101A-8BBB-00AA00300CAB}"
Private Const GMEM_MOVEABLE = &H2

Private Declare Function CreateStreamOnHGlobal Lib "ole32" (ByVal hGlobal As Long, ByVal fDeleteOnRelease As CBoolean, ppstm As Any) As Long
Private Declare Function OleLoadPicture Lib "olepro32" (pStream As Any, ByVal lSize As Long, ByVal fRunmode As Boolean, riid As GUID, ppvObj As Any) As Long

Private Declare Function CLSIDFromString Lib "ole32" (ByVal lpsz As Any, pclsid As GUID) As Long

Private Declare Function GlobalAlloc Lib "kernel32" (ByVal uFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Function GlobalFree Lib "kernel32" (ByVal hMem As Long) As Long
Private Declare Sub MoveMemory Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSource As Any, ByVal dwLength As Long)



Private Sub Command1_Click()
' For this example I quickly put an Access DB together, and used the Dataenvironment to access it
' You'll need to modify appropriately for your own environment

' Retrieve picture from database
Set Picture2.Picture = GetImage(DataEnvironment1.rsCommand1)
End Sub

' Saves image in DB
Private Sub Command2_Click()
    SaveImage "c:\sunset.jpg"
End Sub

' Simple binary stream to database
Private Function SaveImage(strFile As String)
    Dim hFile As Long
    Dim arrPic() As Byte
    Dim mstream As ADODB.Stream
    
    hFile = FreeFile
    
    Open strFile For Binary As hFile
    ReDim arrPic(LOF(hFile)) As Byte
    
    Get hFile, , arrPic
    Close hFile
    
    DataEnvironment1.rsCommand1.Open
    DataEnvironment1.rsCommand1.AddNew
    
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write arrPic
    mstream.Position = 0 ' Back to beginning of stream
    
    DataEnvironment1.rsCommand1("BLOB").Value = mstream.Read
    DataEnvironment1.rsCommand1.Update
    DataEnvironment1.rsCommand1.Close

End Function

Private Function GetImage(rsImage As ADODB.Recordset) As Picture
    Dim mstream As ADODB.Stream
    
    rsImage.Open
    rsImage.MoveLast
    
    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rsImage("BLOB").Value
    mstream.Position = 0
    
    Set GetImage = PictureFromByteArray(mstream.Read) 'PictureFromByteArray2(mstream)
    
    rsImage.Close
End Function

Public Function PictureFromByteArray(abpic() As Byte) As IPictureDisp ' Careful! Don't make this a StdPic or Picture object
    Dim nLow As Long
    Dim cbMem As Long
    Dim hMem As Long
    Dim lpMem As Long
    Dim IID_IPicture As GUID
    Dim istm As ADODB.Stream 'stdole.IUnknown ' IStream
    'Dim ipic As IPicture
    
    'Set istm = aistm
    
    ' Get the size of the picture's bits
    nLow = LBound(abpic)
    cbMem = (UBound(abpic) - nLow) + 1
    
    ' Allocate a global memory object
    hMem = GlobalAlloc(GMEM_MOVEABLE, cbMem)
    If hMem Then
    
        ' Lock the memory object and get a pointer to it.
        lpMem = GlobalLock(hMem)
        If lpMem Then
            
            ' Copy the picture bits to the memory pointer and unlock the handle.
            MoveMemory ByVal lpMem, abpic(nLow), cbMem
            Call GlobalUnlock(hMem)
            
            ' Create an IStream from the pictures bits
            If (CreateStreamOnHGlobal(hMem, CTrue, istm) = S_OK) Then
                If (CLSIDFromString(StrPtr(sIID_IPicture), IID_IPicture) = S_OK) Then

                    ' Create an IPicture from the IStream (the docs say the call does not
                    ' AddRef its last param, but it looks like the reference counts are correct..)
                    Call OleLoadPicture(ByVal ObjPtr(istm), cbMem, CFalse, IID_IPicture, PictureFromByteArray)

                End If ' CLSIDFromString
            End If ' CreateStreamOnHGlobal
        End If ' lpMem
        
        'Call GlobalFree(hMem) ' Not needed if CTrue set in CreateStream call
    End If ' hMem
End Function

RE: BLOB in mysql to image control.

strongm,

Thanks!  That works perfectly...Thanks again!  

John

RE: BLOB in mysql to image control.

Star for you Strongm - great job.  This is a keeper.

Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close