When rows are piped to the destination table, they are first inserted and then either committed or rolled back. Whether rows are committed depends on:
What the Commit and Max Errors values are
When errors occur during execution
Whether you click the Cancel button or PowerBuilder stops execution
When you click Cancel, if the Commit value is a number, every row that was piped is committed. If the Commit value is All or None, every row that was piped is rolled back.
For example, if you click the Cancel button when the 24th row is piped and the Commit value is 20, then:
20 rows are piped and committed.
3 rows are piped and committed.
Piping stops.
If the Commit value is All or None, 23 rows are rolled back.
PowerBuilder stops execution if the error limit is reached. Table 17-4 shows how the Commit and Max Errors values affect the number of rows that are piped and committed.
Commit value |
Max Errors value |
Result |
|---|---|---|
A number n |
No limit or a number m |
Rows are piped and committed n rows at a time until the Max Errors value is reached. |
All or None |
No limit |
Every row that pipes without error is committed. |
All or None |
A number n |
If the number of errors is less than n, all rows are committed. If the number of errors is equal to n, every row that was piped is rolled back. No changes are made. |
For example, if an error occurs when the 24th row is piped and the Commit value is 10 and the Max Errors value is 1, then:
10 rows are piped and committed.
10 rows are piped and committed.
3 rows are piped and committed.
Piping stops.
If the Commit value is All or None, 23 rows are rolled back.
A transaction is a logical unit of work done by a DBMS, within which either all the work in the unit must be completed or none of the work in the unit must be completed. If the destination DBMS does not support transactions or is not in the scope of a transaction, each row that is inserted or updated is committed.
In the Data Pipeline painter, the Commit values All and None have the same meaning.
The None commit value is most useful at runtime. For example, some PowerBuilder applications require either all piped rows to be committed or no piped rows to be committed if an error occurs. Specifying None allows the application to control the committing and rolling back of piped rows by means of explicit transaction processing, such as the issuing of commits and rollbacks in pipeline scripts using COMMIT and ROLLBACK statements.
