Configuration & Settings

Options

Table of Content

Table of Content

Table of Content

As we have developed AQT we have frequently had to make design/performance decisions. Rather than restricting you to our settings, we have made most of them available as options that can be changed from our default settings by clicking on Options in the menu bar. This is available on most windows.

Options that you can set are explained in detail in this section. The Options window gives great flexibility in the way that Advanced Query Tool runs for you.

On this window:

  • OK. Save your amended settings.

  • Cancel. Exit the window without saving any changes.

  • Defaults. This button will reset (most) of your options back to the defaults. This can be useful to correct a problem with AQT that may relate to a setting that has been changed.

    Use this button with caution as it can remove any customized options that you may have set.

Misc Options

You can make a lot of changes to the way that AQT displays your data.

General

Background Colour

Allows you to select the colour used in many of the AQT windows.

When you click on Other, AQT will show you a color-picker dialog.

Use System Colors

When this option is selected, AQT will use Windows System Colors for many of the elements on the windows.

It is recommended that this option is used if you are using a non-standard Windows Theme, such as High Contrast.

Otherwise it is recommended that this option is not selected.

Alternate Selection Color

By default, when cells are selected in a grid they will be displayed as black on gray. When this option is selected, selected cells will be white-on-blue (which is more inline with the color scheme used in other windows controls).

Prompt before Closing AQT

With this option selected, if you still have a database connection open AQT will prompt you before it closes.

Having this option selected is often useful to prevent inadvertently closing AQT (which can happen if you click on the wrong close button).

Close AQT when all windows are closed

This option specifies whether you want AQT to terminate when all the windows are closed.

Remember user-parms between sessions

Specifies whether you want user-defined parameters saved between AQT sessions. The default is yes.

Save Session on Close

When this option is selected, your AQT session will be saved when you close AQT. The next time you start AQT, your session will be restored.

This option is only used when you close AQT with a number of windows still open. If you close each AQT window then close AQT, the session is not saved (as there are no open windows to save).

External Editor

Use this to specify the external editor to be used when AQT needs to display or edit a file in an external editor.

By default this is Notepad.

Editor Args

This allows you to specify any arguments than need to be passed to the external editor. Code <1> where you want the filename to go.

Example (for Editplus)

-e

or

-e <1>

This will open the file in an existing instance of Editplus.

If the <1> is not specified it will be assumed to follow the argument.

Attempt to qualify unqualified table names

This is a technical option.

In the Run SQL window and Query Builder windows, it is possible for an unqualified table name to be used. Eg.

Select * from Employee

AQT needs to find the columns for this table. There are two ways AQT can do this:

  • Use the queries that AQT normally uses for obtaining the columns of a table. The problem with this is that AQT needs to know the table-schema, however this is not given in the query. To deal with this, AQT "guesses" what the schema might be - for instance, it might be the same as the user-id of the person running the query. It has a number of other rules in addition to this.

  • Do a "prepare" of the query Select * from tablename. This will return the names and data types of the columns, though not fuller information such as the column description. This method will fail if you do not access to the table.

When Attempt to qualify unqualified table names is set, AQT will use the first method. This is the default.

It is recommended that you do not change this option unless you frequently use unqualified table names, and AQT is having trouble finding the columns for the table. There are several circumstances we know of where this might be a problem:

  • if you use SET SCHEMA, SET CURRENT SQLID (or similar statement) to change your default schema

  • for Oracle, if you are using Remote Synonyms.

  • for DB2 for iSeries, if a Library List has been specified on the configuration of the ODBC Datasource

Qualify MySQL/MariaDB table names with database name

When this option is specified, table names for MySQL and MariaDB in the SQL and Query Builder windows will be databasename.tablename. Example: information_schema.COLUMNS

When using the Query Builder or the Link to Related Table feature of AQT, AQT needs to find information on table relationships. It get this from two places:

If you do not have any relationships defined to the database, the database query is superfluous and adds unnecessary overhead to AQT. In this case, you may wish to select this option. When this is done, AQT will not query the database for foreign key relationships - it will get related table information from UDRs only.

Use Alternate Password Encryption

This option should only be used in circumstances when the normal password encryption appears not be to be working successfully. This has happened for a few users who are using an unusual character set.

In this case, selecting this option specifies that an alternate method be used to encode the password; this should behave more reliably on a variety of character sets.

Passwords encrypted when this option is selected can not be used by releases of AQT prior to AQT v9.1.0.

Number of searches on Search Table window

This specifies how many searches will be present on the Search Table window.

Database Name

The following three options determine the Name for a database. This is the name the database is identified as on the Window-list bar and other places in AQT.

By default, this will the same as the Datasource name (as appears on the Signon window). However there are options for using a different name.

Show user name as part of database name

When this option is select, the user name will be included as part of the database caption. So instead of the database name appearing as (for instance) Oracle, it will appear as Oracle (system).

This option is useful if you are have multiple connections to the same database, each with a different user-id.

For Oracle, identify database by Server Name

When you sign onto a database, by default the database will be identified within AQT by the Datasource name (this is the name shown on the Signon window).

When this option is select, the database will instead be identified by the Oracle Server name.

Other

This option provides more flexibility than the previous two options, and replaces these two options. In this you can specify in more detail how the database name is to be composed. You specify a mask that consists of text plus some of the following keywords:

<dbs>

The Datasource name

<user>

The signon userid

<userB>

The signon userid enclosed in brackets

<dbname>

The SQL-Server / Sybase the user is signed onto

<instance>

The database Instance identifier. For Oracle, this gives the server name.

<orapriv>

The Oracle privileges (SYSOPER or SYSDBA)

<oraprivB>

As with orapriv but enclosed in brackets


Examples:

<dbs>

This is equivalent to the default name

<dbs> <userB>

This is equivalent to the "Show user name" option

<instance>

This is equivalent to the "Identify database by Server Name" option


You can have different specifications for different database types. The following example demonstrates this:

o:<instance> <oraprivB>;m:<dbs>+<dbname>;<dbs>

In this:

  • there are three specifications, each delimited with a semicolon

  • the first two are database-specific specifications (for Oracle and SQL Server). These start with a database identifier followed by a colon.

  • the last specification is not database-specific. It will apply to those databases which don't match one of the previous entries.

