Running SQL & Queries

Running SQL

Table of Content

Table of Content

Table of Content

From the Database Explorer window, clicking on Run SQL opens the Run SQL window. This is one of the main windows in AQT, and offers you an enormous amount of power and functionality.

The Run SQL Window provides you with a text box for entering and running SQL statements.

  • There are four ways to run your SQL statement, depending on the way you like to work.

    Type it into the textbox and do one of the following

    • click on Run

    • use the Run icon

    • use the menu: Run > Run SQL

    • F5

  • You can run action SQL statements (Update, Create, Grant etc) as well as Select queries.

  • You can run stored procedures

  • You can run multiple statements including a script file containing thousands of SQL statements.

  • Queries can contain Substitutional Parameters. These are prompted for when your SQL is run.

  • Once you have run your query the Data Display window will display the results of the query.

  • Use Export to > File (or F7) to export your query data to a file.

  • There are a number of options about the layout of this window

Run SQL toolbars

The Run SQL window has two toolbars - a main toolbar and an editing toolbar.


The editing toolbar contains functions useful for editing the SQL text:

Both these toolbars can be customized to have a difference appearance and selected toolbar buttons. It is also possible to drag the editing toolbar to be on the same line as the main toolbar.

Some tasks available in the Run Sql Window have shortcut keys.

Building your SQL

You can build your SQL by typing the SQL statement into the SQL window.

  • right-click the SQL window, or use the edit toolbar, for common editing functions

  • click on Build Query to build your query using the GUI Query Builder

  • select Edit > Edit SQL using editor to edit your SQL using Notepad

  • you can save and retrieve queries

Lower panels

Save keystrokes or typing errors by using the panels in the lower part of the window, which contain common SQL keywords and operators, plus the column list of your query table.

  • Clicking on an item will add the text to your SQL, except for the column list, for which you have to double-click a column name (a single-click just selects it).

  • You can right-click a column-name – AQT will then show you a drop-down list of sample column functions. Selecting one of these will add the column plus function to your SQL.

  • You can add multiple columns to your SQL. Select multiple columns from the listbox (either by dragging the mouse down, or by using ctrl and shift), right-click then select Add Columns to SQL. This will add the column names to the SQL as a comma-separated list.

  • You can change the entries that appear in these listboxes. This is described in more detail in Other options in the Run SQL window.

Get Values for Column

Once you have “selected” a column (by clicking on it), you can click on Get Values for Column to get all the distinct values the column takes. These will populate the right-hand panel. Clicking on one of these will add the value to your SQL statement (for instance as part of a Where clause). Quotes will be added to the value if required.

Extended Get Vals is like Get Values for Column, except that it gives you more options about the values retrieved.

Get Constants gives you a number of sample constants for your selected column. These are mainly useful for Date/Time/Timestamp data types, as it gives the in-built date/time constants, and shows you the format of date/time literals.

For Oracle date columns you get an option Add To_Date. When this is selected, the TO_DATE function will be added to any date values selected from the Get Values list.

Select the database

You use the drop-down listbox to specify the database the query is to run against. By changing this, you can very easily run your query against a number of different databases.

Query table

The Run SQL window has a query-table associated with it. The name of this table is given in the window title.

  • The column-list for the query-table is given in the middle of the lower panes

  • When you click on the Reset SQL icon, the SQL will be changed to Select * from table-name

  • You can switch to another query-table by clicking on the Table menu icon (or hit Ctrl+T or the table icon). This will take you to the Switch to another Table dialog where you can select another table (in either this or another database). Alternatively you can click on Copy Table Name to just copy the table name (you can then paste this into your SQL).

  • The Table menu item and icon have a list of the last 10 tables you dealt with (though it doesn’t remember the database they were in). This makes it very easy to switch between tables you commonly deal with.

  • When you switch to another query-table, your SQL will be set to Select * from table-name

  • You can display the contents of your query table with Table > Display Query Table (or F9).

Build Column List for Query Table

If you have more than one table in your SQL, you can select which table has its column list displayed. If you click on Table > Build Column List for Query Table (or F2) AQT will parse your SQL looking for the tables in your SQL. If it finds one, this will now become your query table and the column list in the lower panes will be for that table. If there is more than one table in your query, AQT will give you a popup list of the table names; select one of these.

