|
The Data Pipeline allows you to copy tables and their data from one database to another even if the databases are on different DBMSs. For example, you can copy a database table in a SQL Server database to a Watcom database. This is useful when you want to copy data to a local database so you can work on it without needing access to the network.
 To use the Data Pipeline:
- Click the Data Pipeline icon in the toolbar. The Select Data Pipeline dialog displays.
Tip: It may be necessary to customize the toolbar to display the Data Pipeline icon.
- Click New. The Select Destination Profile dialog displays.
- Select the profile that connects to the database you want to copy the tables and data to. For example, if the destination database is the Watcom database you connect to using the profile named Watcom1, you would select Watcom1.
- PowerBuilder connects to the destination database and then displays the Select Tables dialog. The dialog lists the tables in the source database (the database you were connected to when you opened the Data Pipeline painter).
- Select the tables you want to copy to the destination database. The SELECT painter opens with the selected tables displayed.
Tip: The name of the first table you select will be the default name for the destination table. If you select more than one table, a table is created using the selected columns.
- Create the SQL SELECT statement you want to use to select the columns and data you want to add to the destination database. Use the same technique that you use to paint the SQL SELECT statement for a DataWindow when the data source is SQL SELECT.
- Click the Data Pipeline icon to return to the Data Pipeline workspace. The fields at the top of the dialog are:
|
Field Name
|
Description
|
|
Table
|
Name the table will have in the destination database. You can change the name.
|
|
Option
|
Select an option:
- Create - Add Table
- Replace - Drop/Add Table
- Refresh - Delete/Insert
- Append - Insert
- Update - Update/Insert
When you select the option Create - Add Table or Replace - Drop/Add Table, you can modify the table definition displayed for the destination database. Your modifications must follow the rules of the destination database. If you specify a unique identifier (enter a value in the Key field), you must also specify a key column (click the Key checkbox) and visa versa. PowerBuilder will create a primary key or index (which ever is supported by that DBMS) for the table in the destination database.
When you select Refresh - Delete/Insert or Append - Insert, the destination table must exist in the destination database and you can change the name of the table in the destination database and the values in Commit and Max Errors, but you cannot change any other information.
When you select Update - Update/Insert Rows, the destination table must exist in the destination database. The update key (the Key checkbox) must be specified; this is the only field you can modify. If a row exists in the destination database, it is updated and if it does not exist, it is inserted into the destination database.
|
|
Commit
|
Enter the frequency at which you want to commit changes to the database (the default is 100, commit after 100 rows are written to the database). If Commit is 0, PowerBuilder issues a COMMIT only after all rows have been read, then either all changes are made or no changes are made.
If Commit is less than 100, PowerBuilder uses it as the blocking factor. For example if commit is 10, 10 rows will be read and then written at once.
|
|
Key
|
The primary key for the table in the destination database. You can change the key if the option is Create - Add Table or Replace - Drop/Add.
|
|
Max Errors
|
Enter the maximum number of errors you want to allow (the default is 100). When the number of errors exceeds this number, PowerBuilder stops the pipeline. All errors display in an error DataWindow. You can manually correct the errors in the error DataWindow.
|
|
Extended Attributes
|
Click this checkbox to copy extended attributes. If a column has a validation rule, display format or edit mask associated with it, the rule, format or mask will work only if it has been defined in the new db.
|
In the lower portion of the dialog, the name and data type of each column selected in the source database display in the Source Name and Source Type columns and the definition in the destination database displays in the following columns:
- Destination name
- Data type
- A checkbox indicating whether the column is a key column
- Precision
- Scale
- A checkbox indication whether NULLs are allowed
- Initial value if an initial value was specified for the column
PowerBuilder uses the input Select statement (the statement you defined in the Select painter) to determine the destination information. You can modify the destination information when the option is Create or Replace.
See also
Using the Error DataWindow
|
