ADO Field Object Methods

[Previous] [Next]

The two methods available on the Field object are AppendChunk and GetChunk and are described in the following table. These methods are designed to help you work with large string and binary data type fields.

Field Object Methods
Method Name	Description
AppendChunk	Appends data to a large string or binary field
GetChunk	Retrieves data from a large string or binary field

Before we talk about using these methods, let's talk a little bit about the large string and binary data types. These data types have different names depending on what documentation you're reading. ADO uses adLongVarChar to designate long strings, adLongVarWChar for long Unicode strings, and adLongVarBinary for long binary fields. SQL Server uses the names text, ntext, and image for the same data types, respectively. These fields can store large amounts of string or binary data, which is usually stored separately from the rest of the data in your table. They're often called BLOB fields (for binary large objects, not because they slither through air ducts to attack teenagers at movie theaters).

Similarly, the string or binary data is often retrieved separately from the rest of the data in the results of your query. Traditionally, you don't know how much data is contained in such a field. Retrieving this data separately allows you to save time and bandwidth by not retrieving the contents of BLOB fields that you won't ultimately examine in your code. To retrieve this data separately from the rest of the data in your query, you need to make sure the table's primary key is part of your query and that the BLOB fields are the last fields referenced in your query string.

Before visions of BLOBs dance in your head, remember that databases are not file servers. If you want the best possible performance from your database system, don't use it to move large pieces of data that could be better handled by your operating system. Instead, use your database to store the name and location of the file that contains the desired data. Yes, storing this data in your database can simplify your data access code and make backups easier, but that's no excuse for choosing poor performance.

Unfortunately, the code samples that demonstrate the use of long binary data that are included with Access versions 2, 95, 97, and 2000, and with SQL Server 7, further confuse most developers. The Northwind database that has shipped with Access for the past few versions contains bitmaps in the Photo field of the Employees table. To make it easier to use this data, Access wraps this data in its own proprietary header format that allows you to simply double-click on a field marked as an OLE Field, and Access launches the appropriate application that will display and edit the data in that field. Unfortunately, the structure of these headers has never been documented.

Visual Basic users have placed large amounts of data into Access OLE Fields only to find that they could not retrieve this data as easily as they'd hoped. There's a small sample application called RemoveAccessHeaders on the CD included with this book that can strip the OLE headers in Access from data stored as an OLE Field and store the contents of the file instead. It's effective, but painful and inelegant.

SQL Server 7 also shipped the Northwind database as a sample database. The structure is almost identical to that of the Northwind database in Access, but the Photo field in the Employees table contains the same Access header. Maybe this is a good thing. Developers who want to store files in their database might try to build an application that retrieves these images, not realizing that the data is a bitmap wrapped in an Access header and consequently they won't be able to view the image in their application. With any luck, this discovery will encourage them to store the filename in their database instead of the contents of the file.

Let's return to the AppendChunk and GetChunk methods. If you're using a server-side Recordset, the BLOB data won't be retrieved until you request it. If you're using a client-side Recordset, the ADO Cursor Engine retrieves all BLOB fields when you open the Recordset. By retrieving all this data ahead of time, you can close your connection to the database and still access the contents of the BLOB fields. With most OLE DB providers, you should be able to use the Value property on the Field object to retrieve and modify its contents rather than using GetChunk and AppendChunk.

If you want to determine which fields you can use the AppendChunk and GetChunk methods on, examine the Attributes property of each Field object and test for adFldLong.

NOTE

---

ADO 2.5 introduced a Stream object that can simplify the process of moving data from files to databases and back. If you've decided to store the contents of files in your database (despite my recommendation against doing so), you can avoid the cumbersome AppendChunk and GetChunk methods by using the Stream object. Chapter 6 contains some fairly simple sample code that shows how to use the Stream object in such a scenario.

AppendChunk Method

Use the AppendChunk method to place data into a BLOB field. This method has one parameter:

• Data This parameter accepts Variant type data you want to append to the desired field.

The initial call to the AppendChunk method will overwrite the current contents of the field. Successive calls to the method without moving off the current record will continue to add data to the field.

GetChunk Method

The GetChunk method retrieves data from a field and returns it to a Variant. It accepts one parameter:

• Length This parameter is a long data type. It accepts the number of bytes of characters you want to retrieve.

To determine how much data there is to retrieve in a particular field, check the ActualSize property of the field prior to calling GetChunk. If you request more data than remains in the field, ADO will return only the data remaining. So, if the ActualSize property returns -1 and there is no way to determine how much data exists in your BLOB field, you'll have to retrieve the data in chunks and examine what has been returned. Testing the size of the variable that holds the data returned by the GetChunk method and comparing that size to the amount of data requested will help you determine when you've retrieved all available data for the field.

It's worth noting that as of ADO 2.1, when you retrieve string data the ActualSize property returns the number of bytes rather than the number of characters in the field, while the Length parameter of the GetChunk method designates the number of characters to return rather than the number of bytes.