Note that the database identifier is the internal AQT identifer for the database. It can be seen on Help > Database Properties, field Internal AQT Type.

Enable Interface to Document Locator by ColumbiaSoft

When this is checked, AQT will enable the interface to the Document Locator document management system.

This will enable you to save/open AQT files from the Document Locator repository.

This is described in more detail in Document Locator.

Folder for Windows Scheduled Tasks

This is used when creating Scheduled Tasks using AQT's Batch Job feature. It specifies the folder within the Windows Task Scheduler that AQT's scheduled tasks will be created.

If you have multiple AQT users using your machine, you may wish to give each user a different value for this. This will give each user an independent area of the scheduler for their tasks.

Window Behaviour

Specifies whether AQT will automatically open new Data Display, Run SQL or Query Builder windows. This topic is discussed in more detail in the Data Display, Run SQL and GUI Query Builder help.

When new SQL window is opened, set to blank

In the SQL window, clicking on Ctrl-N (or File > Open another SQL window) will open a new SQL window. By default, the new SQL window will have the same text (and query) as the SQL you came from. When this option is checked, the new SQL window will have a blank SQL text.

This option is new with AQT v7, and we recommend that it is checked. When you are using multiple SQL windows, you will find that AQT manages the windows in a more sensible way.

When this option is checked, AQT will "link" an SQL window with the Data Display window that displays the data.

  • every time you run a query from an SQL window, the results will go to the Data Display window that is associated with the SQL window. For example, if you have three SQL windows, AQT will use three Data Display windows, and the results will go to the correct window.

  • the Data Display window will not be reused / overwritten, as long as the SQL window associated with it is still open.

  • in the SQL window, hitting Alt-F5 will take you to the Data Display window.

The Query Builder window also behaves in the same way; each Query Builder window will have a Data Display window that is used for displaying the results of the query.

Center dialog windows

By default, AQT remembers the last position a window was located at, and reestablishes this the next time the window is opened. This includes after AQT has stopped / restarted.

This can sometimes cause problems on a multi-display system, especially if a display is removed or positioned differently. In this case, a window can be positioned out of sight. AQT has code to deal with this situation, however there are display configurations where this problem still happens.

This option will help prevent this problem from happening. When it is selected, any dialog windows will be positioned in the center of AQT, rather than at their previous location.

This option can be useful if you are running multiple displays and these are changed from time to time.

There's more on this topic at No windows visible in AQT.

Window-List bar

These options govern some of the appearance of the Window-List bar. Other options of this bar can be obtained by right-clicking the bar.

Group Data Display and Chart windows with their parent window

This option controls the order which the buttons appear in the window-list bar. By default, the buttons are grouped by window-type; all the Database Explorer buttons are shown, followed by Run SQL buttons, Query Builder buttons, Data Display buttons then Chart buttons.

When you have a large number of Data Display and Chart windows open, it can be useful to have the Data Display / Chart button after the button for the window that has created it. As an example, when this option has been selected, the order of the buttons might be:

  • Database Explorer windows

  • Run SQL window

  • Data Display windows for queries run by the Run SQL window

  • Chart windows for charts created by the Run SQL window

  • Run SQL window2

  • Data Display windows for queries run by Run SQL window2

  • Query Builder window

  • Data Display windows for queries run by the Query Builder

When the buttons are shown in this order, it is easier to see which window created a particular Data Display or Chart window.

Note: when you change this option the existing buttons in the window-list bar are not reordered. This option only applies for new windows are they are created.

Captions for Data Display icons in the Window-list bar

When a Data Display window is opened, a button is created in the AQT window-list bar. The caption for this button is taken from the table name (if you are displaying a table), query name (if you are running a query), or some other descriptive text. The options here govern, to some extent, what text is used here:

  • maximum characters in caption. This specifies how many characters will be used in the caption. A large value here will allow a longer, more descriptive, caption to be used, however the button will be wider.

  • caption is formed from. By default, when displaying a table, the caption will be schemaname.tablename - for instance DBA.EMPLOYEE). You may not be interested in seeing the schema name here. In this case, select Table name only. The caption will be (for instance) EMPLOYEE.

Captions for SQL Buttons

Caption is formed from table or query name. When this option is checked, the caption for an SQL window button is taken from the query table or query name. This will be similar to the caption for the Data Display window for the query.

File Associations

These options allow you to create File Associations for AQT for a number of different file types.

When you create a File Association, there are two modes in which the File Association is created:

File Association

When this is checked, an Open with AQT association is created for the file type. When you right-click a file of this type in Windows Explorer, an Open with AQT option will appear in the drop-down list. Click on this to open the file in AQT.

You can remove the File Association by un-checking the box.

Default

When this is select, AQT becomes the default application for that file type. When this is done:

  • you can open the file in AQT by double-clicking the file in Windows Explorer

  • the AQT icon will be displayed with the files

  • for Chart files, the file-type description is set to "AQT Chart File"

These settings are removed when the Default box is un-checked.

Some Notes

  • Clicking on File Association is "harmless" as it will just add another file association for the file type. This will be removed when you de-select this option. By contrast, clicking on Default will override your existing default file association and icon for the file type. This cannot be undone by de-selecting Default, as the original settings will not be re-established. Use Default with caution if you have an existing file association for the file type.

  • You should not set Default for MS Access or Excel files if you have MS Access or MS Excel installed on your PC. However it is OK to set a File Association for these.

  • You may need Administrator privileges to create File Associations.

What Happens when you open a file?

This depends on the file type:

  • when an SQL, QRY or DDL file is opened in AQT, the file is opened in the Run SQL window. You will first be prompted to sign onto a database.

  • when an MS Access, MS Excel or FoxPro database is opened in AQT, you are signed onto that database.

  • when an xcf file is opened in AQT, the chart is displayed in the AQT Chart window. You will not be prompted to sign onto a database; if you wish to Refresh the chart you will need to use File > Connect to connect to the database before the chart can be refreshed.

    If you to view a number of charts, a better option is to open AQT and use the Query Explorer.

Some Technical Notes

File Associations are created by adding some entries into the Windows Registry (HKEY_CLASSES_ROOT). File Associations are very complicated, as there are a number of different ways these can be set up in the registry.

