Parameters & Multiple Statements

Using ODBC Parameters in your query

Your query can include parameters. When you use these you will be prompted for their values every time you run the query. This can be useful when setting up generic queries for saving and using later.

To include a parameter, simply code a ? where you would code a data value in your query.

Example:

Select * from TEST.DEPARTMENT where DEPTNO = ?

never put the ? inside quotes, even if it is a character value.

You can only use a parameter where you would use a data value. You cannot use it to substitute for a table or column name. For instance you cannot say Select * from ? where DEPTNO = 10

Comparison with User-Defined Parameters

• ODBC Parameters are a part of the ODBC specification and are a feature provided by the ODBC Driver. There are a number of limitations on their use, and some ODBC Driver have bugs in this area. In general we recommend using User-Defined Parameters, which provide more flexibility.

• ODBC Parameters are useful in some circumstances, such as loading LOB values (using Load from File). This is the only way of loading LOBs.

• Queries containing ODBC Parameters can be run using other

Running a Query with Parameters

When you run the query, a dialog window will prompt for the Parameter description and Parameter value for each parameter.

• The Parameter description will normally be blank. You can enter a value here; it will be saved with the query and used when you subsequently run the query.

• The Parameter value is the value to be used. For character strings you should not put quotes around the parameter.

• If you need to use wild cards in the parameter value, you must use LIKE and enclose the parameter value in % %. Example:

  Select * from TEST.DEPARTMENT where DEPTNAME LIKE ?

  then enter %D% as the parameter value.

• When specifying a parameter value you cannot use the name of another column or a special value / register (such as NULL, CURRENT DATE etc). These will be interpreted as character strings and will not have the desired result.

• The dialog also shows you the Parameter type (character, decimal etc). This is for your information only.

More on Parameters

Parameters are a feature provided by the ODBC Driver. Different ODBC Drivers implement parameters with varying degrees of success.

Parameter Types not available

Some databases, such as older versions of Oracle, do not provide information about the parameter types. In this case the Parameter Type will be blank and you will get the message “Your database does not report on the Type of your parameters.”

Parameter Types Inaccurate

Some databases are unable to determine the correct parameter type, so give a generic value

• recent versions of the Oracle ODBC driver gives the parameter type as varchar(999)

• MS Access gives the parameter type as CHAR(100)

• MySQL gives the parameter type as VARCHAR(255)

Load from File

If you are inserting / updating a LOB data type, a button Load from File will appear beside the parameter. Click on this to specify the file you wish to load into the LOB column (the text <aqtfile>filename will appear in the parameter value).

You will not see this button for Oracle/Access/MySQL (as these do not give accurate parameter type). For these databases you can still load the column from a file by typing <aqtfile>filename into the parameter value.

More Information

Further information on the use of parameters is given in technical discussion on parameters.

Changing the Default Values for your Parameter

When you save a query that has parameters, AQT will save default values for the parameters. These values are taken from the parameters supplied when you last ran the query. If you wish to change these default parameter values:

• run the query again, supply new parameter values, then save the query. The default values will be replaced with the new values you supplied.

• alternatively, edit the query file. The parameter description and values are held as control statements in the file. You can change these.

Using User-Defined Parameters in your query

Because of the limitations of ODBC Parameters, AQT provides a more powerful mechanism for parameters.

User-defined parameters have the following features:

• you can set a parameter to a value such as the current date or a value returned by a query

• you can prompt the user to select a value from a drop-down list, or date / file picker

• parameter values stay in effect for your AQT session (or across AQT sessions) so do not need to be re-prompted

AQT parameters are a feature specific to AQT and are not recognized by other query tools.

Example

In this example:

• we define a parameter workdep and prompt the user to provide a value for it

• the user can select a value from a drop-down list

• the parameter value is referenced in the query by preceding it with a $

Defining a Parameter

You can define a parameter in a number of different ways:

