One of my favorite computer acronyms is BLOB. Whenever I mention the term, I always get strange looks and the inevitable question, “What’s a BLOB?” Although many in the AS/400 world aren’t familiar with BLOBs, chances are BLOBs will gain popularity in the near future as they permeate new applications.

BLOB stands for binary large object and is used to store large binary data, usually without a fixed length, such as video streams, images, audio, and the like. This article is designed to show you how to use BLOBs in a client/server environment with Microsoft Access.

BLOBs are just one of three large object (LOB) data types added to DB2 for OS/400 in V4R4. Others in the LOB family are the character large object (CLOB) and the double-byte character large object (DBCLOB) which are used to store long text strings.

BLOB Survival Requirements

If running V4R4, make sure your OS/400 database is at PTF level 11. You’ll need the V4R5 version of Client Access Express for Windows. (V4R4 Express with the January

2001 service pack should work also.) Access 2000 will be used here, although appropriate substitutions can be made for Visual Basic and Access 95/97 developers. Knowledge of Visual Basic for Applications (VBA) is necessary, along with ODBCDirect or ADO.

The first step to using BLOBs on the AS/400 is to create a table with one or more columns defined with the new BLOB data type. The syntax of the BLOB data type is BLOB(maximum size). Figure 1 shows a sample SQL Create Table statement that incorporates the BLOB data type. You must use SQL to create a BLOB table, because DDS does not support any of the large object types.

Notice the picture and sound columns have a data type of BLOB(1M). This means these columns can hold up to one megabyte of binary data. When specifying BLOB sizes for a field, the maximum data size can be given in bytes or abbreviated and suffixed with a K (kilobytes) or M (megabytes.) In V4R4 and V4R5, the maximum BLOB size is 15MB, but this may increase in future OS releases.

BLOBs Belong in Business

The AS/400 is a business machine. To those who program on the AS/400, what good are things like images, sounds, and videos stored in BLOBs? To illustrate the usefulness of BLOBs, consider this programming project for an imaginary consulting firm.

A small chain of hobby stores called The Yardmaster has hired you. The main store has a Microsoft Access database that stores pictures of toy trains and the sounds they produce. But, the Access database is getting large and clumsy. Further, the data in the database must be shared with the other stores—a load that will push Access over its limits. An AS/400 will replace the dying Access database. However, The Yardmaster likes Access' multimedia capabilities, so your task is to continue using Access as the application's front-end while storing the data on the AS/400.

The techniques I will present deal with two tasks: moving BLOB data from Access to the AS/400 and retrieving the data from the AS/400 into Access using linked tables.

Oozing BLOB Data to the AS/400

Your first task is to place Yardmasters’ Access tables on the AS/400. Copying data from an Access table to the AS/400 is easy when you use Access’ export feature. Unfortunately, the export feature doesn’t work for tables that contain BLOBs.

Therefore, Access’ two main methods to export the data use SQL or VBA code. Either way, you’ll need to create the table manually on the AS/400. In Figure 1, you can see the Create Table SQL statement needed to create a shell of Yardmaster’s item master table.

Once the empty table is created on the AS/400, you can export your data. Figure 2 shows an SQL statement that will run a distributed query to insert data from Access’ tblItemMaster into the AS/400 ITEMMASTER table in library YARDMASTER. This SQL statement uses a special IN clause which is unique to Access’ SQL. The clause is used for exporting Access data to another platform. This SQL does nothing more than act as an Append query to an ODBC-linked table.

The syntax of the IN clause for an ODBC export is shown with a blank parameter (two double quotes) followed by a second parameter with the ODBC constant and a data source name (DSN). In this example, our ODBC DSN is EXPRESS. Replace the data in the second parameter with your own DSN name, user ID, password, and any other DSN parameters you need. This SQL statement can run as a Query object, in a Macro with the RunSQL action, or in VBA code using the DoCmd.RunSQL method.

Because no fields are given in the INSERT statement and because the * (all fields) is used with the SELECT statement, this SQL assumes that the two tables are compatible in their number of columns and data types. If they’re not compatible, individual fields would have to be specified on the INSERT and SELECT statements.

