The BLOB (not the movie!)

[ADezii]

Of all the questions asked here at TheScripts, the one which appears with the most frequency relates to the storing of Graphic Images within Access. There are basically three techniques available to manipulate Image Files in Access and they are:

1. Store the Image in an OLE Object Field and display it in a Bound Object Frame. The Image can either be Linked or Embedded.
2. Store the Path to the Image in a Text Field and use an Image Control to dynamically display it.
3. Store the Image as a Binary Large Object (BLOB) in an OLE Field, extract the Image when required, and use an Image Control to display the Image.

The first technique is the most problematic primarily because of Server Errors (the Application launched to display the Image) and horrendous Database bloat because of the manner in which Access internally stores Images.

The second approach, although relatively easy to implement, has its own set of inherent problems. This technique cannot be used with Continuous Forms, and a fully qualified UNC Path is a strict requirement. If you move the Images, if they reside on a Server and the Folder containing them is not shared, if the Server is turned off, if you do not have Read Permissions to the PC, I think you get the idea.

After all is said and done, the third technique quickly becomes the obvious choice for Image storage. Storing Pictures as BLOBs in the Database is the most efficient way on containing Image data because the Picture data is stored byte for byte, exact size, with the Images reflecting their true sizes.

When BLOBs are stored in the Database with other data, BLOB and tabular data are backed up and recovered together and are synchronized, there are no File Paths to contend with, and no resultant Database bloating. Data consistency is ensured because INSERT, UPDATE, and DELETE operations occur in the same transaction. Separate security measures need not be created since BLOB and regular data coexist.

The minimal requirement to implement the BLOB technique is two Functions, one to put files into the Database, and the other to take them out. You can use either DAO or ADO to grab the Image File and read it into an OLE Field (1st Function). To display the Image we have to extract it from the Database (2nd Function) into a Temporary File then use an Image Control to display it by setting the Control's Picture property to the Path of the Temp File.

Some of the subject matter that appears in this Tip, as well as code that exists in the Demonstration Database were taken from Alan Warren's web site. I thought that he provided an excellent insight into this Topic and his code provided a very good example of how to implement this functionality. I have changed the entire Theme of the Database, made structural changes to the Tables, cosmetic changes to the Form, and have also added, deleted, and modified Mr. Warren's code. The critical code sections were essentially kept in tact.


Required References:
Microsoft Scripting Runtime
Microsoft ActiveX Data Objects X.X Library

[noviceadmin]

This post is really helpful but it was unable to solve my problem

I have disscussed it to community but still waiting for the solution

Thank you all for this great ideas and also thanks to A Dezzi who is still trying to solve my problem

[FishVal]

Hi, noviceadmin.

As I've posted before, I make a guess VB Picture control Picture property operates with stdole.IPictureDisp (or stdole.StdPicture which is the same). If so you may use stdole library finction SavePicture() to store it as temporary disk file and upload to BLOB field in the manner described above.

At least with MSForms.Image control this works flawless.

Regards,
Fish

P.S. Sure stdole library (...\WINDOWS\system32\stdole2.tlb) has to be referenced.

[ADezii]

This post is really helpful but it was unable to solve my problem

I have disscussed it to community but still waiting for the solution

Thank you all for this great ideas and also thanks to A Dezzi who is still trying to solve my problem

I'll now Post the PM which I sent to both you and Killer previously and, as you can plainly see, is in a direct line with what FishVal has suggested. The initiative is now yours to test this logic. Should I get a chance, I'll test it also, but with my workload it is not likely.

Thanks for your insight into this matter, Killer. The VB/Access Interface is not the difficult part, it is the saving of the Image within the Picture Box to an Access Database directly as a BLOB. I think I'll recommend your approach, and even test it out myself if and when I get the chance, since my field was originally VB. My logic would be to Save the Image in the Picture Box to a File using the SavePicture Statement, then use the tested BLOB code to do the rest. The only problem my be as to whether this can be handled in a single as opposed to multiple steps. There may also be a problem with the available Graphics Formats supported by a Picture Box. I know that if an Image was loaded as a *.bmp, *.ico, or *.wmf, it will be saved in the same format whereas *.gifs and *.jpgs are always saved in *.bmp format which now involves determining the Graphic Type that was initially loaded into the Control. This problem now becomes more complex by the minute. In any event, forgive me for ranting and thanks again for your help, it is greatly appreciated.

[noviceadmin]

Dear A dezzi

Just wanted to share my success with you and all who helped me totake the steps .
finall i decide to store the Image path to database and my software is working fine. Thanks to all of you and The Scripts

Now a new Problem for you people

How to make reports in VB that can display pictures ,,,

I want to recall you all that it is a Gate Pass generation system so I have to make the pass immediately with/after storing the data in database


Thank You
have a nice day

[Killer42]

Thanks for letting us know how it went.

However, I'd ask that you please post this as a new discussion thread in the VB forum, rather than continuing the comments on this article.

[douglas06]

I have this code is asp and it gives me error my my database sta in sql

If Request.TotalBytes > 0 Then
req_bytes = Request.BinaryRead(tot_bytes)
'Parse POSTed request JSON
'Parse POSTed request JSON
reqArgsString = BytesToStr(req_bytes)
reqArgsArray = Split(reqArgsString, "&")
For i = 0 To UBound(reqArgsArray)
reqArgsArray(i) = Split(reqArgsArray(i), "=")
Next

'Decode base64 image from JSON POST to BytesArray'
'Decodificar la imagen base64 de JSON POST a Bytes Array'
base64Encoded = URLDecode(reqArgsArray(1)(1))
base64Decoded = decodeBase64(base64Encoded)
'Response.Write base64Decoded
'' GoTo Done


'DB Connection Object
'Set cn = Server.CreateObject("ADODB.Connection")
'ConnStr="DRIVER=SQL Server;SERVER=161.196.101.140;UID=sa;PWD=P@ssw0rds ql;Database=ContinuumDB"
'cn.Open ConnStr
'Dim strQry
'strQry = "INSERT INTO GA_BLOB (""Blob_Segment"", ""Blob_Person_ID"") VALUES (?, ?)"

' strQry = "INSERT INTO GA_BLOB (""Blob_Person_ID"", ""Blob_Image_ID"", ""Blob_Segment_No"", ""Blob_Format"", ""Blob_Segment"") VALUES (?, ?)"'

'"insert into GA_BLOB (Blob_Person_ID, Blob_Image_ID, Blob_Segment_No, Blob_Format, Blob_Segment) values ('" & Request.form("Blob_Person_ID") & "', '" 0 "','" 0 "','" 0 "', '" & Request.form("Blob_Segment") & "')"'

'Dim cm As ADODB.Command
'Set cm = New ADODB.Command
'cm.ActiveConnection = cn
'cm.CommandText = strQry
'cm.Parameters.Append cm.CreateParameter("@Blob_Segment", adVarBinary, adParamInput, 100, base64Decoded) 'Leave this parameter first
'cm.Parameters.Append cm.CreateParameter("@Blob_Person_ID", adInteger, adParamInput, , reqArgsArray(1)(1))
'cm.CommandType = adCmdText
'cm.Execute