• in the SQL window use Parameters > View/Edit Parameter to add or edit a parameter

• in the SQL window, use the Parms tab on the listboxes to view and edit parameters.

• use the queryparm command to query the user for the value of a parameter

• use the setparm command to set a parameter to a particular value

The Add Parameter dialog makes it easy to add a queryparm or setparm command to your query. When you add one of these commands, it will be placed at the top of your SQL at the end of the existing query queryparm / setparm commands.

Parameters Dialog

You open this with (from the SQL window) Parameters > View/Edit Parameter

You can define a parameter in a number of different ways:

• in the SQL window use Parameters > View/Edit Parameter to add or edit a parameter

• in the SQL window, use the Parms tab on the listboxes to view and edit parameters.

• use the queryparm command to query the user for the value of a parameter

• use the setparm command to set a parameter to a particular value

The Add Parameter dialog makes it easy to add a queryparm or setparm command to your query. When you add one of these commands, it will be placed at the top of your SQL at the end of the existing query queryparm / setparm commands.

Parameters Dialog

You open this with (from the SQL window) Parameters > View/Edit Parameter

Using the Add Parameter dialog (setparm)

To add a parameter to your query, select Parameters > Add Parameter.

This can be used to add one of the following commands to your query:

• a setparm statement (set a parameter to a value)

• a queryparm statement (prompt the user to enter the value)

The example below is for setparm. See the next topic for queryparm.

Special Value

You can set the parameter to a variety of special values:

In the following example, the parameter will be set to the last day of the current month.

Using the Add Parameter dialog (queryparm)

To prompt the user for a parameter value, check Prompt the user for a value. AQT will add a queryparm command to your query.

• Display Name is the name of the parameter on the parameter prompt

• Prompt user with has a number of options for how the user will be prompted. One of these is List of values, where the user is prompted with a dropdown list of values to choose from.

• Select from list can either be a fixed list of values, or a query to fetch the values from the database.

• IDParm. In this example, the Select from list query returns two columns. The first column is the value displayed in the dropdown list. When the user selects a value from the list, the parameter (depart) is set the selected value; the IDParm parameter (deptno) is set to the second column from the query.

  This is useful for displaying a list of "meaningful" values to the user, yet passing an internal value to the query.

When the query is run, the user will prompted as per the following screenshot.

Notes on Parameters

Enabling the use of User-Defined Parameters

For batch-mode AQT, User-defined Parameters are always enabled.

For online AQT, User-defined Parameters are (by default) not enabled. You can enable this by selecting Enable Parameters in the Parameters dialog. Click on OK. This setting is saved between sessions, so you only have to do this once.

Parameter Character

On the Parameters window, you can specify whether parameters are to be indicated with a $ or a colon. This is done with the Indicate parameters with option. By default, this will be a $. The only choices are $ or colon.

Parameter Replacement

• There is no end-delimiter for the parameters. Parameter replacement is "dumb". AQT just replaces the $ + parameter name with the parameter value. If you have a parameter called cust (with value SMITH) and a parameter called custcode (with value JONES) then $custcode will be substituted as SMITHcode.

  You can avoid this by having fuller names for parameters - for example: custname, custcode, custid, custcountry etc rather than just cust.

• If a parameter is not found, it will not be replaced. You will not get a warning.

• Parameter names are case independent (so $cust and $Cust are equivalent).

• If you want to see what your SQL looks like after the parameters have been replaced, from the Run SQL window go Parameters > Show SQL after parameter replacement

Saving Parameter values

If Options > General > Remember user-parms between sessions is selected, AQT will save the parameter values between sessions.

The file they are saved in is parms.txt in your Default Directory. The parms are saved whenever AQT shuts down successfully, or when OK is clicked on the User-Defined Parameters window.

Parameters are often used when running batch scripts. Any changes to parameters done during batch scripts are not saved.

Referencing Another User-Defined Parameter

A parameter can reference parameter.

