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-5 shows some examples.
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.
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.
To add a blob column to a pipeline definition:
Select Design>Database Blob from the menu bar.
If 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.
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.
In the Large Binary/Text Column box, select a column that has a blob datatype.
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.
When you have specified the blob source and destination as needed, click OK.
To edit the source or destination name of the
blob column in the pipeline definition:
Display the blob column’s pop-up menu and select Properties.
To delete a blob column from the pipeline definition:
Display the blob column’s pop-up menu and select Clear.
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.