Using VBA to Copy BLOB Data

In case you need to use VBA code to export your data, Figure 3 (page 94) shows the VBA code required to copy the records from an Access table to the AS/400 using ADO and the Client Access ODBC Driver. (An example using ODBCDirect for Access 97+ and VB 5.0+ is in the downloadable code available for this article at www.midrangecomputing.com/mc.) Again, this code assumes that the AS/400 table already exists and has columns that are compatible with the Access table in name, data type, and number. If the column names aren’t the same between your tables, you’ll need to reference the fields explicitly.

Remember to place your own user ID, password, and other options in your DSN string, and be sure that the ADO 2.x library is included in your references. If you’re using ODBCDirect, make sure the Data Access Objects 3.x library is referenced.

The main body of the export code is simple: The tables are opened, a row is read from the Access table, the fields are copied, and, finally, the row is written to the AS/400. However, a small quirk occurs when a BLOB (or other large data type) column is

encountered. A field is considered a large object (LOB or long) data type if the adLongField bit of the attributes property is set to True. With long data types, it is not permissible to set the value of a long field in one table to the value in another. The usual rsAS400!Blob = rsAccess!Blob VBA code will not work. Therefore, when a BLOB or other long column is encountered, the CopyLargeField_ADO subroutine is invoked.

Why do you need special logic for processing BLOB and other long columns? Since BLOB data, unlike other data types, may be several megabytes in size, the data for the entire column may not be able to be stored in a single variable due to programming language or memory constraints. In order to access all of the data, it must be processed in chunks.

ODBCDirect and ADO field objects have .getchunk and .appendchunk methods that are used for reading and writing BLOB data in pieces. In the CopyLargeField_ADO subroutine, the .getchunk method is used to retrieve data from the Access column in 64K chunks, and .appendchunk appends these chunks to the AS/400 BLOB column. Notice a loop is implemented to continually read in 64K chunks until the entire BLOB is copied.

Getting AS/400 Data Back into Access

Now that you’ve moved Yardmaster’s data tables to the AS/400, how do you get the data back into Access? The easy answer lies with linked tables.

To link a table in Access, choose File/Get External Data/Linked Tables, select your AS/400 ODBC data source (making sure your data source contains the library with your BLOB tables), and select your tables from the list. When your item master table is linked, it will appear in the database window with a globe icon next to it, signifying that the table is linked to an external database. By default, Access prefixes the table name with the AS/400 library. Place the linked item master table in design view. When the design view appears, you will notice the PICTURE and SOUND fields have a data type called OLE Object. This is Access terminology for a BLOB.

Now that you have linked the table, you can report against it. Figure 4 (page 94) shows a picture of the item master maintenance form for Yardmaster. This form was created primarily by Access’ form wizard. You can create one very similar to it by selecting the linked item master table from the Tables tab of the database window and choosing Auto Form on the Insert menu.

As you can see, Access can interpret the BLOB field as a picture. The tool box control used to show this picture and all other BLOBs (OLE Objects) is called a bound object frame (BOF). The bound object frame control differs from the image control in that the BOF control can display all types of BLOB information, while Access’ image control can only display pictures.

The sound box on the form (also a BOF) shows a speaker icon to indicate that a sound clip is available to demonstrate the sound effects for this item. The BOF supports OLE Automation; double-clicking the picture will start a paint application and double- clicking the sound icon will play the sound. Remember that BOFs can only display BLOB data that was originally inserted through the BOF control.

So how do you insert or change images and sounds on the form? Place your cursor on the picture or sound BOF. Choose Insert/Object or right-click on the BOF control and choose Insert object. When the Insert Object dialog box appears, choose the Create from file option. Choose Browse, select a path for a picture or sound file, and click OK. Alternatively, if you have a sound or picture on the clipboard, you can paste it here by choosing Edit/Paste. By the way, Access doesn’t validate the object being inserted, so any file type can be inserted here.

BLOBs Come in All Shapes and Sizes

