DreamingEagle
asked on
Storage, Retreival, and Display of .JPG Images in a SQL Database
I am offering 200 points on this question because the code sampling required might be a little more extensive than usual for a question here, and because I simply do not have time to research and hack the code out myself. Let's presume 1) the existence of a photo called MyImage.jpg in the root directory of a local machine, 2) that the table to be used is called 'Images', 3) that the field to be used for the images is called 'Image', that 4) ADO will be used to work with the table. If someone could provide the following points will be awarded without delay.
1. I am assuming the 'Image' datatype is the best to use.
2. Code to read c:\MyImage.jpg and store it into Images![Image].
3. Code to grab the image from Images![Image] and display the image in the PictureBox control.
Thanks much!
1. I am assuming the 'Image' datatype is the best to use.
2. Code to read c:\MyImage.jpg and store it into Images![Image].
3. Code to grab the image from Images![Image] and display the image in the PictureBox control.
Thanks much!
Just leaving a comment so as to "listen" to the activity on this question!
Preece
Preece
Have a look at these:
https://www.experts-exchange.com/jsp/qShow.jsp?qid=10218749
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20112693
https://www.experts-exchange.com/jsp/qShow.jsp?qid=10206397
just do a search on "blob" in all areas!
Hope this helps!
Preece
https://www.experts-exchange.com/jsp/qShow.jsp?qid=10218749
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20112693
https://www.experts-exchange.com/jsp/qShow.jsp?qid=10206397
just do a search on "blob" in all areas!
Hope this helps!
Preece
DreamingEagle,
Inside Access I used the 'OLE Object' datatype (there is no 'Image' datatype). So, I can't say that this code will work 100% with the 'Image' datatype. I'm also assuming that you have a field called ID that is unique.
You can simply:
Call InsertImage(1, "c:\MyImage.jpg")
'Assuming you have a PictureBox called picDisplay
Call DisplayImage(picDisplay, 1)
Andrew
------------------- THE CODE -------------------
Public Sub InsertImage(ID As Long, FileName As String)
Dim DB As Connection
Dim Images As Recordset
Dim Query As String
Dim hFile As Integer
Dim Buff() As Byte
Query = "SELECT Image FROM Images WHERE (ID=" & ID & ");"
Set DB = New Connection
Set Images = New Recordset
Call DB.Open("<DSN>")
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
If Not (Images.BOF And Images.EOF) Then
hFile = FreeFile
Open FileName For Binary Access Read As hFile
ReDim Buff(LOF(hFile)) As Byte
Get hFile, 1, Buff()
Close hFile
Call Field.AppendChunk(Buff())
Call Images.Update
End If
Images.Close
DB.Close
Set Images = Nothing
Set DB = Nothing
End Sub
Public Sub DisplayImage(PictureBox As PictureBox, ID As Long)
Dim DB As Connection
Dim Images As Recordset
Dim Query As String
Dim hFile As Integer
Dim Buff() As Byte
Const TempFile = "c:\temp\image.tmp"
Query = "SELECT Image FROM Images WHERE (ID=" & ID & ");"
Set DB = New Connection
Set Images = New Recordset
Call DB.Open("<DSN>")
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
If Not (Images.BOF And Images.EOF) Then
hFile = FreeFile
Open TempFile For Binary Access Write As hFile
Buff = Images("Image").GetChunk(I
Put hFile, , Buff
Close hFile
Set PictureBox.Picture = LoadPicture(TempFile)
Kill TempFile
End If
Images.Close
DB.Close
Set Images = Nothing
Set DB = Nothing
End Sub
Inside Access I used the 'OLE Object' datatype (there is no 'Image' datatype). So, I can't say that this code will work 100% with the 'Image' datatype. I'm also assuming that you have a field called ID that is unique.
You can simply:
Call InsertImage(1, "c:\MyImage.jpg")
'Assuming you have a PictureBox called picDisplay
Call DisplayImage(picDisplay, 1)
Andrew
------------------- THE CODE -------------------
Public Sub InsertImage(ID As Long, FileName As String)
Dim DB As Connection
Dim Images As Recordset
Dim Query As String
Dim hFile As Integer
Dim Buff() As Byte
Query = "SELECT Image FROM Images WHERE (ID=" & ID & ");"
Set DB = New Connection
Set Images = New Recordset
Call DB.Open("<DSN>")
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
If Not (Images.BOF And Images.EOF) Then
hFile = FreeFile
Open FileName For Binary Access Read As hFile
ReDim Buff(LOF(hFile)) As Byte
Get hFile, 1, Buff()
Close hFile
Call Field.AppendChunk(Buff())
Call Images.Update
End If
Images.Close
DB.Close
Set Images = Nothing
Set DB = Nothing
End Sub
Public Sub DisplayImage(PictureBox As PictureBox, ID As Long)
Dim DB As Connection
Dim Images As Recordset
Dim Query As String
Dim hFile As Integer
Dim Buff() As Byte
Const TempFile = "c:\temp\image.tmp"
Query = "SELECT Image FROM Images WHERE (ID=" & ID & ");"
Set DB = New Connection
Set Images = New Recordset
Call DB.Open("<DSN>")
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
If Not (Images.BOF And Images.EOF) Then
hFile = FreeFile
Open TempFile For Binary Access Write As hFile
Buff = Images("Image").GetChunk(I
Put hFile, , Buff
Close hFile
Set PictureBox.Picture = LoadPicture(TempFile)
Kill TempFile
End If
Images.Close
DB.Close
Set Images = Nothing
Set DB = Nothing
End Sub
Sorry....
Inside DisplayImage, the following line:
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
can be changed to:
Call Images.Open(Query, DB, adOpenForwardOnly, adLockOptimistic)
Andrew
Inside DisplayImage, the following line:
Call Images.Open(Query, DB, adOpenDynamic, adLockOptimistic)
can be changed to:
Call Images.Open(Query, DB, adOpenForwardOnly, adLockOptimistic)
Andrew
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't have the code at this very momment, sorry.
ASKER
Well, I should have fun evaluating this code the minute I get back to this project, which should be tomorrow, Thursday. If not, however, I am afraid it will be Monday. I am leaving Friday morning for a weekend in Mexico and have other kinds of "images" on my mind. Rest assured, though, I will evaluate these responses the minute I get a chance. I am very eager to get a large number of historical images in SQL tables where they can be properly catalogued, preserved, and studied by local historians. Thanks for the suggestions.
ASKER
Outstanding answer. The other code sample was also good, but using the stream object is clearly the way to go. I was able to modify this code, have it running, loop through my image collection, store them into the table, and have them displaying in the picture box in less than one hour. Thanks very much!
ASKER