Note: Adding BLOBs to a database can have severe performance impact, if you are thinking of adding lots of binary data it would behoove you to look instead into using an OLEControl so that you can link the external files to the database, this will keep the size of your database smaller and help the performance of non-BLOB activities and maintenance routines. However, while the situations may be limited, there are a few valid reasons for putting the raw data into a database and if you find yourself in one of those situations then here is your code.
It's a multimedia world and we should be able to put media (Wav files, MIDI files, Graphics files, AVIs and Quicktimes) into and get them back out of our databases.
Since Access1.0, the Microsoft Northwind Riggers demo database (NWind.mdb) has shown how to display OLE Bitmaps on an Access form, and with VB we can display graphics very simply by binding a data control to a recordset, binding an image or picturebox to an OLE (picture) field in that recordset and letting the user view the images.
But the task of getting the image into the database in the first place has never been easy from a developer's point of view. With Access, the user can open a table view and drag-and-drop a file into an OLE Object field. For VB development, where we are supposed to be making databases easy for the user, this is not an intuitive process to force upon users.
Also, datacontrols are really not the best way to design an app. They take up a lot of resources and are very inflexible, if you're doing a front-end against a shared database over a network you should try to minimize the use of data controls wherever possible (each standard data control can take up a minimum of 3 database connections to just for doing displays .. this is not good for your app's performance!)
The answer to the problem lies in using AppendChunk to put media files into a database and GetChunk to get them back out as physical temp files that you can use for LoadPicture and other display/playback.
This demo program shows a simple media database utility. (We did not concentrate on making a pretty GUI, it's only to show code). As a demo, it has minimal error coding but it shows the following techniques:
- How to use AppendChunk put a media file into a Jet database OLE Object field as a BLOB (Binary Long Object type)
- How to use GetChunk to copy the object out of the database and into a temporary file so that standard VB display techniques (techniques that require a physical file) can be run.
- How to use PaintPicture to resize a graphic to fit inside a thumbnail area while retaining the original file's aspect ratio.
- For the demo's thumbnail max area we use a rectangular shape control. No matter what the original graphic's size and height/width ratio, the display will be adjusted to fit inside the shape's boundaries. You can see this neat logic in action by resizing the Shape1 control in design mode (make it short & fat, tall & thin, etc) and running the project
- If you don't need to print and just want to place a graphic with the correct aspect ratio within a certain thumbnail area, use just the resize routine against an image control (good lightweight way to show transparent gifs on a form)
- This technique can be easily adapted for use in printed manuals, brochures and catalogs. Use it to run PaintPictures on the Printer object so that all graphics are printed within a certain area of the printed page with their aspect ratios intact.
As-is, the demo works with Graphics (bmp, ico, gif, jpg), audio (wav) and video (avi). You could use it for any binaries by adding to the enumeration and commondialog filterindex validation.
If this were a real app we would probably use MCI for more control over active Multimedia. Here, we simply ShellExecute the files so that your default media player will start the playbacks ... remember, because of ShellExecute you will have to manually close your media player before switching between non-graphic files (the media player holds files open as long as the player is active and that means that the code can't write over the temp file).
Also, a real app would have to do file cleanup on the exported files. There are as many ways to work with tempfiles as there are programmers so we'll let you handle cleanup in your favorite style.
To use, create a new folder on your machine and unzip the download to that folder. (VB5/DAO, with standard MSFlex grid and a demo datafile. If using VB6 simply open the project and resave as VB6)
click here to get! (just 59k!)
Hope it helps!
Robert Smith
Kirkland WA
added to smithvoice july 1999