AQT uses a relatively simple way of setting up File Associations. This is shown in the image below. Note that other applications may set up their File Associations in a different way. If you have other applications with File Associations on the same file-types as AQT, they may do it in a way which is not compatible with AQT. The result is that you may not see the File Association for AQT or the other application.

When the File Association button is checked, the following entries are created.

  • .xcf is the entry for the file type. This points to the xcf entry

  • the xcf entry must have a shell entry. There can be multiple entries underneath shell. Each of these entries gives an action for the file type. In this example, there is only one entry (AQT).

  • the value for the AQT entry gives the Title for that entry ("Open with AQT"). You see this when you right-click a file in Windows Explorer.

  • the AQT entry has a command sub-entry. This gives the command that is used to start AQT.

When Default is checked, the following steps are also done:

  • the shell entry is given a value of "AQT". This specifies which of the sub-entries is the default action for the file type.

  • the DefaultIcon entry is created. This specifies the icon to be used to represent files of this type.

  • the xcf entry is given a value of "AQT Chart File". This is description of the file type in Windows Explorer.

Command String

Except for SQL files, the command string to open a file with AQT is:

aqt "%1"

where aqt is the fully-qualified name of the aqt executable.

For SQL files, starting AQT in the above way will run the SQL file as a batch script; AQT will appear not to start. To open the SQL for editing (eg. in the Run SQL window), the following command is used :

aqt -e "%1"

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 need 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-journaled DB2/400 tables.

  • you have deselected AutoCommit, or are using Transactions.

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.

Autosave

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

  • this is only done if you are running a Saved Query

  • if your query runs, but fails, the query will not be saved. It is only saved when it has been run 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:

  • 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 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.

Example:

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 need 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-journaled DB2/400 tables.

  • you have deselected AutoCommit, or are using Transactions.

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.

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.

Syntax Highlighting

These give the options for the display of SQL in the Run SQL window. Any changes to these options take effect only the next time a Run SQL window is opened.

The SQL is displayed using the Essential Edit from Syncfusion. These options configure this control.

Access these options through Run SQL > Syntax Highlighting.

Font Name

This specifies the Font to use for the text. Other attributes of the text (such as Bold, Italic etc) are given in the Colors box on this tab. The default font is Courier New, 9 pt.

Replace Tab with Spaces

This option specifies what is to happen when the Tab key is pressed:

  • when the option is non-selected, a Tab character is inserted into the SQL

  • when the option selected, AQT will insert a number of blanks into the SQL. Use Num Spaces to set the number of blanks to be inserted.

Automatic Indenting

Specifies whether the SQL text is to be automatically indented as new lines are entered.

Line Spacing

Gives the space between lines in the Editor. By default, this is 1.

Background Color

This specifies the background color of the editor box.

Tab Stops

This allows you to specify the stop positions when the Tab key is hit.

Specify these as a comma-separated list - eg. 10,20,30

Column Guides

This option allows you to specify a number of Column Guides. These appear as a vertical bar in the Run SQL window, and is useful for lining text up in a particular column positions.

Specify these as a comma-separated list - eg. 10,20,30. Values have to be greater than 1.

The Column Guides will only appear in the Run SQL window when you have clicked on View > Column Guides in the Run SQL window.

Highlight Current Line

When this option is selected, the current line in the SQL Editor will be highlighted with the specified color.

Colors

This box specifies the colors and text effects of the various types of text in your SQL.

  • the box on the left lists various text types.

  • text color gives the color of the text. Back color gives the background color to the text.

  • you can specify text-effects with the Bold and Italic check-boxes.

Clicking on Reset this color to Default resets the colors of this text element to the default settings.

Clicking on Reset all colors to Default resets the colors of all text elements to their default settings.

Note that Column and Table colors will only display in the Run SQL window when AQT has loaded the column list for the table. This is done with (in the Run SQL window) Table > Build Column List for Query Table or F2.

Highlight Table and Column Names

When this option is selected, Table and Column names in your SQL will be highlighted.

The colors used for this are given in the Colors box - items Columns and Tables.

Intellisense

These options govern the behaviour of the Intellisense feature of the Run SQL window.

Show Tables Dropdown

Specifies whether the table-list dropdown will automatically be displayed as the user types.

If this option is switched off, the table-list dropdown will only be shown when the user hits Alt+T.

Show Columns Dropdown

Specifies whether the column-list dropdown will automatically be displayed as the user types.

If this option is switched off, the column-list dropdown will only be shown when the user hits Alt+C.

Show Column Values

Specifies whether column values are to be displayed as the user types. This is triggered when the user types an equals sign after a column name.

This option should only be used when you are dealing with tables with a small number of rows. AQT runs a query to get this list of values; this can be time-consuming for large tables.

This option is de-selected by default.

Show Keywords when Tab is pressed

Specifies whether the list of Keywords is displayed when the user hit the Tab key.

If this option is switched off, the Keyword list will only be shown when the user hits Alt+K.

Max Dropdown Items

Specified the maximum number of items in the intellisense dropdown box. Without a limit, the dropdown could be populated with tens of thousands of table names; this can cause a performance problem.

Edit Keywords

This allows you to edit the Intellisense keyword file. These keywords are those that appear in the keyword-dropdown of the Intellisense feature.

Switching Intellisense On and Off

By default, Intellisense is active. You can switch it off and on by clicking on the Intellisense button in the Edit toolbar in the SQL window.

Display Options

These are a number of miscellaneous options controlling how data is displayed.

Use Correct Data Types for Grid Columns

This is a technical option concerning the way AQT uses the grid.

When this option is selected, the columns in the grid will be defined as per the data type of the data being displayed:

  • numeric columns will be displayed in a numeric grid column.

  • date and time columns will be displayed in a date grid column.

  • datetime and timestamp columns will be displayed in a character grid column.

  • boolean columns will be displayed in a boolean grid column. They will appear as a checkbox.

  • all other data types will be displayed in a character grid column.

In most cases, this will display the data correctly. However there are some possible problems:

  • the data will not display as it was retrieved from the database. Instead, the grid will format the data using it's own formatting process. In some circumstances, you may wish to see the data exactly as retrieved from the database. This is discussed more at How Numeric Values are Displayed.

  • if there is any "invalid" data, the values will not display; instead a flashing error-box will be shown. This can sometimes happen with date values:

    • any date less than 0001-01-02 or greater than 9999-12-31 will not display. This is a limitation of the .Net environment.; dates outside of this range are not valid dates.

    • DB2 displays midnight as 24:00:00. The grid will display this as an invalid time.

When this option is not selected, all grid-columns will be defined as character. The data will display more accurately, however:

  • numeric and date columns will not sort correctly. They will sort in character order, rather than numeric or date order.

  • the Totals and Sub-totals functions of the Data Display window will not be available.

  • Pivot will not recognize any columns as being numeric; consequently it will think that there are no columns which are candidates for the Analysis column.

By default this option is selected. If you have a problem with the display of numeric columns, you should switch this option off.

Display Date columns as character

This is similar to the previous option, but this is only done for Date and Time columns.

This allows you display Date and Time columns as character, but display columns of other types normally. You would do this by selecting Use Correct Data Types for Grid Columns and also selecting this option.

Note: this option only applies to columns defined as Date or Time. Many databases do not have a Date and Time data type but store date and time values in a datetime or timestamp data type. This option does not apply to these, as these datatypes will be displayed by AQT in a character grid column.

Display Numeric Columns as Double

Normally, numeric columns are displayed in a grid column defined as Decimal. In some circumstances this cannot display particularly large values. To deal with this, this option can be selected; in this case numeric columns will be displayed in a grid column defined as Double. This can handle virtually all numeric values, however there can be loss of some precision digits for large values.

Show ROWID

This option is useful for databases that have an internal table row identifier that can be displayed with the contents of the table.

  • for Oracle, Informix and SQLBase this is called the ROWID

  • for SAP DB and MAXDB this is called the SYSKEY

  • for PostgreSQL this is called the OID

  • for DB2 for LUW, this is called the RID

When this option is selected, this rowid is displayed when a table is displayed. AQT does this by changing the table-display SQL to include the rowid column.

You may notice that this option doesn't come into effect right away - only on the next table you display.

Displaying the rowid is often useful when you wish to edit the data in a table. AQT will recognise the rowid column as a valid table key, and will use this to identify the rows you are updating or deleting.

Show Length of Varchar Columns?

This is a potentially useful option for database developers. When a value is inserted into a Varchar column the length of the column has to be set (in C and Java this is generally done with the null byte, in other languages a length field is set). When you display the table you can verify that the data has been loaded correctly, however the column length cannot be seen.

When you select this option the length of the column is displayed, along with the data. For instance FRED will be displayed as (4)FRED. This can be useful for verifying that the column lengths have been set correctly - if instead you see (20)FRED then you know that you are filling your database up with a lot of unnecessary blanks! This has happened on some projects we have been involved in.

Remove trailing spaces from string values

When this option is selected (which is the default), AQT removes spaces from the end of string values. De-select this option if you wish to retain these spaces.

This option is used when displaying data, plus the following AQT features:

  • Data Export. This option governs whether trailing spaces are included when data is export

  • Data Loader. This option governs whether trailing spaces are included when data is loaded from one table to another

  • Data Compare. This option governs whether trailing spaces are included in the compare of values between tables.

For DB2 for iSeries use Column Headers

When this option is selected and you are displaying a DB2 iSeries physical / logical, AQT will use the Column Headers (rather than the Column Name) when displaying the table contents.

If the Column Heading is blank, AQT will use the Column Text.

For Oracle, use TO_CHAR with Timestamp and Interval columns

Most versions of the Oracle ODBC Driver do not handle Timestamp or Interval columns. When you try to display these, the ODBC Driver, and AQT, will crash. AQT has a work-around for this problem; when you display a table with columns of these types, and you have this option selected, AQT will enclose the timestamp/interval columns in a TO_CHAR function.

By default, this option is selected. De-select this option if you have a recent version of the Oracle ODBC Driver that can display these data types without problem.

How to display Bit / Boolean values

This specifies how bit / boolean values are displayed:

  • Checkbox. This is default. The values are displayed as a checkbox in the grid.

  • 0 or 1

  • False or True

When Checkbox is used, you cannot see whether a value is Null. The other two options will show this.

Grid Appearance

These options control the appearance of the grid on the Data Display window.

Show Row Numbers

Specifies whether Row Numbers are shown. This gives the default behaviour; when displaying a query result you can switch Row Numbers on and off by View > Row Numbers, clicking the icon, or F4.

Show Group-by Box

Specifies whether the group-by box will be shown on the data display. This gives the default behaviour; when displaying a query result you can switch the Group-by box on and off by View > Group-by Box, clicking the icon, or Ctrl+G.

Variable Row Height

Specifies whether the data display will show the data using variable height for the rows. This is useful if your data contains multi-line text columns.

This option is set to the default behaviour. When you display a query result you can switch the variable-height display on and off by View > Variable Row Height, clicking the icon, or Ctrl+H.

This option is CPU-intensive and can significantly slow down the data display. For this reason, the setting of this option (unlike most of the others) is not saved between AQT sessions.

Show Fixed-Column Splitter

This specifies whether the Fixed-Column Splitter is to be displayed in the Data Display window.

This can be displayed / hidden on a particular window with View > Show fixed-column splitter.

Enable drag-select

Specifies whether cells can selected in the grid by clicking on a cell and dragging over cells. This is similar to the behavior of AQT v8 and other products such as Excel.

This feature has a slight CPU impact on your machine. On slow machines this may make the grid feel sluggish. In this case you may wish to de-select this option.

You can also select cells by clicking on a cell, holding down Shift, then clicking on a second cell.

Maximum column width

Gives the maximum width (in pixels) of a column in the data display. The default value is 500; you may wish to increase this if you often deal with large columns.

When you change this value, the grids in your existing Data Display windows will not be changed to reflect this new value. You must do a Refresh of the data for the new value of Maximum Column Width to come into effect.

Adjust column widths

When displaying your data, AQT will calculate the width of the column needed to contain the data and column header. This setting allows you to add an amount to this width; this can be useful if the calcuation AQT uses results in a value which is slightly too small. The default is 5.

Minimum row height

This gives the height of rows in the display grid. The default value (20) is normally adequate. However if you change the font to a larger or taller font, you may need to increase this.

Maximum row height

Gives the maximum height (in pixels) for a row, when the Variable Row Height option is in effect. The default value is 1000.