One issue I haven’t yet covered is how to determine the maximum size for the BLOB columns in our table. The answer depends on the mechanism you use to store data in your

BLOBs. For example, if you’re storing the contents of a file directly in a BLOB, then your maximum BLOB size is just your anticipated maximum file size. In this article though, I’ve only covered inserting data into BLOB columns via the bound object frame control.

Estimating the amount of room it will take to store a BLOB via the BOF can be tricky. Nevertheless, the following guidelines should prove helpful:

• Pick a single file type for each type of data, if possible. If you’re storing pictures, stick with one picture format (.jpg, .bmp, or .tif).

• Create a dummy table in Access with an OLE Object column called TEST. Then pick three or four of your largest images and insert them into your table.

• Create a query in Access over your dummy table. Use the VBA length function LEN() to determine the size (in bytes) of your

BLOBs: SELECT LEN(TEST) * 2 FROM DUMMYTABLE. The BLOB length is multiplied by 2 because LEN() misreports the BLOB size; this is because it thinks that the BLOB is a Unicode structure. (Note that the SQL/400 LENGTH() function will return the length of a BLOB in an AS/400 table.)

• Choose your largest BLOB size, multiply it by 1.5 to be safe, and round up to the nearest 100K. This is your maximum size for defining an AS/400 BLOB to be used with the BOF.

It is better to err on the larger side. While it might waste space, this will prevent truncation errors. Further, you can always examine your tables at a later date and issue the ALTER TABLE command to reduce the size of your BLOB column.

The Cost of Ease

SQL lets you quickly move the data from Access to the AS/400, and linked tables let us bring the data back in a snap. The BOF control allows all sorts of Windows-related data to be inserted into a BLOB field from a file or from the clipboard. All of this is done with little programming.

Now here’s the bad news: When using the bound object frame to work with BLOBs, Microsoft Access introduces mega resource overhead. For example, the picture in Figure 4 is stored on my hard drive in both .jpg (compressed, 34K) and .bmp (bitmap, 214K) formats. I inserted both images into the BLOB table via Access’ BOF and found that the 34K compressed image took about 664K as a BLOB while the uncompressed 214K bitmap only required about 218K to store as a BLOB. Therefore, the space-saving benefits of compressed images are lost. A second problem with the BOF is that BLOB data is stored in a proprietary Microsoft format—only Windows machines can access it.

Another downside of using linked tables containing BLOBs is Access retrieves image data for every record, regardless if the data is needed. The typical row size of a table may range between 200 bytes and 4K. But, if you add two 1-MB BLOBs to the size of the row, each row retrieval is now pulling up to 2MB of data. This is not an ideal situation for a lightweight client/server app. In the Yardmaster scenario, this row size would be grievously slow for remote locations attempting to get the data from the AS/400. I find my average BLOB size to be about 300K, which can make navigating from row to row in the Access form somewhat slow.

The BLOBs Are Creeping In

I hope this article has inspired some thought on how BLOBs can be incorporated into your own applications. Although very resource-intensive, Access is a good tool to get you

grounded in working with BLOBs. As binary data grows more critical to everyday business use, expect to see BLOBs inch their way into your AS/400 environment.

REFERENCES AND RELATED MATERIALS

• “AS/400 Client/Server Programming with ADO, Microsoft Excel 2000, and OLE DB,” Michael Sansoterra, AS/400 Network Expert, May/June 1999

• “Configuring 32-bit Client Access/400 ODBC, Part 1,” Shannon O’Donnell, Client Access/400 Expert, September/October 1998

• “Configuring 32-bit Client Access/400 ODBC, Part 2,” Shannon O’Donnell, Client Access/400 Expert, November/December 1998

• DB2 UDB for AS/400 Object Relational Support, Redbook (SG24-5409-00)

• “Links, Imports, Exports: Using ODBC to Share OS/400 Data with Microsoft Access,” Michael Sansoterra, AS/400 Network Expert, September/October 1999

• “More AS/400 Client/Server Programming with ADO and VBA,” Michael Sansoterra, AS/400 Network Expert, July/August 1999

• “Not Able to Adopt ADO? Why not use ODBCDirect?” Michael Sansoterra, AS/400 Network Expert Web Edition, October 1999

• “Turbocharging ODBC for Client/Server Performance,” Howard F. Arner, Jr., Midrange Computing, December 1998

Create Table Yardmaster/ItemMaster (

ItemNo Char(15), /* Item Number */

Description Char(30), /* Description */

Class Char(5), /* Item Class */

Cost Dec(11,2), /* Item Cost */

Price Dec(11,2), /* Item Price */

MfgID Char(10), /* Mfg ID */

Picture Blob(1M), /* Picture */

Sound Blob(1M), /* Sound Effect */
Primary Key (ItemNo))

Figure 1: This table definition contains two columns (picture and sound) that use the BLOB data type.

INSERT INTO [YARDMASTER.ITEMMASTER]

IN "" "ODBC;DSN=EXPRESS;UID=USER;PWD=PASSWORD"
SELECT * FROM tblItemMaster

Figure 2: This is an Access distributed query used to copy data from the Access item master table (tblItemMaster) to the AS/400 table (YARDMASTER/ITEMMASTER).

Option Explicit
Option Base 1

Function ExportBLOB_ADO()
'

' Copy data from Access table with BLOBs
' to AS/400 table
'

'

' NOTE: Make sure the ADO 2.x library
' is referenced.

'

Dim cnAccess As New ADODB.Connection
Dim rsAccess As New ADODB.Recordset

Dim cnAS400 As New ADODB.Connection
Dim rsAS400 As New ADODB.Recordset

Dim fld As ADODB.Field
'

' Open Access Item Master Table
'

cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & CurrentProject.Path & _

""" & CurrentProject.Name & ";" & _

"MODE=SHARE DENY NONE"
rsAccess.Open "SELECT * FROM tblItemMaster", _

cnAccess, adOpenForwardOnly
'

' Create AS/400 Connection and Open Table
' Replace your own DSN, User and Password Here
'

cnAS400.Open "Provider=MSDASQL;Data Source=EXPRESS", "USER", "PASSWORD"
rsAS400.Open "SELECT * FROM YARDMASTER.ITEMMASTER", _

cnAS400, adOpenKeyset, adLockPessimistic

Do Until rsAccess.EOF

With rsAS400

.AddNew

For Each fld In .Fields

'

' If field type is "long" (i.e. LOB),

' then use copy subroutine

' to copy the LOB data.

'

If fld.Attributes And adFldLong Then

Call CopyLargeField_ADO(rsAccess(fld.Name),
rsAS400(fld.Name))

Else

fld = rsAccess(fld.Name).Value

End If

Next

.Update

End With

rsAccess.MoveNext
Loop

rsAS400.Close
rsAccess.Close

End Function
Sub CopyLargeField_ADO(fldSource As ADODB.Field, _

fldDestination As ADODB.Field)

'

' Set size of chunk in bytes.

'

Const conChunkSize = 65536

Dim lngOffset As Long

Dim lngTotalSize As Long

Dim lngNoBytes As Long

ReDim bytChunk(1 To conChunkSize) As Byte

'

' Copy the LOB from one field to the other in 64K

' chunks until the entire field is copied.

'

lngTotalSize = fldSource.ActualSize

Do While lngOffset < lngTotalSize

lngNoBytes = lngTotalSize - lngOffset

If lngNoBytes < conChunkSize Then

ReDim bytChunk(1 To lngNoBytes)

Else

lngNoBytes = conChunkSize

End If

bytChunk = fldSource.GetChunk(lngNoBytes)

fldDestination.AppendChunk bytChunk

lngOffset = lngOffset + lngNoBytes

Loop

'

' Set Destination Field to Null

'

If lngTotalSize <= 0 Then

fldDestination = Null

End If
End Sub

Figure 3: The ADO .getchunk and .appendchunk methods are critical for manipulating BLOB data.

Figure 4: Here is the item master maintenance form for Yardmaster. The data shown here is taken from the AS/400.