Creating a data pipeline

You have a number of choices when creating a data pipeline. This section leads you through them.

To create a data pipeline:

Click the New button in the PowerBar and then select the Database tab page.

Select Data Pipeline and click OK.

The New Data Pipeline dialog box displays.

The picture shows the New Data Pipeline dialog box. At top is a box showing icons for available data sources. They are labeled Quick Select, sequel Select, Query, and Stored Procedure. Under them is a Database Connections box. Within it, the Source Connection and Destination Connection boxes display database profiles that have been defined. The last database connected to is selected as the source. The first database on the destination list is selected as the destination. In both boxes of the sample, this is E A S Demo D B V 4.

The Source Connection and Destination Connection boxes display database profiles that have been defined. The last database you connected to is selected as the source. The first database on the destination list is selected as the destination.

If you do not see the connections you need To create a pipeline, the databases you want to use for your source and destination must each have a database profile defined. If you do not see profiles for the databases you want to use, select Cancel in the New Data Pipeline dialog box and then define those profiles. For information about defining profiles, see “Changing the destination and source databases”.

Select a data source.

The data source determines how PowerBuilder retrieves data when you execute a pipeline:

Data source	Use it if
Quick Select	The data is from tables that have a primary/foreign key relationship and you need only to sort and limit data
SQL Select	You want more control over the SQL SELECT statement generated for the data source or your data is from tables that are not connected through a key
Query	The data has been defined as a query
Stored Procedure	The data is defined in a stored procedure

Select the source and destination connections and click OK.

Define the data to pipe.

How you do this depends on what data source you chose in step 3, and is similar to the process used to define a data source for a DataWindow object. For complete information about using each data source and defining the data, see Chapter 18, “Defining DataWindow Objects.”

When you finish defining the data to pipe, the Data Pipeline painter workspace displays the pipeline definition, which includes a pipeline operation, a check box for specifying whether to pipe extended attributes, and source and destination items.

The sample Data Pipeline painter workspace displays the pipeline definition. Displayed in the top section are a Table box with the entry employee _ copy, a Key shown as employee _ x, an Options box displaying Create - Add Table, a Max Errors value set to 100, a Commit value set to 100, and an Extended Attributes check box that is unselected. Below this section, a grid displays source and destination items. It has columns titled Source Name, Source Type, Definition Name, Type, Key, Width, D e c, Nulls, Initial Value, and Default Value. Cells in the Default Value column are blank. The rows of the Key and Nulls columns display check boxes. The rest of the cells in the grid are filled in with values.

The pipeline definition is PowerBuilder's best guess based on the source data you specified.

Modify the pipeline definition as needed.

For information, see “Modifying the data pipeline definition”.

(Optional) Modify the source data as needed. To do so, click the Data button in the PainterBar, or select Design>Edit Data Source from the menu bar.

For information about working in the Select painter, see Chapter 18, “Defining DataWindow Objects.”

When you return to the Data Pipeline painter workspace, PowerBuilder reminds you that the pipeline definition will change. Click OK to accept the definition change.

If you want to try the pipeline now, click the Execute button or select Design>Execute from the menu bar.

PowerBuilder retrieves the source data and executes the pipeline. If you specified retrieval arguments in the Select painter, PowerBuilder first prompts you to supply them.

At runtime, the number of rows read and written, the elapsed execution time, and the number of errors display in MicroHelp. You can stop execution yourself or PowerBuilder might stop execution if errors occur.

For information about execution and how rows are committed to the destination table, see “When execution stops”.

Save the pipeline definition if appropriate.

For information, see “Saving a pipeline”.

Seeing the results of piping data You can see the results of piping data by connecting to the destination database and opening the destination table.