The Recordset is an extremely flexible object. In addition to working with data of various types, it supports the retrieval and manipulation of raw binary data stored in a database. This can be especially valuable if our database contains items such as graphical or sound data.
It's important to note that although BLOB (Binary Large Object) data can be retrieved from a database, it is not wise to store all of the graphical images on the Web site in the database. SQL Server, or Access for that matter, was not designed to process and work with this type of data. Typically it's far more efficient to store BLOB information as normally, as .gif or .wav files, along with our Web pages.
BLOB data manipulation comes in handy when we have graphics that are supplied from other sources, and are typically dynamic in nature. For example, a workflow imaging system may track document images across various users, and we may need to display some of these images within a dynamic Web page. To do this, the GetChunk method of the Field object is used. Let's take a look at an example:
ID = Request.QueryString("ID")
BlockSize = 4096
Response.ContentType = "image/JPEG"
strQuery = "SELECT * FROM Blob WHERE Blob_ID = " & ID
Set oRS = oConn.Execute(strQuery) 'oConn is a Session level object
oRS.MoveFirst
Set Field = oRS("Blob")
FileLength = Field.ActualSize
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
Response.BinaryWrite Field.GetChunk(LeftOver)
For intLoop = 1 To NumBlocks
Response.BinaryWrite Field.GetChunk(BlockSize)
Next
oRS.Close
Make sure the BLOBs are the last things in your SELECT statement. Currently they don't work if they are not the last field(s).
In this example, a jpeg image has been stored in an Image field within our SQL Server database. We're retrieving it from the database, and displaying it on our Web page. SQL Server supports several binary data types. Two of these, Binary and VarBinary are limited to a maximum of 255 characters. In addition, SQL Server supports an Image type that stores data as 2KB increments of binary information. This type meets our need for this example.
Our first step is to obtain a key that references the binary object in which we are interested. Here, we will use an ID that is selected by the user on a previous page. In a real world scenario, the previous page would include a number of “thumb nail” pictures, that when selected would call this page and pass the appropriate ID for the image required.
A BlockSize value is used to determine how much data will be read from the data source at one time. Take care when setting this value. It is best to use a value that is a multiple of the field increment size to avoid an excessively large or small leftover chunk. It may seem logical to read the whole image as a single chunk, but keep in mind that we need to move this data from our server to our Web site in a single transaction, a process that can be very resource intensive with large files.
The next step is to define the type of data that will be displayed on the page. This is done using the ContentType property of the Response object. Then, with our initial values set, we can define a query to retrieve the data. Using the Execute method of the Connection object, we submit the query and return a Recordset containing the query results. To verify that the data set is populated, we call the MoveFirst method of the Recordset object.
Now we are ready to actually read the data from our Recordset. To do this we define an object variable representing the BLOB field. In this example we use:
Set Field = RS("BLOB")
The object variable Field now has a reference to the actual binary data. Our next step is to calculate how much data we need to retrieve in terms of the block size defined earlier. We do this by retrieving the actual size of the object (using the ActualSize property as you might guess), and then dividing that value by our block size variable to determine the number of chunks our data can retrieved in. As our block size may not be evenly devisable by the size of the image, a LeftOver value is calculated to determine any partial chunk of data that needs to be retrieved:
FileLength = Field.ActualSize
NumBlocks = FileLength \ BlockSize
LeftOver = FileLength Mod BlockSize
Using the BinaryWrite method of the Response object, we can output a chunk of the data read from the data source with the GetChunk method. GetChunk is design to read an unstructured binary data stream from an object of a given size. Here, we initially retrieve the extra bytes (if any), and then read blocks of data of the size defined, and write them to the Web page:
Response.BinaryWrite Field.GetChunk(LeftOver)
For i = 1 To NumBlocks
Response.BinaryWrite Field.GetChunk(BlockSize)
Next