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 4-5 shows some examples.

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

DBMS

Datatypes that support blob data

Sybase SQL Anywhere

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

Sybase Adaptive Server Enterprise

IMAGE, TEXT

Microsoft SQL Server

IMAGE, TEXT

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.

    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.