Use the new ADO 2.6 Stream object to keep BLOBs contained
Editor's Note: This article was adapted from the SQL Server 2000 Developer's Guide (Osborne/McGraw-Hill) by Paul Conte and Michael Otey.
When you're designing and programming databases, images and audio or video clips probably aren't among the first things that you consider storing. However, today's databases often need to store employee photos, product pictures, and the like. Even the ability to play on-demand video clips and MP3 files isn't uncommon for the current crop of database applications. Fortunately, SQL Server can handle these binary files as easily as it does the more traditional integer and character data types. Let's look at how SQL Server treats binary objects, how to use SQL Server to store binary data, and how to use these data types in your applications. You can use the ADO Stream object to import binary data into SQL Server. And you can use an ADO Recordset object to retrieve that data and display it in both a data-bound image control and an unbound Microsoft Hierarchical FlexGrid.
SQL Server's BLOB Data Types
From a database perspective, a binary data object is known as a binary large object, or BLOB. As the acronym suggests, this type of data can take up a lot of space. Using the image, text, or ntext data types, SQL Server can store binary data in tables. Table 1, page 32, lists the attributes of the data types that SQL Server can use for BLOB storage.
The image data type is the primary BLOB-storage data type; you can use it to hold virtually any type of binary data. Text and ntext are much like the Microsoft Access Memo data type and are particularly useful for storing variable amounts of text data. For example, you would use SQL Server's text data type if you needed to store the entire contents of a text (.txt) file. The text and ntext data types also let you search column data for text strings. And full-text indexing, a new SQL Server 2000 feature, enables searching of binary data types by associating the column with its target application.
Besides these natural BLOB data types, a few other SQL Server data types—varchar, nvarchar, and varbinary—might seem to be viable candidates for storing BLOB data because they store variable amounts of data. For the most part, though, these types won't work for BLOB storage. The primary difference between these and the usable data types is the total amount of data that each type can store, as Table 2, page 32, shows. The true BLOB data types that Table 1 shows can hold as much as 2GB of data, whereas the data types in Table 2 can store a maximum of 8KB. (For more information about how SQL Server handles BLOB storage, see the sidebar "SQL Server BLOB Storage," page 33.) Under certain circumstances, you can use the varbinary data type to store BLOBs because, like the image data type, it can store binary data. Although varbinary isn't a true BLOB data type, you can use varbinary fields to hold small binary objects such as icons, wave files, or small bitmaps for which you know the maximum amount of data is less than 8000 bytes.
In many ways, you can treat SQL Server's binary data types—image, text, and ntext—exactly like standard char, varchar, and int data types. Although the binary data types have some limitations that might surprise you at first, when you consider the nature of binary data, they make sense. First, you can't use SQL Server's binary data types as local variables in stored procedures or T-SQL batches. Likewise, SQL Server can't use them as part of an index or in WHERE, ORDER BY, COMPUTE, or GROUP BY clauses. The exception to this restriction is that you can use the text and ntext data types with the LIKE keyword in a WHERE clause.
T-SQL provides a few rudimentary functions for dealing with BLOB data, but the primary mechanism for accessing BLOB data stored in SQL Server is ADO. (For more information about the T-SQL commands that can access BLOB data, see the Web sidebar "T-SQL Functions That Work with Text and Image Data" at http://www.sqlmag.com, InstantDoc ID 20842.) ADO 2.6's Stream object lets you read and write the contents of binary columns. The Stream object is a powerful replacement for the GetChunk and AppendChunk methods, which have existed in ADO since version 1.0. Although GetChunk and AppendChunk are workable, they're awkward to use. (For more information about using GetChunk and AppendChunk to work with BLOBs, see "Who's Afraid of the Big, Bad BLOB?" April 1999.) The Stream object vastly simplifies and streamlines the process of using ADO to access binary data. Figure 1, page 33, illustrates the ADO 2.6 object model that contains the new Stream Object.
Prev. page
[1]
2
3
next page 