Grid Style

This specifies the Style to be used with the grid. Click on a Style from the dropdown box, or Load from file to load a customized style. You can edit your existing styles, or create customized styles with Edit Styles.

The default style is AQT0. Our preference is AQT7 - this uses gradient colors which can crash on some machines with older graphics cards.

Font

This specifies the font that will be used on the Data Display windows.

Selection color

This specifies the color to be used when you select a range of cells.

Highlighted row color

This specified the color to be used when you Highlight a row.

Fixed-column color

This specifies the color to be used for fixed columns.

Display Format

These options govern how data is to be displayed when you run a query or display a table.

Show Nulls as

This specifies how null values are displayed.

Numeric Values

Whether numeric values are left or right justified in the grid.

Format of Date/Time Columns

These options specify how Date, Time, DateTime and Timestamp columns are displayed. Enter a format using a combination of the letters: y (year), M (month), d (day), H (hour), m (minute) and s (second) plus any delimiting characters (such as -,: or /).

Examples:

  • MM-dd-yyyy will display as 12-31-2001

  • dd/MMM/yyyy will display as 31/Dec/2001

  • HH.mm.ss will display as 15.32.58

Timestamps and Datetime columns display as: Date Format + “ “ + Time Format.

You can read a more detailed discussion about Date and Time columns.

The date format MUST include the following specifications: yyyy, MM or MMM, dd.

The time format MUST include the following specifications: HH, mm, ss.

For Oracle, the NLS_DATE_FORMAT will be changed to what you specify for the Date Format.

Format of Informix Literals

These options specify how Informix date / time literals are to be specified. This must match the format specified in the DBDATE or GLS_DATE environment variables. If AQT were smarter, it would be able to automatically pick up the DBDATE / GLS_DATE values; however it cannot do this, so this literal format need to be specified here.

These options need to be specified as described in Format of Date/Time Columns, except that the mmm specification is not valid.

These options only need to be specified for Informix. For all other databases, the format of the date / time literals are hard-coded in AQT (see Date and Time columns for more on this). For Informix, the way in which date and time literals are specified is not fixed, but depends on the DBDATE or GLS_DATE environment variables.

How to display Binary Columns

Binary Columns are columns which can contain non-ASCII data (in DB2 these are defined as CHAR FOR BIT DATA, in Oracle they are defined as RAW, in Sybase and SQL Server they are defined as BINARY). This also includes BLOB and VARBINARY columns.

AQT can display binary data in two formats:

  • ascii – the ascii representation of the data is displayed. This may well give unreadable values.

  • hex – the data is displayed in hex mode. For DB2 hex data is displayed like x’44’, other databases display hex data as 0x44.

The default mode for AQT is smart. In this mode, AQT will display the binary columns in either ASCII mode or hex mode depending on the contents of the column – eg. it is displayed in ASCII mode if the column contains only displayable characters, otherwise it is displayed in hex mode.

For Hex display add a space between bytes

When AQT is displaying data in hex mode, this option will add a space between byte values. This makes the hex value more easy to read. Example:

  • (without this option) x'4151542072756C657321

  • (with this option) x'41 51 54 20 72 75 6C 65 73 21'

Max size of Hex display

This specify how many bytes of hex data AQT will display.

Convert EBCDIC to ASCII

The option only applies to DB2 z/OS and DB2/iSeries binary columns. This includes character columns defined as FOR BIT DATA and VARBINARY.

For normal character columns, Ebcdic - Ascii translation is done by DB2/Connect or DB2 CAE. However this is not done for columns defined as FOR BIT DATA, as these may contain binary information for which conversion is not required. However, if you have any normal text in these columns, they will display in AQT in Ebcdic format, and so will be unreadable.

When this option is selected, AQT will check whether binary columns contain Ebcdic data (it does this looking for Ebcdic characters in the first 20 bytes of the column). If so, the column value is converted from Ebcdic to Ascii, and so will display correctly in AQT.

Notes:

  • If you wish to insert or update any Ebcdic column, you need to specify the text in Ebcdic rather than Ascii. You can convert from Ascii to Ebcdic using the Base64/Ebcdic Encoding tool (see Tools from the Database Explorer).

  • the Data Compare tool will not convert data to Ascii

  • the Data Loader will also not convert data to Ascii. The data will remain in Ebcdic - this enables it to be loaded correctly into another Ebcdic column.

Display x'00' characters as spaces, or as

This option allows character columns that contain ascii 0 characters to be displayed fully in AQT. Normally, such strings are truncated at the ascii 0 character - this is because text-boxes (and other Windows components) interpret the ascii 0 as an end-of-string delimiter. To circumvent this, when this option is selected, AQT will scan all strings being displayed and replace any ascii 0 characters with spaces. This enables the full string values to be displayed.

In the or as box, you can specify which character is to be used to display x'00' characters. By default this is a single blank; you can specify another character here.

This option also applies to data returned in OUT and INOUT parameters of Stored Procedures.

Display unprintable characters as

When this is specified, AQT will replace all unprintable characters with the specified string. Unprintable characters are those in the range ascii 0 to ascii 31.

If the replacement string contains a colon, the ascii code will be replaced there. For instance, when $(:) is specified, an ascii 10 will be replaced with $(10).

This option will override the Display x'00' characters as spaces option.

This option will not be used for LOBs which are written to files with the Write LOBs to files option.

This option will have a small impact on performance, so should not be used unless necessary.

Automatically apply Layout to Queries

When you specify this option, every time you run a saved-query, AQT will check whether there is a layout-file for that query. If so, AQT will format the data as per the specifications saved in the layout-file. This is discussed in more detail in Saving / retrieving the display Layout.

Automatically apply Layout to Tables

When you specify this option, every time you display a table, AQT will check whether there is a layout-file for that table. If so, AQT will format the data as per the specifications saved in the layout-file. This is discussed in more detail in Saving / retrieving the display Layout.

Display Limits

Max Rows Displayed

Specifies the number of rows to display when a query is run, or when a table is displayed. You may just wish to set this to the maximum value, however be very aware that when you set this to a large value your queries will take a long time to run and more memory will be used.

Our recommendation is to set this to a low value (1000) and just change this to a higher value when you want to see a lot of rows.