In a join, your tables are often identified with a table-id (also called a correlation name). AQT will find these when it is parsing your SQL; when you select a table it will place the table-id in the textbox under the column list. This table-id can then be included with the column names when you add them to the SQL. This feature is very useful when building multi-table queries, as it makes it easy to add fully-qualified column names.

If there is a table name in your SQL but AQT does not find it (which can happen if it is within a sub-select statement), double-click to highlight the table name then hit F2. If this table has a table-id, make sure you include this in the text you highlight.

By default, AQT will show the columns in the order they are defined to the database. You can display the columns in column-name order by selecting View > Display columns in alphabetic order. This option is remembered for your AQT session, though is not saved between AQT sessions.

SQL History

AQT maintains a history of queries run. Clicking on File > SQL History (or Ctrl+H) will show you the SQL history from which you can retrieve a previously-run query. This history is saved to disk, so is retained between AQT sessions.

If you want a more comprehensive record of the SQL you have run, you can use the Auditing feature.

Analyzing your data

AQT provides methods for making simple analysis of your table and columns. These options are contained within the Analyse menu item, or the Analyse icons.

  • Number of Rows will run a query which gives you the number of rows in your table.

  • Selecting a Column then clicking on Number of Distinct Values of Column will show you just that.

  • Selecting a Column then clicking on Analyse Column will show the values the column takes, and how many times each value occurs. This provides a very useful mechanism for quickly understanding the data held in a column.

  • Selecting a Column then clicking on Duplicate Values of Column will show the values of the column which occur more than once. This can be useful is you want to know “how unique” a column is.

The Analyse Column and Duplicate values of Column functions can be used be used with multiple columns from your table. Using the Ctrl and Shift keys you can select multiple columns from your column list then run the analyse functions.

Table Analysis gives more details, and describes analysing multiple columns.

Syntax highlighting

