Who's Afraid of the Big, Bad BLOB?

Because many developers want to spice up database applications by adding graphics and sounds, modern database applications need to handle graphic and sound data as well as traditional character and number data. Fortunately, graphic and sound data are simply a type of specially formatted binary data called binary large objects (BLOBs). As the name suggests, this type of data can be large. As an example of graphical data, Screen 1 shows the graphical logo data in the pub_info table of SQL Server 7.0's sample pubs database.

SQL Server uses the Visual Basic (VB) Image or LongVarBinary data type to store binary image data in tables. You can use the GetChunk and AppendChunk methods, which the ADO Field object provides, to access the binary data in SQL Server columns. GetChunk and AppendChunk are necessary to handle BLOBs' size: Unlike standard text and numeric data, which you can set and retrieve in one operation, binary data is so large that you must access data a bit at a time (i.e., in chunks). GetChunk retrieves a chunk of binary data from an ADO Field object, and AppendChunk adds a chunk of binary data to an ADO Field object.

Exercise caution when you access binary objects. Although BLOBs won't affect performance with local database access, BLOBs can cause performance problems with low-speed LAN links to remote databases.

Using GetChunk

The BinaryData subroutine in Listing 1 uses the GetChunk method to retrieve and display binary data in an MSFlexGrid. Because this subroutine accepts an ADO Connection object as a parameter, the calling program must create and open an ADO connection object before calling the BinaryData subroutine. The BinaryData subroutine begins by creating an ADO Recordset object that contains the pub_id and logo columns from the pub_info table in the Pubs database (the sample database that comes with SQL Server 7.0). This data seems standard; however, the logo column is an Image data type and contains a graphical image for each publisher's logo.

After the subroutine opens the Recordset object, the code initializes the grid that SQL Server will use to display the binary data. The subroutine uses the ColWidth property of the two grid columns to set the initial column size. Because the first column is a standard character data type, the ADO Field object's ActualSize property specifies the column width. However, because the second column contains graphical data, the ActualSize property will not provide the length necessary to display the data. The subroutine sizes the second column for 200 characters to ensure that the column is large enough to display the binary data.

Next, the subroutine adds to the grid's RowHeightMin property to increase the height for the entire row in the grid (in this example, the subroutine triples the original row height). After the subroutine sets up the grid, a For Each loop assigns the grid's column headings. The For Each loop iterates through the Field objects in the Fields collections and uses the fld.Name property of each ADO Field object to assign names on the grid. Column headings for binary data types are no different from standard-character column headings.

Next, a Do Until loop reads all the rows in the Recordset object. A For Each loop retrieves the column data for every row and puts it in the grid. Because programs must handle binary data differently from text data, the subroutine checks the Type property of each Field object before moving the data to the grid. If a Field object contains binary data, the Field object's Type property will equal the constant adLongVarBinary. The subroutine in Listing 1 will use the BintoFile subroutine (which you see in Listing 2, page 53) to convert this data and then place the data into the grid's CellPicture property. Otherwise, the subroutine will treat the data as standard text and assign the Field object's Value property (i.e., fld.Value property in Listing 1) to the Grid.Text property.

The BintoFile subroutine in Listing 2 assembles the contents of a binary column into a byte array and writes that array to a temporary file bitmap. The LoadPicture function can then assign the bitmap file to the CellPicture property.

As the first parameter of Listing 2, the BintoFile subroutine accepts a String variable that contains the name of a temporary data file. As the second parameter, the subroutine accepts an ADO Field object. The subroutine then checks the current directory for the existence of the file that the first parameter named. If the subroutine finds this file, the Kill statement deletes the file, and the Open statement creates a new temporary file. Note that the subroutine opens the new temporary file in binary mode.

After the subroutine creates the temporary output file, it sets the chunk size to a maximum of 32KB and checks the ADO Field object's ActualSize property value. If the data in the Field object is smaller than the maximum chunk size, the subroutine adjusts the chunk size. If the data is larger than 32KB, the subroutine will retrieve the binary data in 32KB chunks. A Do loop extracts each chunk of binary data and places it into a byte array. After the GetChunk method copies all the binary data into a byte array, a Put statement writes the binary data to the open temporary file and closes the file. Other functions can reuse the binary data in this temporary file, or you can delete the temporary file.

Handling Sticky Data Types

This article shows that ADO and SQL Server can handle BLOBs as well as they handle traditional text and numeric data types. SQL Server must use the GetChunk and AppendChunk methods to piece together binary data types. Although dealing with binary objects is more difficult than handling standard data types, the ability to add graphics and sounds to your database applications makes the effort worthwhile.