Warning: if you set this to No Limit, and display a very large number of rows, your machine can run out of memory. Use this with caution. For this reason, AQT will warn you if you use this setting.

If you are running the Evaluation version of AQT you will not be able to increase this beyond 50 rows.

Max Column Size

This option determines the maximum size of a column held in the result-set. Our recommendation is to set this to a low value (200) and change this to a higher value only when you wish to see the full text of your large columns. A high value will result in an large amount of memory used by AQT.

For displaying the full value of large columns (such as LOBs), it is recommended that you use Options > LOB Options > Write LOBs to Files.

If you are running the Evaluation version of AQT you will not be able to change the Max Column Size value.

LOB Options

These options govern how AQT will display large columns, such as BLOBs and CLOBs.

When Write to LOB File is not selected, (which is the default) AQT will try to display the value of BLOB/CLOB columns in the same way that other columns are displayed.

This may not give useful results if the column contains hex data or is very large. In this case, the option Write LOBs to Files provides a better mechanism for displaying LOB columns. This issue is described in more detail here.

You can also select what column-types this is done for: BLOBs, CLOBs and LONG VARCHAR:

  • BLOBs means

    • BLOB (DB2/Oracle)

    • IMAGE (Sybase, SQL Server)

    • LONG VARBINARY (Sybase, SQL Server)

    • VARBINARY of x bytes or longer

  • CLOBs means CLOB (DB2)

  • LONG VARCHAR means

    • LONG VARCHAR (DB2)

    • CLOB and LONG (Oracle)

    • TEXT and NTEXT (Sybase, SQL Server)

    • VARCHAR of x bytes or longer

    • MEMO (Access)

In this, x if the value specified in For Varchar / Varbinary use Write to Files when column length>=

Exclude LOBs when displaying tables

When this option is specified, AQT will not display any LOB values.

This can be useful if your tables contain LOB data and you aren't interesting in seeing their values (retrieving them can be time consuming).

Unicode

If you have enabled Unicode, AQT will write all CLOBs and Long Varchars values as unicode files. If you don't want your files in this format, you should disable Unicode (see Options > Unicode Options).

Unicode Options

Enable Unicode Features

Specifies whether Unicode support in AQT is enabled. This is discussed in more detail in Unicode Support.

By default, Unicode support is enabled. You should also switch this of if you are having problems accessing your database; it could be that your database doesn't support the unicode-enabled interface used by AQT when this option is selected.

Use Unicode with ODBC Functions

Specifies whether AQT is to use the Unicode version of the ODBC Function calls. For most databases this option will not have any effect unless Options > Database Explorer > Get Table Information from > ODBC functions has been selected.

By default this option is not selected. You may wish to check this if you have a database that has Unicode in the table and column names, and you are using the ODBC functions option.

Workarounds

The remainder of the options relate to the use of unicode strings in SQL statements. Many database have limitations in how these are used. We have developed a set of workarounds that enable unicode strings to be used within AQT for these databases.

For Oracle, use UNISTR for Unicode strings

This option is discussed in Unicode Support for Oracle. When this option is checked, AQT will scan all SQL being run against an Oracle database. If it finds some unicode characters, it will replace the Unicode characters wuth UNISTR function calls.

The UNISTR function is not available in Oracle v8, so you should not set this option if you are running Oracle v8.

For SQLServer, ensure Unicode strings are in N'xxx' format

This option is discussed in Unicode Support for SQL Server. When this option is checked, AQT will scan all SQL being run against a SQL Server database. If it finds some unicode characters, it will ensure that the strings are preceded with an N (for example N'ald').

For Sybase, use TO_UNICHAR for Unicode strings

This option is specified in Unicode Support for Sybase. When this option is checked, AQT will scan all SQL being run against a Sybase database. If it finds some unicode characters, it will replace the Unicode characters with to_unichar function calls.

For Sybase Anywhere, replace Unicode strings with parameters.

This option is specified in Unicode Support for Sybase Anywhere. When this option is checked, AQT will scan all SQL being run against a Sybase Anywhere database. If it finds some unicode characters, it will replace them with a parameter marker, and pass the unicode string in the parameter.

Table Editing

These options control the functioning of the table editor.

Prompt On

This specifies whether, before AQT runs an Update, Delete or Insert statement, AQT is to show you the SQL it is about to run. You will have the opportunity to check that this SQL is what you expect, and to cancel the change if it is not.

Delete Row with Delete Key

With this option set on, rows can be deleted by hitting the Delete key. Without this option, you will have to right-click the row, and select Delete from the quick menu.

New Row Position

Specifies whether a row created by New Row (which is used for inserting new rows into the table) is to appear at the Top of the grid, or the Bottom.

Check row is unique before updating or deleting

When this option is on, before running an update or delete, AQT will run a query to check that only a single row will be updated / deleted. AQT will only do this check if your key has been "manually" specified - eg. though the Specify Key dialog or as a user-defined key. The check will not be done if your table has a primary key or unique index.

Because AQT runs a query before doing your update/delete, there is will be a slight performance impact. However it is strongly recommended that you do not switch this option off.

History

These specify options relating to the SQL History feature.

There are three history files:

  • SQL History. This records all SQL statements run

  • Procedure History. This records all invocations of stored procedures through the Run Stored Procedure window.

  • Export History. This records all exports.

In these cases:

  • the history files are held in the Location of History Files (see Options > File Locations)

  • history entries are written immediately, so the entries are retained even if AQT crashes.

  • history entries are never purged.

As a result of the last of these items, you may wish to select some of the options below to manage your history file entries.

History Filename

The name for your history file. If you change this, it comes into effect immediately. This is a simple way of switching to another history file.

Separate Files each Day / Week

This option is useful if you want AQT to use separate history files for a given period. In order to do this, you need to check this option and change your history file name to have a date-period indicator. The date period indicators are: yyyy (year), mm (month), dd (day), ww (week). So:

History File Name

Effect

aqt_history_yyyymm.txt

Separate file each month

aqt_history_yyyyww.txt

Separate file each week

aqt_history_yyyymmdd.txt

Separate file each day

AQT determines the filename when it starts - if the day changes during an AQT session, the history filename will not change.

Maximum SQL text length

To prevent the history files becoming excessively large, AQT limits the amount of SQL text written with each History record. This option specifies the amount of SQL text written.

