Previous Topic

Next Topic

Book Contents

Book Index

Running multiple statements

The Run SQL window allows you to run multiple SQL statements. Generally these would be Action statements, however the window can also be used to run multiple Select statements.

This feature is often used to run a series of SQL statements supplied in a file OR a series of generated statements.

SQL formatting

Setting the Statement Delimiter

The statement delimiter used for running a script is given in Options > Run SQL > Statement Delimiter.

It is also possible to set the delimiter to be used for a particular script. This is done by having an --aqt options,sdelim command as the first statement in the script. Example:

--aqt options,sdelim=@

Insert into TEST.DEPARTMENT values('A00', 'SPIFFY COMPUTER SERVICE DIV.', '000010', 'A00', NULL)@

Insert into TEST.DEPARTMENT values('B01', 'ADMINISTRATION SYSTEMS', '000020', 'A00', NULL)@

This delimiter is only used when running the script; it is not changed for the AQT session.

Multiple statements

When you run a series of SQL statements, the Run Multiple SQL Statements window will be displayed. This will show you all the SQL statements in your script. Your statements are not run immediately, instead you must click on Run (or F5) to run them. This gives you the opportunity to:

Auto-Commit

By default, AQT will operate in Auto-Commit mode. In this mode, every statement is committed after it has run. You can switch off Auto-Commit mode; AQT will then commit only when it hits a Commit / Rollback in your Script. Alternatively you can manually Commit / Rollback by clicking the Commit / Rollback buttons.

Stop on Error

This option specifies whether AQT is to stop processing the script if there is an error while processing an SQL statement. You have options:

Yes

Stops on any error

Yes, but ignore errors on Drops

Stops on any error, unless it occurs on a Drop statement.

This option is available because scripts for building a database often consist of a number of Drop and Create statements. The Drops will fail if the objects do not already exist; this is an “expected” error and you may not want the script to stop running when this happens.

No

Do not stop on any errors

More

Displays the Error Codes dialog (see below).

Error Codes

AQT allows you to specify a number of error codes that are considered "normal" errors, so will not stop the script from running.

You do this on the Error Code window, which can be invoked from the More button, or View > Error code filter. In this window, you enter a number of SQLState codes, separated by commas. When an SQL statement gets this SQLState code, it will be regarded as a successful execution of the SQL.

The SQLState is a 5-character code given as the first part of all error messages. The following error has an SQLState of S0001:

S0001(-1303)[Microsoft][ODBC Microsoft Access Driver] Table 'Customer_Details' already exists.

Parameters

Your SQL statements may contain parameter markers. How AQT handles these is governed by the Always Prompt option.

Other Buttons

Abort

Cancels the currently-running statement. This will only be active if you have specified Async Queries (See Options > Statement Options).

Pause

This stops the script after it has finished the currently-running query.

Skip

This button will be active only if your script has stopped because of an error. You can

  • either amend the statement then click on Run to re-run it
  • or click on Skip to skip this statement and continue from the next statement.

Multiple Select Statements

If your script has Select statements (or other statements that produce result-sets), by default a new display-results window will be created for each result-set.

If you want to have all the result-sets in the same window, use Options > Run SQL > Display Multiple Queries in same window. When you are in this mode:

When you are displaying multiple results-sets in the same display window, you will not be able to use the Row update/delete/insert functions.

Export Data

You can use Export Data when running multiple SQL statements. In the Run SQL window this is done by F7 or Export Data to > File. When running in this mode, the following rules apply:

Viewing only some Statements

You can use the View menu option to view only some of your SQL statements, eg:

These options can be useful if you have many SQL statements in your script, and you only wish to view particular statements.

Removing Comments

You can remove the comments from your SQL statements with Edit > Remove Comments. This will remove all line-comments (--) but not block comments (/* */).

Amending the Script

You can amend the statements in the script. You can do this by either amending the statement text in the SQL box, or double-clicking it for a larger editing window.

Having amended the script, you can save your changes:

When generating the script, you may wish to have a blank line placed between the statements. Select option Edit > When copying, include blank line between statements if you wish to do this.

When you copy the script, AQT will only copy the rows that are currently displayed. For instance, if you have selected View > Show statements that suceeded, it will only copy the statements that were successful.

If you have used Edit > Remove Comments, the comments will not be present in the copied / saved script