Options – Run SQL

Statement delimiter

This option is used when running multiple SQL statements. By default the semicolon is used to delimit multiple SQL statements. This can cause a problem if your SQL statement contains a semicolon (which can happen with procedure or trigger definitions). To prevent a semicolon from being interpreted as a statement delimiter, select either:

• None (No delimiter)
• / When this is selected, a / in column one will be used as the statement delimiter. This delimiter is commonly used in Oracle Scripts.
• GO. When this is selected, GO at the start of a line will be used as the statement delimiter. This is a delimiter commonly used in SQL Server and Sybase scripts. This is case independent, so go or Go will also be recognised as the delimiter.
• Other. To use a delimiter other than these, enter the delimiter into the box.

For further information on this see Running multiple statements.

Safe Update Mode

This feature was introduced to prevent people inadvertently updating or deleting more rows in a table than they planned on. At every site we've worked at, every now and then someone will accidentally update or delete all the rows in a critical production table. Enabling the Safe Update Mode feature helps to prevent this.

When this feature is enabled, AQT will check the number of rows affected by every Update or Delete statement you run. If this is greater than the value you specify for When more than x Rows Changed then:

• when Prompt for Action is selected, AQT will ask you whether you want the change to proceed or to be rolled back
• when Rollback Change is selected, AQT will roll back the change without prompting.

By default the row-limit is set to 1, so any update affecting more than one row will have these actions happen. You can set this to a higher value if you are running a series of updates that you know are going to affect multiple rows, however we recommend that you do not switch this feature off - instead, you should increase the row-limit.

From a technical point of view, this feature is achieved by doing a Start Transaction before any update, then a commit / rollback, End Transaction after. Some databases (such as MS Access) occasionally have trouble with Transactions and give errors such as "Option not valid at this time". If this happens you will have to switch off Safe Update Mode.

This option is not available in a number of circumstances:

• your database doesn't support transactions. This is a the case for version of MySQL earlier than v5. It is also the case for non-journalled DB2/400 tables.
• you are using option Use SQLExecDirect. We plan on removing this limitation in future releases of AQT.

By default, these settings are not remembered between sessions - eg. if you switch off Safe Update Mode, when you start AQT the next time it will be set on. The reason for this is that Safe Update Mode is a safety mechanism and we wish to prevent people switching it off permanently by mistake. However if you really want to switch it off permanently, click Remember Between Sessions.

Prompt for changes to queries

This option specifies whether you wish to be prompted to save changes to a Saved Query.

Use standard Windows open/save Dialog

By default, AQT will use its own dialog window for opening and saving queries. If you prefer to use the normal Windows open/save dialog, check this option.

Multiple Result-Sets

These options govern how AQT is to treat queries (such as Stored Procedures) that return multiple result-sets. By default, AQT will display each result-set in a different results-window, and changing this setting affects whether results are updatable (see options below).

If you select Display multiple result-sets in the same window then AQT will display all result-sets from the query in the same results window. By default, these result-sets will appear one after the other with no gaps between. These result-sets will NOT be updatable.

The option Display multiple queries in same window is a similar option, but governs how AQT displays the results when you run multiple queries from the Run SQL window. When this option is selected, the result-sets from all the queries go to the single results window. These result-sets will NOT be updatable.

If you select Include a blank line between query results AQT will place a blank line between the different query result-sets. The column titles will be taken from the first result-set - the following result-sets will not have column titles as this is not possible with the way the grid works.

This option is the closest achievable to having a header row for the results of the second query in the same window.

Enable Get More Rows Feature

The Get More Rows feature allows you to scroll through all of a large table/query, irrespective of what you have specified for the Max Rows Displayed. When this option is selected, AQT will retrieve the number of rows specified in Max Rows Displayed; if there are more rows you will get a Get More Rows button displayed on your results window. Clicking on this will get another set of rows. By clicking on the Get More Rows button you can scroll through the entire table.

Be aware that this option can cause an increased impact of AQT on your database - your DBA may complain about the performance impact it causes.

This feature works by AQT keeping your query active (technically, your cursor is kept open). For some databases this open cursor will hold some resources (including database locks). You should only use this option with caution. DB2 users are recommended to also set Technical Parameters > Isolation Level to Uncommitted Read. This will ensure that your queries do not hold any locks.

Another warning: some databases (such as MS SQL Server, HP SQL/MX) do not allow multiple queries to be active. While you have a query open, you will not be able to run any other queries. None of the queries in the Database Explorer window will work.

Append More-Rows to Grid

When this option is selected, the Get More Rows feature will add the extra rows to the existing grid. The grid will continue to get larger, and you may eventually run out of memory.

Display Results while Fetching

Normally, AQT will only show you the data once it has all been fetched from the database. For large tables this can take some time.

If you select this option, AQT will show you the data as soon as it becomes available (actually once it has retrieved 100 rows). This can be useful when displaying a large table, as you don't have to wait before seeing the data. Once you have retrieved enough data, you can click on Abort to stop AQT fetching more data.

Some notes:

• if you scroll to the bottom of the grid, you may notice a lot of blank rows. AQT adds rows to the grid in "chunks" before populating them with data.
• you cannot exit the window, or show the Row Detail window while data is still being fetched.
• if you are using a non-default font, the font will only be set once the query has finished.
• if you are refreshing a query, AQT will reestablish sort-order, column and row position, and grouping after the query has finished.
• this window is only fully set up when the fetching finishes processing. A number of the functions on the window will not work, or will fail, if you try to run them before the fetching has completed.

Prompt when in transaction mode

When you are running AQT in transaction mode, and have some uncommitted changes, AQT will remind you of this every time you run some SQL.

If you do not want to get this reminder message, you can de-select this option.

Remove Comments from SQL

When this option is selected, AQT will remove line-comments from your SQL (line comments are comments starting with --). This is discussed in more detail in Including comments in your SQL.

By default, this option is selected. You may wish to de-select this if you are defining a procedure, trigger or function and wish to retain the comments in the text.

If this option is de-selected, and you run a normal SQL statement (query, update, insert statement etc) that contains line comments, you will get a syntax error with some databases.