![]() |
Editor Transaction Control |
![]() |
The Query Editor employs transaction control techniques as found in most (if not all) similar tools shipped as a part of any particular database product. Commit and rollback blocks are specific to the current editor's connection which remains with a single editor for the duration of its use - ie. opening multiple editors creates a separate connection for each. Editors support two commit modes - manual or auto - and this may be toggled by selecting the Toggle auto-commit on/off button from the editor's toolbar. |
Manual-Commit Mode |
In manual commit mode, each statement sent to the database is not committed or rolled back until the user explicitly does so using either the Commit/Rollback button from the editor's toolbar or sending a single 'commit' or 'rollback' statement to the database as shown below. 1. INSERT INTO people (first_name, second_name) 2. VALUES ('John', 'Smith'); 3. 4. UPDATE people SET first_name = 'Robert' 5. WHERE people_id = 9652; 6. 7. commit; In the example above, the first two statements on lines 1 and 4 would not be committed until line 7. Alternatively, in the absence of the 'commit' at line 7, selection of the Commit button will complete the transaction. Rollback statements work in the same manner. Depending on the driver and database in use, were the first statement at line 1 to return an error, subsequent statements such as the second at line 4 would also return an error where it otherwise would not. In such cases, the transaction needs to be reset (commit or rollback) before execution may continue. Note: Some database drivers and products do not accept creation of certain objects such as tables whilst in manual transaction mode. In such cases, toggle the transaction mode to auto-commit. |
Auto-Commit Mode |
In auto commit mode, each statement is committed as it is executed depending on the driver and database implementation. Reusing the example above: 1. INSERT INTO people (first_name, second_name) 2. VALUES ('John', 'Smith'); 3. 4. UPDATE people SET first_name = 'Robert' 5. WHERE people_id = 9652; Both of the above statements would be committed to the database following successful execution of each individual query. Where it was previously noted that when in manual transaction mode an earlier statement can affect the success of any statement after it, this does not apply to auto-commit. If the first statement at line 1 fails, the second is executed in turn and may succeed or fail irrespective of any previously executed queries. |