BLOB in mysql to image control. 2

[LordGarfield]

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.

 

[Kryzsoccer]

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)

 

[SemperFiDownUnda]

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/Pro...ges/Visual_Basic/VB_Databases/Q_20809962.html

Hope I've been helpful,
Wayne Francis

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

 

[LordGarfield]

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

 

[strongm]

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&quot.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&quot.Value
mstream.Position = 0 ' Back to beginning of stream
pb.Contents = mstream.Read
Set GetImage = pb.ReadProperty("Picture&quot

rsImage.Close
End Function

 

[thojohp]

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&quot.Value
stream.Position = 0
pb.Contents = stream.Read
Set GetImage = pb.ReadProperty("Picture&quot

' 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

 

[strongm]

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

 

[thojohp]

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

 

[strongm]

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

 

[SemperFiDownUnda]

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

 

[strongm]

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

 

[strongm]

Yep, that works...

 

[strongm]

...and here's the code I put together to test the theory:
[tt]
' 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&quot.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&quot.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

 

[thojohp]

strongm,

Thanks! That works perfectly...Thanks again!

John

 

[SemperFiDownUnda]

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