Example:

In this, the value of rundate is the name of another parameter.

When you specify:

select * from accounts where transaction_date > '$rundate'

the following will be run:

select * from accounts where transaction_date > '2007-03-31'

Using the function Parameters > Show SQL after parameter replacement is useful for seeing how parameters will be resolved.

Deleting Parameters

You can delete parameters with the delparm statement. You might wish to do this at the start of a batch script. Examples:

--aqt delparm,parm=custcode (deletes a single parameter)

--aqt delparm,parm=*cust* (deletes all parms containing cust)

--aqt delparm,parm=* (deletes all parameters)

Where Clause Syntax

This section provides a brief summary of the syntax of the Where clause. For fuller information, consult the SQL Reference manual for your database.

Search Table

The AQT Search Table window uses a simplified version of the where clause. One significant difference is that Table Search uses * as the wild-card, whereas normal SQL syntax uses %.

Case sensitivity

Some databases are case sensitive, some are not. For instance Sybase, SQL Server and MS Access are case insensitive, Oracle and DB2 are case sensitive. This also depends to some extent on how the database has been configured.

For databases that are case sensitive, you will may to code your Where clause such as:

where ucase(cust_name) like '%SMITH%'

Where clauses such as this are often a poor option as the database will not use an index (if there is an index on cust_name). However if you wish to do a case-insensitive compare you have little choice other than to use this.

LIKE Clause

This LIKE clause allows you to do a wild-card search of a column. With this:

Examples:

Dates

Most databases are very fussy about how dates are specified. For more on this see Date and time columns.

MS Access

For MS Access, date values must be encapsulated with #s instead of quotes. Example:

where datecol = #2007-12-09#

AND and OR clauses

If you have multiple Where clauses, you connect these with AND and OR clauses.

• if you code AND, the rows of your table must meet both conditions. You are likely to get less rows returned.

• if you code OR, the rows of your table must meet either condition.You are likely to get more rows returned.

Running multiple statements

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

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

Formatting of statements in the SQL script

• the statements may be on the same or different lines (line breaks are ignored, unless inside quotes).

• the SQL statements are (by default) delimited by a semicolon. However this can be changed - see the following section.

• two minus signs (--) appearing anywhere is taken as a line comment – all the text from there to the end of the line will be removed from your SQL (see Including comments in your SQL for more on this).

• for Oracle any line starting with REM or @ will be ignored.

• some databases allow comments to be placed between /* and */. AQT will not remove such comments from your SQL; these will be passed to your database along with your SQL. This is useful if you wish to use comments in Stored Procedure text.

Setting the Statement Delimiter

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

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

--aqt options,sdelim=@

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

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

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

Two ways of running the script

• By default, when you run the script, you will be shown the Run Multiple SQL statements dialog. This allows you to view / amend the individual statements in your script, plus gives you more control of pausing and restarting.

• You can bypass the use of the Run Multiple SQL statements dialog by selecting Run > When running multiple statements, run directly. When this option is selected, AQT will run the statements in the script without showing the Run Multiple SQL Statements dialog.

  This option is suitable when your script is unlikely to have errors, so you do not need the pause/restart capability.

  If any statement gets an error, the script will stop processing.

Run Multiple SQL Statements dialog

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

• review your SQL statements before running them

• amend any statements if necessary

• prevent any particular statement from running (by clicking on the Run? Column so that it changes from Y to N)

• start the script at a particular statement

• change some of the Run options

• save your script if you have made changes to it

Auto-Commit

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

Stop on Error

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

Error Codes

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

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

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

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

Parameters

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

• when this option is checked (the default), AQT will prompt for parameters for every time it runs an SQL statements that contains parameters.

• when this option is not checked, AQT will prompt for parameters only once. The values you supply will be taken as the parameters in subsequent SQL statements. This can be useful if you have multiple statements that have the same parameter values.

Multiple Select Statements

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

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