AQT uses a third-party control for displaying the SQL text. This control is SourceView from Tetradyne Software (see http://www.tetradyne.com). This control will highlight various SQL keywords and identify comments, strings, numbers, and brackets. You can configure the syntax highlighting: see Options > Run SQL > Syntax Highlighting for details.

Search Table

You can quickly build a simple search on your table by clicking on the Table Search icon (or Table > Search Table). This opens the Search Table window, where you can quickly define a number of search conditions.

Click OK to close the Search Table window. The search condition will be displayed in your SQL statement in the Run SQL textbox. You can then amend the SQL further or run it.

GUI Query Builder

If you want a more comprehensive facility for building a query, click on the Build Query menu item or icon to go to the GUI Query Builder. AQT will parse the SQL you have in this window and display it as a GUI query. After you have amended the query in the GUI Query Builder, you can return it to the Run SQL Window.

You can use the GUI Query Builder to build just a selected portion of your SQL (such as a sub-select statement). Select the piece of SQL then click on Build Query. Only the selected SQL will be passed to the Query Builder.

SQL window layout

Listbox Positions

AQT offers a number of options for the appearance of the SQL window. You can have the listboxes at either:

  • the bottom of the window

  • on the right of the window

  • completely hidden (full-screen mode)

You can cycle through these options by clicking on the list-box position icon (or hitting Ctrl-B). You can select a particular option with View > List Boxes.

When the listboxes are at the right or hidden, the Run and Abort buttons are no longer displayed. Instead you should use the Run and Abort buttons on the toolbar.

Data Grids

You can choose to display the results of the query on this window. Clicking on the Show Grids icon (or Ctrl-G) will display a data grid at the bottom of the window. Results of your query will go to this grid, as opposed to a Data Display window.

This is discussed in more detail in the section Displaying Query Results on the Run SQL window.

When the Data Grid is displayed, you cannot have the list-boxes at the bottom.

Example

This screenshot shows the listboxes being at the right, data grid being at the bottom and with line numbers displayed.

Displaying Query Results on the Run SQL window

You have the option of displaying your query results on the Run SQL window, as opposed to Data Display windows. To do this, go to View > Data Grid (Ctrl+G).

Once you have selected this option, a panel will appear at the bottom of the window - your query results will be displayed there.

  • every time you run a query, the results are displayed in a grid.

  • if you have View > Display Multiple Grids, you will get a new grid every time you run a query.

  • you close a grid by clicking on the x at the right-end of the tab

  • you can close all the grids with View > Close all grids

  • if you run a stored procedure or code-block that returns multiple results, you will get either a single grid or multiple grids depending on the setting of Options > Run SQL > Display multiple result-sets in same window

  • you can right-click the grid for the set of actions you can do with the grid - this includes being able to link into the Row Display window to view or update the row.

  • you can right-click and select Revert to this SQL. In this case the SQL that was used to display the query results is copied to the SQL text area.

  • you can Refresh the data in this grid by clicking on Refresh Grid, or hitting Ctrl+Shift+F5

  • you can select multiple cells by clicking a cell, holding down Shift then clicking a second cell. All the cells between the two clicked-on cells will be selected. Once you have selected multiple cells, you can right-click to copy or sum the selected cells.

These grids are not as fully featured as the grid on the Data Display window. If you wish to perform some of the advanced grid functions (such as grouping and totalling), you will need to copy the grid data to a Data Display window. This is done by right-click a grid and selecting Copy to Data Display window.

Using Intellisense

Intellisense is a feature of the Text Editor component which, as you type a query, will show a dropdown list of keywords, table names, column names or column functions. This is a great time saver when building a query.

This feature is context sensitive. AQT will parse your SQL and show you the dropdown list appropriate to your position in the query; for instance if you are in the column-list, AQT will show you the column-list dropdown, if you are in the table-list, AQT will show you table-list dropdown.

You have the choice of having the dropdown lists show automatically, or open when you hit a hot key. The hot keys are as follows:

Dropdown

Hotkey

keywords

Tab

keyword completion

Alt+K or Ctrl+Space

table name

Alt+T

column names

Alt+C

column functions

 

column values

Alt+V

Table List

AQT will show a dropdown list of the schemas in your database. If your database is one that does not have schemas, you will be shown a list of tables.


Once you have selected a schema, AQT will show you a list of tables in that schema

Some notes:

  • for the drop-down of schema and tables names to work, your database needs to be configured for this feature. For v9 this has been done for all databases, however if you are using an old version of the cfg file for your database you will not see these drop-downs.

  • for most database, the dropdown will show you tables, views and aliases. However for some databases (such as Oracle, sybase and sql server) it will only show you tables.

  • there is currently a bug whereby you may not see the drop-down list for MS Access and MySQL databases if are using option Show Favorites on Startup. We plan to correct this.

Column List

Important - before AQT can give you a drop-down of column names, you must hit F2.

When F2 is hit, AQT will parse your SQL to determine the tables in your query, and will fetch the column names for your tables. This must be done before AQT is able to show you a drop-down list of your columns. You should also hit F2 if you change the tables in your query, or change the correlation names (table-ids) of the tables.

The column-list drop-down is shown after a blank or comma is hit and you in a column-list or where-clause part of the SQL. Alternatively, you can activate the column-list drop-down with Alt+C.

Column Function

AQT also has the ability to show a dropdown list of column functions. You do this by typing a fullstop after the name of a column - once you have selected the column, it will be applied to the column names using the normal function syntax.

Using Intellisense with a multi-table query

Intellisense can be used when your query contains multiple tables. AQT understands the syntax for a multiple-table query; when you need to refer to a column-name, AQT will give you a dropdown of the table-ids. This is shown in the following example. When you select a table-id, you will be given a dropdown of the columns within that table.

Note that for multi-table queries, it is strongly recommended that you use table-ids with your tables (these are emp and empinfo in this example). If these are not given, column names can be ambiguous (eg. AQT may not know which table a column is in), which can cause problems with the way this component works.

Keyword Dropdown

A dropdown list of keywords does not happen automatically, but only when you hit Alt+K or Ctrl+Space

Switching Intellisense On and Off

By default, Intellisense is active. You can switch off by clicking on the Intellisense button in the Edit Toolbar. This can be useful if you are typing a lot of text into your SQL.

Options

Options governing the use of Intellisense are in Options > Run SQL > Intellisense.

Running your SQL

You can run your SQL by clicking on Run or hitting F5. AQT will pass your SQL to the database then display the data (if there are any). If there are any error messages, you will see them on the status bar at the bottom of the window.

Run only part of SQL

If you only want to run part of your SQL, select the part you want to run and click on Run. If you have text selected, AQT will run only the selected text.

Select current statement

If your SQL consists of multiple statements, hitting Run > Select Current Statement (or F4) will select the current statement. You can then run this with F5.

Run to Cursor

You can run all the SQL up to the position of the cursor by selecting Run > Run to Cursor.

Run within Brackets

You can run the SQL within the brackets containing the cursor by selecting Run > Run within Brackets.

This is useful for running sub-select clauses.

Pausing a query

If you are running a query that returns a large number of rows then you can Pause the query.

In order to do this, you need:

  • to have Options > Run SQL > Enable "Get more rows" and Pause features checked

  • to be displaying your results in a Data Display window (eg. not on the Run SQL window)

If so, then when you run a query you will get a Pause button in the toolbar. Clicking in this will pause the fetching of the data. The data will be shown in the Data Display window. You will then have the option to either:

  • fetch more rows from the query.

  • close the query.

Note that while the query is still open you will be holding some database resources. This feature should be used with caution.

Cancel a query

AQT allows you to cancel a long-running query. This is discussed more detail in Options > Cancel Queries.

Syntax-check SQL

You can do a Syntax Check of your SQL. You do this with Edit > Check Syntax (or F6). AQT does the syntax check by doing a “prepare” of your SQL against your database. This feature does not work with some databases (depending on whether “deferred prepare” can be disabled).

Explain plan

For Oracle, DB2 z/OS, DB2/UDB, SQL Server, Sybase, MySQL, IDMS and Teradata you can do an Explain Plan of your SQL. You do this by Edit > Explain Plan (or F8). See Explain Plan for more information on Explain Plan.

Show Query Cost

This function is available for DB2/UDB, DB2 z/OS and Oracle. This does an Explain of your query and returns the estimated query cost.

This is similar to Explain but doesn't show the explain window. It is useful if all you want to know is the cost of the query.

Running a DB2 Command

AQT can run a DB2 Command by invoking the DB2 CLP (Command Line Processor). This is described in the section Running a DB2 Command.

Running using SQL*PLUS

You can run your SQL under SQL*PLUS by selecting Run > Run using SQL*PLUS. AQT will invoke SQL*PLUS and pass your SQL to this.

This can be useful if you are trying to debug a complicated piece of SQL. AQT cannot display the line / columns where an error has occurred (this is a limitation of the ODBC driver). However, SQL*PLUS can display this information.

This function is for Oracle only.

Saving and opening a query

To Save or Open a query, click on File > Save Query or File > Open Query (or Save/Open icons) from the Run SQL window.

AQT maintains a list of the last 10 queries saved/retrieved. These appear in the File menu, and provide a very quick way of retrieving a recently-saved query.

You cannot save queries in the Evaluation mode of AQT.

Once you have saved or retrieved a query, the query name will be given as part of the caption in the window title bar. This query name will be followed by an * if the query has been changed since the last time it was saved. The query name is also given in the status bar at the bottom of the window.

If you have changed a query, and you either close the window, or do anything that overwrites the SQL (such as retrieving another query), you will be prompted to save the query. You can turn off this prompt by de-selecting Options > Run SQL > Prompt to Save Query.

Save / Open Dialog

When you click on the Save Query or Open Query options, you will be taken to the Save/Open dialog.

This has a number of features:

  • the Save in or Look in box has the directory being displayed. You can click on the dropdown to see the directories you have recently used.

  • as you click on files, the contents of that file will be displayed in the right panel. For retrieving, this makes it easy to find the query you are looking for. For saving, this makes it easy to compare the query you are saving with the existing contents of the file.

  • you can edit an existing file by clicking on the Edit File icon. By default Notepad will be used, however you can change this with Options > General > Editor Program.

  • You can delete or rename a file with the Delete File or Rename File icons.

  • You can refresh the contents of the window by clicking on the Refresh icon.

  • Click on the directory named ... to go up to the parent directory.

  • If you check Hide Dirs, directories will be hidden from the list.

More Features

There are a number of features to help you find a particular query you are looking for:

  • check Sub-Dirs to see files in the current directory plus all sub-directories. When you do this, another column (Directory) will appear in the file list. For large directory structures this display can take a few seconds.

  • you can enter some text into the Filter box. Only files (and directories) whose name contains this string will be shown.

  • check Recent to see only files which were amended recently (within the last 7 days). This provides a very quick way of seeing recently-changed files and directories.

  • Search Files allows you to search the text of all your files for a particular string. Enter a search string and click on Go. If you have a large number of files, this can take a while.

    To re-display all the files, clear the search string and click on Go.

Both the Filter, Recent and Search Files are particular powerful if you have the Sub-Dirs option set. These provide a means to search an entire directory structure.

Deleting and Renaming Multiple Files

This can only be done when you are in Open Query mode (in Save Query mode, multiple files cannot be selected).

To delete multiple files, select the files then click on the Delete button.

To rename multiple files, select the files then click on the Rename button. You will be prompted with the Rename File dialog, with a filename of <name>. Modify the <name> to specify how the new file names are to be contructed. The general syntax for this is given in Load Mapping Specification. Examples:

Specification

File Name

Renamed File Name

<name>_test

C:\AQT\Queries\employee_query.sql

C:\AQT\Queries\employee_query_test.sql

<name:rep(query,new)>

C:\AQT\Queries\employee_query.sql

C:\AQT\Queries\employee_new.sql

<name:beforelast(_)>

C:\AQT\Queries\employee_query.sql

C:\AQT\Queries\employee.sql

new_<name:left(6)>

C:\AQT\Queries\employee_query.sql

C:\AQT\Queries\new_employ.sql

This feature cannot be used to change either the directory name or file extension of the files.

Large Files

If you have a large query you may wish to use Run from File. This bypasses the need for AQT to import the SQL into Editor box on the SQL window - this can be time-consuming for a large query.

File is Read Only

When you select a file, AQT shows you whether the file is read-only (has the read-only flag set in the file attributes). If you change this setting, AQT will change the attributes of the file.

If you open a read-only file, AQT will not allow you to save it. You will have to save it under a different name.

Query / Home Directory

Your default / home directory for saved queries is specified in Options > File Locations. This will be the directory you will be taken into the first time you save / retrieve a query in your session. If you change to another directory you can quickly return to this default directory by clicking on the Go to Home Directory icon.

Clicking on the Set this as Home Directory will set the current directory as your home directory.

Shared Queries

If you are using Shared Queries, you will have both a Home and Shared Home buttons.The Shared Home button takes you to the Shared Queries directory.

If Allow Changes to Shared Queries is set to None, you will not be make any changes to the Shared Queries.

Opening Multiple Files

You can select multiple files (by clicking on files in conjunction with the Ctrl or Shift keys). AQT will concatenate the files together when retrieving them to the Run SQL window. It will also add a delimiter character between them.

If you select Run From File, multiple <file> specifications will be written to the SQL window.

The files will be opened in the order they are on the window, not the order in which they were selected.

Column Sort Order

The files can be displayed in a particular order by clicking on the column heading.

Your display order is saved between sessions. This allows you to always see the files displayed in your preferred order.

Microsoft Open/Save Dialog

If you prefer to use the Microsoft Open/Save dialog, rather than the AQT dialog, you can set this in Options > Run SQL > Use standard Windows open/save dialog.

Other options in the Run SQL window

Display options

You can change the font used in the text box with Edit > Change Font

You can change the case of the selected text with Edit > Change Case

Editing functions

You can right-click the text-box to get a drop-down list of the Edit menu items.

You can Comment or Un-Comment a block of text with Edit > Comment Text and Edit > UnComment Text, or the Comment/UnComment icons.

You can Find and Replace text with Edit > Find and Edit > Replace, or use the Find icon.

Keyword management

You can add or remove entries from the Keywords and Operators listboxes. This is useful if there is an SQL keyword or phrase you use frequently. The keywords are held in file sqlkeywords.txt (in your AQT directory). You can edit this file to change the entries that appear in the listboxes.

You can edit this file by right-clicking the listbox and selecting Edit Keywords. If you have changed the keywords outside the control of AQT, Reload Keywords will reload the listboxes from the keywords file.

Line Numbers

You can have line numbers on your text by selecting View > Line Numbers

Line Wrap

You can specify whether text is wrapped with View > Line Wrap (Ctrl+W).

Bookmarks

The edit control supports the use of bookmarks. This makes it easy to find your way around a large script.

  • Shift+F2 adds or removes a bookmark from the current line

  • Shift+F3 moves forward to the next bookmark

  • Shift+F4 moves back to the previous bookmark

Right to Left Mode

You can switch your editor into Right to Left mode by selecting Edit > Right-to-Left mode.

Moving around your SQL

The following keys are useful to move through your SQL.

  • Home takes the cursor to the start of the line

  • End takes to cursor to the end of the line

  • Ctrl+Home takes the cursor to the start of your SQL

  • Ctrl+End takes the cursor to the end of your SQL

  • Ctrl+I takes you to a particular line in your SQL

Find Bracket

This function is useful if your SQL has many indented levels of brackets. Position the cursor at a bracket and hit Edit > Find Matching Bracket (or F12)

  • if the cursor is positioned at an open bracket, AQT will scan forward to find the matching closing bracket

  • if the cursor is positioned at an close bracket, AQT will scan back to find the matching opening bracket

Select within Brackets

Edit > Select within brackets (or F11), will select the text between the brackets containing the cursor. This can be useful for selecting and running a sub-select.

Send query result to SQL window

You can use Export > Export to SQL Window to send the query result back to the SQL Window. This is useful if you are running an SQL statement that is generating SQL statements.

Format SQL

You can format an SQL statement by clicking on Edit > Format SQL. This will reformat your SQL in a layout which is more easily read.

  • all comments will be removed from your SQL. In the future we hope to leave comments intact.

  • only select, create view and insert statements will be formatted. Other statements will not be changed (except for the removal of comments).

  • you can have up to 1000 statements in your SQL - each will be formatted in turn and delimited with a semicolon.

  • if you have selected some text, only the selected text will be formatted.

Insert statements will be formatted in a way which makes it easy to match the insert-value with the column being inserted; if the insert statement has a column list, the column name will be given (preceded by the comment indicator --) to the right if the column value. This syntax may not be valid for many databases, for which the comment indicator can only be given at the start of a line. However such insert statements will run OK within AQT as long as Options > Run SQL > Remove Comments from SQL is selected (by default it will be).

Both Format SQL and Unformat SQL will replace x'A0' characters with spaces. x'A0' is a "non-breaking space" that can appear in your SQL if you have pasted it from an email.

By default, the comma will be placed after the column name. To have it placed before the column name, specify Options > Run SQL > Format SQL places comma before column name.

Unformat SQL

You can unformat your SQL by clicking on Edit > Unformat SQL. This will remove all linebreaks and extra spaces from your SQL.

  • all comments will be removed from your SQL.

  • you can have up to 1000 statements in your SQL - each will be unformatted in turn and delimited with a semicolon.

  • if you have selected some text, only the selected text will be unformatted.

Wrap to 60 chars

This will split your SQL into multiple lines, with each line a maximum of 60 characters.

This function may not work if you have text literals greater than 60 characters (AQT will not split these) or have text literals which span lines.

Rewrite SQL

This is a more comprehensive reformat than Format SQL. AQT will "interpret" your SQL then rewrite it. The rules for how it will rewrite your SQL are specified in the Query Builder options.

This function can be used to:

  • change your column and tables names to have a consistent case

  • change your query to have a different correlation-id for your tables

  • expand select * to have the list of all your column names

  • change your join syntax to use Ansi syntax

This function works by importing the SQL into the Query Builder then generating the SQL from the Query Builder. The Query Builder can have difficulty with some complex queries, so this function can generate incorrect SQL in some cases. Use with a degree of caution.

Remove Invalid Characters

This option is useful if you have pasted a query from an email or from Microsoft Word. In these cases, there can be invalid characters in the SQL that can prevent it from running. In addition Word can use special characters for start and end double-quote, which are also not valid in SQL.

Using Edit > Remove Invalid Characters will correct both these problems.

Copy as RTF

This option will copy your SQL text in rich-text format. This can then be used for pasting the SQL into Microsoft Word or similar editor; the colors and other text effects will be copied with the SQL.

Column Guides

Column Guides are vertical bar in the Run SQL window, and are useful for lining text up in a particular column positions.

The positions of the Column Guides is specified in Options > Syntax Highlighting.

Zoom in/out

You can make the text smaller / bigger with Editor > Zoom in/out, or Ctrl + Up/Down.

Change Line Spacing

The default Line Spacing is given in Options > Syntax Highlighting > Line Spacing.

Alternatively you can change it in an SQL window with Editor > Increase/Decrease Line Spacing or Alt + Up/Down.

Block Selection

You can use Block Selection with Editor > Use block selection. When this is specified, text in a rectangular area is selected.

Visual Where

You can display Visual Where for your query with View > Visual Where (Ctrl+Shift+W). This displays your query in a visual format, which makes it easier to understand.

  • If you have multiple queries in your window, only the first will be displayed.

  • the Visual Where window will be displayed modally, which means you must close it before continuing to use AQT.t

  • the Visual Where window is also be displayed when you build a query using the Query Builder.

Using Transactions

By default, when you run an SQL statement (such as an Update or Delete), the database is immediately changed.

Another option is to run your SQL statement as a Transaction. When this is done:

  • your changes are applied to the database but not committed, eg. are not yet permanent.

  • once you have decided that your changes to the databases are what you want, you can Commit your changes. Your changes to the database will then become permanent.

  • if you decide that your changes to the database are not what you want, you can Rollback your changes. The changes to the database are then undone.

How to use Transaction Mode

Within both the Run SQL and Row Update windows, you have the option of starting a transaction. This is done with the menu item Transaction > Begin Trans:

  • once you have done this, you will get a Transaction Mode warning in the status-bar at the bottom right.

  • every time you run a query, you will get a warning message that you have some uncommitted changes. Note that this message can be switched off with Options > Run SQL > Prompt when in transaction mode.

  • you can Commit your changes with Transaction > Commit Trans

  • you can Rollback your changes with Transaction > Rollback Trans

  • you can also commit or rollback your changes by running the SQL statements Commit or Rollback in the Run SQL window. These commands can be included as part of an SQL script.

Once you have Committed or Rolled back, your transaction is terminated (except when AutoCommit is de-selected - see the next section).

Auto-Commit

By default, every update you make to the database is applied and committed to the database immediately. This is sometimes referred to as Auto-Commit mode.

You can switch off Auto-Commit mode by de-selecting Options > Technical Parameters > Auto Commit. When auto-commit is switched off:

  • AQT will start a transaction as soon as you connect to a database

  • your SQL will only be committed or rolled-back once you explicitly click on the Commit Trans or Rollback Trans items

  • once you click on Commit Trans or Rollback Trans, a new transaction will be started. Your AQT session will remain in a transaction until Auto Commit is de-selected.

If you change the Auto Commit option when you are signed onto a database, nothing immediately happens:

  • if you are currently in a transaction, the transaction will not be terminated until you commit or rollback

  • if you are not currenty in a transaction, one will not be started until you click on Begin Trans

If Auto Commit of off, Safe Update Mode will be disabled.

You must Commit or Rollback

Once you have made some changes, a message at the bottom of the screen will warn you of this. It is highly recommended that you Commit or Rollback promptly and do not leave uncommitted changes for any length of time.

  • if you are using the option Disconnect if Idle, the Disconnect may fail if there are uncommitted changes. If so, you will remain connected to the database.

  • if AQT crashes, or looses communication with the server, the results can be unpredictable. See AQT Closes without Committing Changes below.

  • before you close a database, or shut down AQT, you will be prompted to Commit your changes.

    • It is highly recommended that you click Yes to Commit.

    • Only click No if Commit fails. This might be the case, for instance, if your machine has lost contact with the database due to communication or other problem.

    • Clicking No will terminate AQT with either Committing or Rolling Back the changes. Only use this if all other options do not work. See the next section.

AQT Closes without Committing Changes

This can happen if:

  • you have uncommitted changes and AQT either crashes or looses communication with the server

  • when disconnecting from the database, you select the No option.

In this case, most databases will automatically rollback the outstanding changes. You will have lost any work you have done.

In some circumstances, the AQT process can remain in a suspended state on Windows if is has not terminated cleanly. In this case, it will continue to hold locks and may interfere with other tasks on the database. This may require a DBA to terminate the task on the server.

Other Notes on Transactions

  • if you are doing a large or critical change to your database, it is a good idea to run the change as a transaction. The ability to undo the change can be very useful should the change not do what you want!!

  • once you have run your SQL, you do not need to decide immediately whether to commit or rollback. You can display the table and run queries to determine whether your change has had the desired effect. Once you have determined this, you can choose whether to commit or rollback.

    Note that you should display the table using the same AQT session and connection as the one that made the change. If you display the data using a different AQT connection you may or may not see the unchanged version of the data.

  • while your transaction is active, the rows you have changed in the database are locked and cannot be updated by other users. In some circumstances, other users will not even be able to display the data. You should not leave your transaction active for any longer than you need to.

    If your transaction is active, and your PC or connection to the database fails, your changes will be rolled back. Once you have determined that your changes are OK, you should Commit immediately!

  • there are number of limitations of transactional control

    • some databases do not have the ability to rollback DDL statements (Create, Drop, Alter etc). These changes will be run outside of transactional control.

    • some statements (for instance Oracle Truncate) cannot be rolled back.

Including comments in your SQL

You can include comments in your SQL. These are generally used when you are running an SQL script, or including as part of a Stored Procedure, Trigger or Function Definition.

There are several ways for specifying comments:

  • line comment. This is indicated by two negative signs (eg. --). The remainder of the text on the line is the comment.

  • block comment. The start of the block comment is indicated by /*, the end of the comment by */. A block comment can cover text in part of a line, or can span multiple lines.

  • rem or @. For Oracle, a line starting with either REM or @ is a comment line.

For many databases, comments are not valid statements. It is expected that the script-processor (in this case AQT) will remove the comments from the SQL before passing it to the database for processing. Consequently, when running some SQL, AQT will:

  • remove all line comments from your SQL, except when the Remove Comments option is de-selected (this is discussed later).

  • not remove any block comments. You should only use block comments with databases that allow these are part of the SQL syntax.

  • for Oracle, remove all lines starting with rem or @

Remove Comments option

In some circumstances you may want the line comments to remain in your SQL. For instance, you are defining a Stored Procedure and want the comments to remain as part of the procedure definition.

In this case, go to Options > Run SQL and de-select Remove Comments from SQL. When this option is de-selected, AQT will retain all the line comments that appear within the body of your SQL statement. Any line comments appearing before the start of your SQL statement will be removed unless Retain comments before start of SQL is selected.

This option only affects the processing of line comments. Other comments are handled as described earlier.

Beware - if you de-selected this option and include line-comments within normal SQL (such as queries, updates, inserts etc), you may get a syntax error with some databases.

SQL history

The SQL history can be accessed from the Run SQL window. Click on the History icon, File > SQL History or Ctrl+H.

Show SQL for History Run From

You can run SQL from a number of places in AQT. By default, only SQL run from the Run SQL window is recorded in the SQL History. However, in Options > History you can specify to include SQL run:

The menu item Show History for SQL Run From allows you to specify which of these SQL statements are shown in the History display.

History Files

Information is (by default) written to aqt_history.txt in your default directory. You can change the directory where the history file(s) are written with Options > File Locations.

AQT holds SQL history indefinitely. If you history file becomes very large it can take a while to open the SQL History window.

If this happens, you should do one of the following:

  • purge the history file with File > Purge SQL History. You will be asked how many days data you wish to retain

  • you can use File > Save History as to save your history file prior to purging

  • switch to another history file with Options > History

  • use separate history files per week or month. How to do this is described in Options > History

If you have multiple History files, you can open a particular one with File > Open.

When are history records written?

History records are written to disk as soon as a query is run.

  • if AQT crashes or is terminated, you will not lose any history information.

  • if you are running multiple AQT sessions, each session will be able to see the history records from the other's session.

AQT writes a maximum of 20,000 bytes of an SQL statement and 1000 bytes of the result message to the history record. If you have a very large SQL statement you may notice that it is truncated when you retrieve it from the History. In this case you should save the SQL statement to disk rather than relying on the History to "save" it.

If you run the same SQL statement multiple times, this will only be written to the History the first time it is run.

History and Queries

When your SQL statement is a saved-query (eg. is SQL you have saved or retrieved from disk), the information on the query filename is recorded with the history record. When you click on the SQL in the history grid, the information in the status bar (at the bottom of the window) will tell you the name of this saved query.

When you retrieve the SQL from the History, AQT will associate the query with your SQL:

  • AQT will check the query-file to see if it has changed since the history record was written. If so, AQT will ask you whether you want to load the most recent version of the query from disk

  • the name of the query will appear in the window caption and status bar of the Run SQL window.

  • when you click on Save, AQT will save the query to disk without prompting for the name.

Database

When you retrieve some SQL, AQT will not change the database in the Run SQL window to the database in the history record.

However if you use Retrieve into new Window, AQT will set the database in this new Run SQL window to the database in the History record.

Procedure History

AQT also records the running of Stored Procedures from the Run Procedure window. The history entries for these are recorded in file aqt_prochistory.txt. These history records also includes the parameters passed to the stored procedure; this can be useful when rerunnng a procedure run earlier.

Many of the considerations of these history records are the same as for the SQL History.

Audit Feature

In addition to the SQL History feature, there is also the Audit Feature. While this records similar information, the audit feature has a number of different options more oriented to long-term recording of update (non-query) activity.

Copying SQL Text

You may wish to copy a number of SQL statements, for instance to replay a script. You can do this as follows: