Discarding changes to a transaction using SQL ROLLBACK
Application
SQL ROLLBACK discards all of the changes and additions of a transaction. The transaction is defined via the HANDLE to be specified.
Requirements
- Code number 555343
- Table exists
- Appropriate table name
The names of tables and table columns must start with a letter and must not contain an arithmetic operator (e.g., +). Due to SQL commands, these characters can cause problems when data are input or read.
Description of function
The function of the SQL command SQL ROLLBACK depends on the INDEX:
- Without INDEX:
- The control discards all changes and additions of the transaction
- The control resets a lock set with SELECT...FOR UPDATE
- The control completes the transaction (the HANDLE loses its validity)
- With INDEX:
- Only the indexed row remains in the result set (the control removes all of the other rows)
- The control discards any changes and additions that may have been made in the non-specified rows
- The control locks only those rows indexed with SELECT...FOR UPDATE (the control resets all of the other locks)
- The specified (indexed) row is then the new Row 0 of the result set
- The control does not complete the transaction (the HANDLE keeps its validity)
- The transaction must be completed manually with SQL ROLLBACK or SQL COMMIT at a later time
Input
11 SQL ROLLBACK Q1 HANDLE Q5 INDEX 5 | ; Delete all rows of transaction Q5 except row 5 |
The NC function includes the following syntax elements:
Syntax element | Meaning |
---|---|
SQL ROLLBACK | Syntax initiator for the ROLLBACK SQL command |
Q/QL/QR or Q REF | Variable in which the control stores the result |
HANDLE | Q parameter with identification of the transaction |
INDEX | Row number within the Result set as a number or variable that is retained If not specified, the control discards all changes and additions to the transaction Optional syntax element |
Example
11 SQL BIND Q881 "Tab_Example.Position_Nr" |
12 SQL BIND Q882 "Tab_Example.Measure_X" |
13 SQL BIND Q883 "Tab_Example.Measure_Y" |
14 SQL BIND Q884 "Tab_Example.Measure_Z" |
* - ... |
21 SQL Q5 "SELECT Position_Nr,Measure_X,Measure_Y, Measure_Z FROM Tab_Example" |
* - ... |
31 SQL FETCH Q1 HANDLE Q5 INDEX+Q2 |
* - ... |
41 SQL ROLLBACK Q1 HANDLE Q5 |