• all results produced by the script go to the same window (eg. are appended one after another). If you specify Options > Run SQL > Include a blank line betweeen query results, then AQT will write a blank line between the results from the different queries.

• if the script stops (either with an error, or you hit Pause) then, when you restart by clicking on Run, AQT will continue appending the data to the window.

• If you click on Select > All (which is a way to rerun the script from the start), AQT will write the results to the start of a new window.

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

Export Data

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

• You will not be prompted for the export options right away, instead this will be done when AQT runs the first SQL statement that produces a result-set.

• All subsequent result-sets will go the same file (with the same export options) except that the file mode will be changed to Append. In other words, all the data will be appended to the same file. Excel works slightly differently (see next point).

• For Excel, if you specify a Worksheet name, all the result-sets will be appended to the same Worksheet. If you do not specify a Worksheet name, every result-set will go to a different Worksheet.

• If the script stops (either with an error, or you hit Pause) then, when you restart by clicking on Run, AQT will continue writing to the file in Append mode.

• If you click on Select > All (which is a way to rerun the script from the start), AQT will reset the export options. You will be re-prompted for them.

• If you do not want all your output to go to the same file, you can code export specifications in your script to change the export options in the middle of your script.

• If you want your output to go to different files, you can use the keywords <schema> and <table> in your file names. When the export is run, <schema> and <table> are replaced with the schema and table-name of the table you are exporting. <schema> and <table> can also be used in the Worksheet name (if exporting to Excel), or insert_tab (if exporting as insert statements).

Viewing only some Statements

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

• only those that have failed

• only those that have succeeded

• only those that have not yet run

• only those that have updated / deleted zero rows

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

Removing Comments

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

Amending the Script

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

Having amended the script, you can save your changes:

• use Edit > Copy script back to Run SQL window to update the script in the SQL window with the changes you have made.

• use Edit > Copy script to file to save the script to a file.

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

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

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

Run from File

When you open a query, there is an option Run from File. This is used for running a large SQL script. When you use Run from File, the SQL statements are run directly from your file without first being written to the SQL window. The avoids the overhead of loading the SQL into the window; in addition, you can use this feature to start processing at a particular line in the file.

When you select Run from File:

• text such as the following is placed in the SQL window: <file>bigfile.sql <startline>1

• you can, if you wish, change this to another file, or use a different value for startline. This makes it easy to restart from a particular point in a file.

• run the file by clicking on Run or hitting F5.

• the Run Multiple SQL statements window will be displayed, showing you the individual SQL statements in the file. Run these by clicking on Run or F5. See Run Multiple SQL Statements for more information on this window.

You can open multiple files using Run from File. If you have multiple <file> statements in the Run SQL window (on separate lines), AQT will open all these files in the Run Multiple Statements window.

Multiple SQL Windows

By default, you have a single SQL window. Whenever you go to the SQL window (you can do this from a number of places in AQT) AQT will use this one SQL window.

You can open another SQL window by Ctrl+N or File > Open another SQL Window. Whenever you go to the SQL window, it will go to the first one and leave this second one untouched.

You can specify that AQT is to open a new SQL window every time it goes to an SQL window. You do this by setting Options > Window Behaviour > Always Open a new SQL Window.

Even with this option on, AQT will reuse an SQL window in some circumstances. For instance the Query Builder and Data Display windows remember the SQL windows they came from. If you select File > Send SQL > SQL Window (or click on the SQL icon) AQT will return the SQL to the originating SQL window. If there is no originating SQL window, AQT will create a new one. If you select Send SQL a subsequent time, AQT will use the SQL window it has created. This behaviour allows you to go through a number of iterations of building SQL without causing an excessive number of SQL windows to be created.

By default, when you open a new SQL window (with Ctrl-N) it will have the same SQL as the existing window. If you prefer new SQL windows to open as blank, set Options > Window Behaviour > When new SQL window is opened, set to blank.