473,890 Members | 1,358 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

The BLOB (not the movie!)

ADezii
8,834 Recognized Expert Expert
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
Jan 4 '08 #1
55 24844
missinglinq
3,532 Recognized Expert Specialist
Loved The Blob (the movie, not the Access thingie!) so I changed the status of this thread to "Stuck" so it wouldn't get lost in the shuffle!

Linq ;0)>
Jan 4 '08 #2
ADezii
8,834 Recognized Expert Expert
Loved The Blob (the movie, not the Access thingie!) so I changed the status of this thread to "Stuck" so it wouldn't get lost in the shuffle!

Linq ;0)>
Thanks linq, I'd do it myself but don't have the access.
Jan 4 '08 #3
ADezii
8,834 Recognized Expert Expert
Just an interesting side note on this Topic of storing Graphic Images as BLOBs. In some elementary testing, I found that with each Graphic insertion, the overall size of the Database grew by almost exactly 2X the size of the Graphic Image. This seems to be independent of the actual Graphic Type (*.bmp, *.jpg, *.ico). A simple Table will demonstrate my findings:

Expand|Select|Wrap|Line Numbers
  1. DB Size      Graphic      DB Growth      End DB Size
  2. 500 Kb       100 Kb        200 Kb           700 Kb
  3. 700 Kb        50 Kb        100 Kb           800 Kb
  4. 800 Kb       300 Kb        600 Kb         1,400 Kb
  5. etc.
  6.  
Jan 5 '08 #4
MMcCarthy
14,534 Recognized Expert Moderator MVP
OK I've had the file size limit increased for zip files and attached the database. I deleted the last paragraph of your article accordingly. You may want to go back and add a couple of lines though.

Mary
Jan 8 '08 #5
ADezii
8,834 Recognized Expert Expert
OK I've had the file size limit increased for zip files and attached the database. I deleted the last paragraph of your article accordingly. You may want to go back and add a couple of lines though.

Mary
Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).
Jan 8 '08 #6
MMcCarthy
14,534 Recognized Expert Moderator MVP
Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).
I have no problem with that.
Jan 8 '08 #7
FishVal
2,653 Recognized Expert Specialist
Thanks Mary, if it's OK with you I'd like to keep this Tip running for 2 weeks since I honestly think that it will generate a lot of interest, and besides, I put a lot of time into it. (LOL).
Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.
Jan 8 '08 #8
ADezii
8,834 Recognized Expert Expert
Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.
Hello Fish, always good to hear from you. The synchronizing problem, in my opinion, is understandable given its context as far as what is involved in Record Navigation and large *.jpgs. For each movement to another Record:
  1. A Recordset must be created to see if an associated Image actually exists for the Current Record.
  2. Assuming the Image exists, the Binary Data representing the Image has to be extracted from the OLE Object Field and written to a Stream Object.
  3. The data is then saved to a Temporary File in a Temporary Folder with the proper Extension.
  4. Finally, the Image is loaded into the Picture Property of the Image Control.from the Absolute Path created in the previous step.

Given their much larger size, I've found much better success using Images saved in *.bmp Format as opposed to *.jpg when navigating through Records. It appears as though the larger size does not significantly impact the load time, and you never get that annoying progress Dialog when a *.jpg is loaded. I'm really not sure why this occurs, but I assume it has something to do with the compressed nature of *.jpgs.
Jan 9 '08 #9
ADezii
8,834 Recognized Expert Expert
Hello, ADezii.

It's a very interesting issue and I'll be happy to discuss it. Moreover, just now I'm working on implementing BLOBs storage in Access db.
Storing/retrieving BLOBs is not a very challenging problem. The real trick is with form control suitable to represent the stored image.
Actually I don't like native Access Image control - it is slow and don't accept many picture formats. MSForms.Image is somewhat problematic too. So far I use WebBrowser control which is considerably fast and accepts a bunch of different image formats.
So far the code is quite unstable (endless issues of synchronizing Form events and ActiveX control), but I hope I'll work it out in a couple of days. I'll send you a sample if find it interesting.

Regards,
Fish

P.S. Note that your db still has Image control to form events synchronizing problem. It can be noticed with large jpegs and "too fast" browsing through the records. Though error is trapped, the picture for the previous record remains on the control while the form is focused on the next record.
Just as another side note, Fish, implementing BLOBs in DAO seems to be mpre complex since there appears to be no intermediate Stream Object involved. The Image File must be opened in Binary Mode, data Read from it, then written to the OLE Object Field storing the Image Data in Chunks.
Jan 9 '08 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
6129
by: Carolyn Longfoot | last post by:
Help! This is driving me crazy... I'm trying to read a BLOB from a db and display it in a browser, like so: $query="SELECT blob from table where blob_id=9"; $result=mysql_query($query); $blob=mysql_fetch_assoc($result); Now when I do print_r (array_values($blob));
0
7494
by: sandeep G | last post by:
I've a table which has a number & a blob column, both of which are NOT NULL type. This table is composite partitioned using range & hash on the same column. Each partition is sub partitioned into two. Now if I try to move the partitions to a different tablespace I get an oracle error sayiing that ERROR: ORA_14257 cannot move partition other than a Range or Hash partition Now is there any other way to move the partition to another
1
3016
by: konsu | last post by:
Hello, I am planning to implement an image and movie library (e.g. a photoalbum), and one of the ways that I see to store image and movie files is to put them in to BLOB fields in an sql table. I would greatly appreciate any information as to how this method compares with simply storing the images and the movies as plain files on disk. Does MySQL add a lot of overhead when retrieving images from BLOBs compared to reading the files from...
1
3336
by: torbs | last post by:
I have a problem when I use javascript to get the length and position of a movie I recieve from a rtsp stream. The length and position is extremely high and not the actual length and position of the movie. When I try Real Player and it's methods I recieve a length and position of 0.
4
2566
by: Claus Konrad | last post by:
Hi What's the better way for retrieving a very large BLOB field from a SQL 2000 (image-field)? I'm currently usign a SqlDataReader, but it times out before all data is retrieved from the table field Is there any good approach for obtaining parts of the field (like Reader.GetBytes(...))...??
4
7970
by: aaggarwal | last post by:
Hi All, I'm working on DB2 UDB 8.2 with Fixpack 10 on Windows 2000 Server. The problem I'm facing is that I have created a table having BLOB type column with NOT LOGGED option. (see script below) CREATE TABLE ATTACHMENTS ( CASEATTACHMENTID INTEGER NOT NULL,
2
6555
by: Vinciz | last post by:
hi guys... im new in java and i would love to learn some of these... basically i got a sample code to retrieve the blob from the mysql. however, i dont really know what to do with these retrieved byte/binary data as i got no idea on how to save them in our pc. For this situation, what i need to do is give the byte/binary data an extension (retrieved from another field in the table) in order to revert back to the original data i had in the...
8
3756
by: virtuoso | last post by:
Hi, I'm a beginer in c sharp and I can't pass over a problem. I would like to bind a blob field (mysql) with a picturebox in c sharp; here is the code: string query = "Select name,director,picture from Movies where Movies.name='" + search + "'"; //create command and assign the query and connection from the constructor MySqlCommand cmd = new MySqlCommand(query, connection); //Execute...
0
9978
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9822
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10819
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10462
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5851
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6045
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4676
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4271
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3277
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.