By default this is 20000 bytes - you may wish to increase this if you are running large SQL statements and you the full SQL text to be recorded in the History.

Include SQL Run From

The SQL History has a number of options about which SQL statements are included in the history. This is because SQL can be run in many places within AQT:

Run SQL

These records are always included

GUI Query Builder

This includes queries run the Query Builder. An history record is created every time you click on the Run button.

Table Inline Edit

This includes the SQL run when you update a table using the Inline Edit feature.

Row Update

This includes SQL run using the Row Update / Insert / Delete window.

SQL Scripts

By default, when you run a multi-statement script from the Run SQL window, you will get a single history record. When you select this option, you will get an separate history record for every statement run from the Multiple Statements window.

Auditing

These options control the Auditing feature of AQT.

Enable Auditing

Specifies whether the auditing feature is enabled.

Audit Directory

Specifies the directory where the audit file/s are to be written

Audit File Name

Specifies the name of the audit file within the Audit Directory. You can Browse the contents of this file by clicking on Browse.

If your audit file contains <osuser>, the user's OS Userid will be substituted in the filename. This will allow you create the audit files in a shared directory, with each user's file being given a different name.

Separate Audit Files Each Day

Check this option is you wish to use a separate Audit File every day. If you select this, you must also include a date specification as part of the Audit File Name. The date specification must be in the format yyyymmdd.

Example: aqt_audit_<osuser>_yyyymmdd.txt. This will create a separate audit file for each day, with a name such as aqt_audit_PHIL_20021103.txt.

Audit Select Statements

By default, AQT will only audit non-select statements (Updates, Deletes etc). Check this option if you also wish to audit Select statements. This will not include “internal” AQT select statements, such as those that populate the Database Explorer.

Audit Query Results

AQT can also write the results of queries to the Audit file. Select this option if you wish to enable that. The query-data is written to the audit file in comma-delimited format.

Maximum Rows to write, Maximum Row length

These options are only relevant when you have selected Audit Query Results. To prevent an excessive amount of data being written to the audit file, you can specify the maximum number of rows (per query) to be written, and the maximum length of a row.

File Locations

These options specify the directories where relevant files reside. In all cases clicking on Change allows you to change the directory. Clicking on Default will reset the directory to its default location.

Default directory for files created by AQT

AQT creates a number of files while it is running. These are:

  • LOB files (in sub-directory \lob)

  • debug file (aqtdebug.txt), if debug was switched on

  • files containing information on your Favorites (favxxxxx.txt)

  • files containing information on your user-defined relationships (xxxxx.udr).

  • files containing information on your user-defined keys (keyxxxxx.txt).

By default, these files are created in sub-directory workfiles of the AQT Application Data directory (this directory is discussed later).

If you change this directory, you should move any existing files to the new location. AQT can do this for you automatically when you change this directory and click on OK.

Location of Config files

This specifies where the config files are located. Database Types and Config Files describes the config files. By default this directory is sub-directory cfg of the AQT install directory.

Location of Saved Queries

This specifies the default directory for queries to be saved to and retrieved from. This is used in the Save Query window.

By default, these files are created in the sub-directory queries of the AQT Application Data directory (discussed later). If you wish to share your queries with another user, you may wish to change this directory to another location.

When using the Query Explorer, you will be shown the queries in this directory.

Location of Shared Queries

This specifies the directory containing queries which are shared amongst members of your workgroup. This would typically be a directory on a shared server.

If this is blank, AQT will not use Shared Queries.

When using the Query Explorer, files in this directory will be shown in the Shared Queries folder in the tree.

Location of History Files

This specifies the directory where the query, run-procedure, export history and job history History files are held. If this is blank, the directory used is the Default Directory for files created by AQT.

If you change this directory, you should move your history files to this new location. This change comes into effect immediately and AQT will write into the new directory the next time it runs a query.

Location of Layout Files

If this is specified, Layout files will always be held in this directory.

If this option is not specfied, the Layout files will be held in the queries directory (for table layouts) or the same directory as the query (for query layouts). This is discussed further in Saving / Retrieving the display layout.

If you change this directory, it is your responsibility to move your existing layout files from their current locations to this directory.

Location of Report Styles

This specifies the location of Report Styles, as used by the Generate Report function of the Data Display window.

A number of Report Styles are delivered as part of AQT, and are held in sub-directory ReportStyles of the AQT install directory. Windows security can prevent modification to this directory, which can interfere with your amending or adding reports. In this case, locating the report styles in another directory can be useful.

If you change this directory, it is your responsibility to move the existing report styles from their current location to this new directory.

Default directory for Exports

This directory specifies the directory for Export files when an unqualified filename is specified.

This is main used when an export script is run, and an unqualified filename specified. By default, this directory will be the Exports sub-directory of the Location of Saved Queries.

Directory for Disk Cache

This is the directory where the Disk Cache files are held.

Location of Batch Jobs

This is the directory where your batch jobs reside. This is used by the Manage Batch Jobs dialog. Batch jobs located in a different directory will not be seen by Manage Batch Jobs.

By default, batch jobs as held in sub-directory batch from the Application Data directory. A number of sample batch jobs are placed in there by the AQT install process.

Application Data directory

Files created by AQT are held in your Application Data directory. This is:

  • (for Windows XP and earlier) x:\Documents and Settings\<username>\Application Data\Advanced Query Tool

  • (for Windows 10 and later) x:\Users\<username>\AppData\Roaming\Advanced Query Tool

where:

  • x is the drive in which AQT was installed

  • <username> is the user-id with which you are logged onto Windows

Note: this directory is also given by the APPDATA environment variable.

Using Windows Environment Variables

You can use Windows Environment variables in the file locations. Example:

%APPDATA%\Advanced Query Tool\Queries

or

%HOMEPATH%\AQT\Queries

Row Display/Update

These options relate to the Row Display / Update window.

Get values from Foreign Keys

This relates to the use of the Get Values button.

  • When this option is not checked, AQT will get the existing values of the column from the table.

  • When this option is checked, AQT will see whether a Foreign Key has been defined on that column. If so, it will get the values from that foreign key column.

Note that for some databases, particularly Oracle, obtaining information on Foreign Key relationships can be very slow. Consequently we suggest caution when using this option with such databases.

