|
|
|
|
|
Your query can include substitutional parameters. When you use these you will be prompted for their values every time you run the query. This can be very 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 SCOTT.DEPT 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
When you run the query, a dialog window will prompt for the parameter description and parameter value for each parameter.
The parameter description will be saved with the query and is the text used to prompt for the parameter. In this example you might enter Department Number as the parameter description.
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: to display all employees whose names begin with a D, use: Select * from Employees WHERE LastName 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.
This screen also shows you the parameter type (character, decimal etc). This is for your information only.
Some databases, Oracle in particular, 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."
Some versions of the Oracle ODBC driver do provide parameter information – but it is varchar(999) irrespective of what the parameter really is. Oracle has decided that providing incorrect information is an improvement on providing no information. :)
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 (as it does not give the parameter type). For Oracle you can still load the column from a file by typing <aqtfile>filename into the parameter value.
Further information on the use of parameters is given in technical discussion on parameters.
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: