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.