User Defined Relationships

If the table has some User Defined Relationships, AQT will look at these as well as any Foreign Key relationships.

If you have checked Options > General > Get Related Table Information from User Defined Relationships only then AQT will only look at User Defined Relationships, and not at any Foreign Keys. This is a useful option for databases such as Oracle, for which the Foreign Key query is slow.

Document Locator

These options relate to AQT's interface to Document Locator.

This tab will only be visible if General > Enable interface to Document Locator has been checked.

Profile Name

When documents are imported into the Document Locator repository they must be given a profile name. This gives the default profile name to be used when files are imported.

This applies to all files imported into the repository, except for exports. When you run an export you can spe

When you are running an export, you can specify the profile name as part of the export options.

Default existing-file mode

This specifies the action to take when a file being imported already exists in the repository. The following values can be specified:

createversion

A new version of the document will be created. This is the default.

replace

The document will be replaced. The existing version of the document will be overwritten.

createnew

A new document will be created with a name such as "name - Copy(1)"

autoinc

A new document will be created with a name such as "name (1)"

cancel

The document will not be imported. Other documents in the queue will not be imported

skip

The document will not be imported. Other documents in the queue will be uploaded

prompt

The user will be prompted for the action to be taken.

Show import progress box

When this option is checked, you will be shown a box giving progress of the import of files to the repository.

This option is ignored when AQT is run in batch mode.

Prompt when files are Imported

When this option is checked, AQT will show you a dialog whenever a file is being imported to the repository. The dialog allows you to specify various parameters of the file in the repository - profile and other parameters.

This option is ignored when AQT is run in batch mode.

Local Directory

This specifies the directory on the local PC used to transfer files to / from Document Locator.

By default it is subdirectory DL from your Documents directory. Example:

c:\Users\your-user-name\Documents\<repository>

The DL respository name will be substituted where you have specified <repository> in the directory name.

Do not change this directory without good reason.

Sessions

These options govern how AQT Saves / Opens sessions.

Save Session on Close

To save your session when AQT closes, select one of the following:

  • Prompt to Save. This is the recommended option. It allows you to select whether or not you wish to save the session.

  • Show full Save dialog. When this is specified the full Save Session dialog will be shown

  • Save without Prompting. When this is specified, AQT will save your session automatically when AQT exits.

  • Save Session when Windows shuts down. When this is selected, AQT will save your session (without prompting) when Windows shuts down, restarts or you log off you session.

    It is recommended that you use this option when you are using Prompt to Save - if Windows shuts down when you are not at the computer, you will not be able to respond to this prompt and your session will not be saved.

Open Last Session when AQT starts

If you have a saved session,

  • Automatically open last Session. AQT will open the last session without prompting. It will do this only if you have a single saved session - if there are multiple saved sessions, you will be shown the Open Session dialog to select the session.

  • Prompt to open last Session. This is the recommended option. AQT will prompt you whether or not to open the last session.

  • Always show the Open Session dialog. When this option is selected, AQT will show the full Open Session dialog. This will allow you to select a session to open (such a permanently-saved session).

If there is a saved session, AQT will delete it once AQT starts, irrespective of whether you have opened it.

Open a particular Session when AQT starts

You can use this to open a particular session when it starts. Select a session from the drop-down list.

As there are separate sessions for 32-bit and 64-bit AQT, there are options here for both architectures.

When this option is selected, Save Session on Close, Open Last Session and Auto Save Session options are ignored.

Automatically Save Session

When Auto Save Session is selected, AQT will save the state of your session to disk continuously during your session. This means you will not lose your work should AQT terminate unexpectedly.

The option Save state every xx seconds specifies the frequency for which AQT checks for / saves change to the state of your session. If this is set to 60 (eg. 1 minute) then you could lose up to a minutes work should AQT crash.

This option can be resource intensive, so not recommended for normal use.

Technical Notes on Options

Registry

All AQT options are held in the Windows registry in the registry key "HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Cardett\Options".

For AQT v8, most of the options were held in registry values List1 and List3. For AQT v9, v10 and v11, the settings are held as one registry value per option. This makes it easier to manage the settings through the registry.

An option only appears in the registry when it has a value different from the default.

Moving Options between Machines

This can be done in either of the following ways:

  • using regedit, go to "HKEY_LOCAL_MACHINE\SOFTWARE\Cardett\AQT\Options" then click on File > Export. This will create a *.reg file. Copy this to the target machine then double-click it to load these settings into registry on that machine.

  • in the AQT Options window, click on File > Export all Options to export the options to a file. On the target machine, the options can be loaded with File > Import options.

Loading Options on AQT Startup

AQT can load an options file when it starts. The file must be called aqtoptions.txt, and located in the the same directory as the AQT executable (eg. C:\Program Files\Advanced Query Tool v10). This file must be in the same format as created by File > Export from the Options window.

This provides an easy way for system administrators to deploy a set of options to their users.

Unless the locked option is used, the user will be able to change the options during their AQT session; these will not be saved but will be set back to the values in aqtoptions.txt the next time AQT starts.

To lock an option, add the locked keyword after the option. When this is specified, the user will not be able to change the option.

Example:

ReadOnlyMode=y;locked

Specifying an Option when AQT starts

You can set an option on the command which starts AQT. You may include either in a bat file which starts AQT, or in the shortcut which starts AQT.

Locking an Option

System administrators can "lock" an option by placing an entry for it in "HKEY_LOCAL_MACHINE\SOFTWARE\Cardett\AQT\Options".

Once an option is locked, it will appeared "grayed-out" in the Options window - the user will not be able to change it. AQT provides no mechanism to manage the options in this part of the registry - this can only be done by the use of regedit and importing / exporting registry values.

Internal Name

Each option has an internal name, which is the name AQT uses when storing the option in the registry, and used in the export and import files.

The easiest way to see the internal name, is to (in the Options window):

  • set an option to a value

  • save your options with Ctrl+S

  • click on File > View non-default options. This will show you your options (which are not set to their default values). You will be able to see the internal names for the options.

Alternatively (from the Options window):

  • click on File > Show internal option names

  • when this is set, when your mouse hovers over an option it will show you the internal name for the option. Note that there are a few options (such as text boxes) this does not work for.