Piping blob data

Blob data is data that is a binary large-object such as a Microsoft Word document or an Excel spreadsheet. A data pipeline can pipe columns containing blob data.

The name of the datatype that supports blob data varies by DBMS. Table 17-4 shows some examples.

Table 17-5: Examples of datatypes that support blob data

DBMS

Data types that support blob data

Sybase Adaptive Server Anywhere

LONG BINARY, LONG VARCHAR (if more than 32 KB)

Sybase Adaptive Server Enterprise

IMAGE, TEXT

Microsoft SQL Server

IMAGETEXT

Oracle

RAW, LONG RAW

Informix

BYTE, TEXT

For information about the datatype that supports blob data in your DBMS, see your DBMS documentation.

Adding blob columns to a pipeline definition

When you select data to pipe, you cannot select a blob column as part of the data source because blobs cannot be handled in a SELECT statement. After the pipeline definition is created, you add blob columns, one at a time, to the definition.

StepsTo add a blob column to a pipeline definition:

  1. Select Design>Database Blob from the menu bar.

    NoteIf the Database Blob menu item is disabled The Database Blob menu item is disabled if the pipeline definition does not contain a unique key for at least one source table, or if the pipeline operation is Refresh, Append, or Update and the destination table has no blob columns.

    The Database Binary/Text Large Object dialog box displays. The Table box has a drop-down list of tables in the pipeline source that have a primary key and contain blob columns.

  2. In the Table box, select the table that contains the blob column you want to add to the pipeline definition.

    For example, in the EAS Demo DB, the ole table contains a blob column named Object with the large binary datatype.

  3. In the Large Binary/Text Column box, select a column that has a blob datatype.

  4. In the Destination Column box, change the name of the destination column for the blob if you want to.

    If you want to add the column and see changes you make without closing the dialog box, click Apply after each change.

  5. When you have specified the blob source and destination as needed, click OK.

StepsTo edit the source or destination name of the blob column in the pipeline definition:

  1. Display the blob column's pop-up menu and select Properties.

StepsTo delete a blob column from the pipeline definition:

  1. Display the blob column's pop-up menu and select Clear.

Executing a pipeline with blob columns

After you have completed the pipeline definition by adding one or more blob columns, you can execute the pipeline. When you do, rows are piped a block at a time, depending on the Commit value. For a given block, Row 1 is inserted, then Row 1 is updated with Blob 1, then Row 1 is updated with Blob 2, and so on. Then Row 2 is inserted, and so on until the block is complete.

If a row is not successfully piped, the blob is not piped. Blob errors display, but the blob itself does not display. When you correct a row and execute the pipeline, the pipeline pipes the blob.