13
Wed, Nov
5 New Articles

Invasion of the BLOBs

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.


Invasion_of_the_BLOBs07-00.png 444x201

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: