Whether rows are committed
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 stop
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.
When PowerBuilder stops
execution
PowerBuilder stops execution if the error limit is reached.
The following table 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 |
|
All or None |
No limit |
Every row that pipes without error is |
|
All or None |
A number n |
If the number of errors is less than n, all If the number of errors is |
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.
About transactions
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.
About the All and None commit
values
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.