Previous Topic

Next Topic

Book Contents

Book Index

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:

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:

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 need to switch off Safe Update Mode.

This option is not available in a number of circumstances:

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 to Save Query

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

This option is ignored for Shared Queries when Allow Changes to Shared Queries is set to Warn or No.

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.


When this option is selected, AQT will automatically save your query every time your query runs successfully.

This option is ignored for Shared Queries when Allow Changes to Shared Queries is set to Warn or No.

Show Saved Message

Indicates whether you want to be shown the message Query saved to filename whenever you save your query.

Always prompt to Save

By default, AQT will only prompt you to save your query when you have a saved-query.

When this option is selected, AQT will always prompt you to save your query even if it is not a saved-query.

Number of Entries in Recent List

This specifies the number of entries AQT is to maintain in the Recent Queries list

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 results 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:

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 can get a syntax error with some databases.

Retain comments before start of SQL

When Remove Comments from SQL is de-selected, AQT will retain all comments in your SQL except for comments preceding your SQL. These will be removed as these are often a description of the statement and not required to be included with your object definition.

If you wish to retain these comments, check Retain comments before start of SQL.

This can be useful in some circumstances, such as when you have Optimizer Hints preceding your SQL.

Show popup box when SQL fails

When this option is selected, you will get a popup message when your SQL fails. This can be useful so that a failed SQL statement doesn't go un-noticed.

Parse SQL when query opened

When this option is checked, AQT will parse the SQL when a query opened. The parsing will obtain the names of the tables in the query; the column list of the tables will be retrieved.

This parsing can take a few seconds. De-selecting this option will prevent this time delay. In this case the query can be "manually" parsed with Table > Build Column List for Query Table (or F2).

Show "Copy to Data Display window" button on tabs

When this is specified, in both the Run SQL and Query Builder windows the display tabs will have a button for "Copy to Data Display window". This makes it easy to copy the results in that tab to a Data Display window.


Include full column list in default SQL

By default, when AQT queries a table it will use SQL such as:

select * from table

When this option is checked, the default SQL will instead include the full column list, eg.

select col1, col2, col3, col4 .. from table

This is useful for subsequently amending the query

Format SQL places comma before column name

When this option is selected, Format SQL will place the comma prior to the column name.


Advanced Query Tool
© 2023 Cardett Associates Ltd. All rights reserved.