Parameterized Scripts (Revision #0)

The Query Analyzer supports parameterized scripts. From the Query Analyzer Toolbar, the user may enable/disable the ability to support parameterized

scripts. There is also an option to keep parameterized scripts enabled for all query analyzers based on vendor and version under File > Options > Query Analyzer.

By enabling Parameterized Scripts, the user may have scripts with defined parameters so that Aqua Data Studio will prompt for the values of the parameters

before executing the script.

Executing a Parameterized Script

Parameters are defined by a leading "&" when editing SQL statements within the Query Analyzer. When executed, a Parameters window appears with the parameter

names and values in a grid. Input the values within the Value column and click Execute to continue executing the script with those inserted values.

Simple parameter query
   Example: SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID = &id

Parameter query in a quoted string
   Example: SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = ’\&name’

Parameters are also supported in server side comments. Client side and server side comments may be defined in File > Options > Scripts for each database. If a parameter is defined in a client side comment, it will be ignored. Parameters in server side comments will be prompted and replaced.

Parameters also support default values. Default values may be defined by appending "=" and the value after the name of the parameter. Default values may be single

quoted to support values with spaces and special characters.

Simple parameter query
   Example: SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID = &id=10

Parameter query in a quoted string
   Example: SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = ’\&name=’Sales Dept’’

Oracle OCI Connections

There are two options for Parameters for Oracle OCI connections. "Parameterized Script" and "Parameterized Script - Quoted String Backlash Enabled".

Parameterized Script - Quoted String Backslash Enabled. Default for the use of a backslash with a quoted string. Checked or Enabled requires that a backslash be used when parameterizing a quoted string like SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '\&name'. Unchecked or disabled does not require a backslash in a quoted string like SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME = '&name' 

Date and time expressions in parameter values scripts.

This feature allows users to specify a default value of a parameter to be a date expression. Go to File > Options > General > Parameterized Scripts to specify the tags for the date and time expressions and to prompt it.

• File > Options > General > Parameterized Scripts
• DateTime Tag: dt
• Date Tag:
• Time Tag:
• DateTime at Midnight Tag
• Prompt for parameter values
• Date & Time Expressions - Configurable in File > Options > Parameterized Scripts

Current Units
-------------------
d = current date
t = current
dt = current date time
dm = current date at midnight


Time Units
--------------------
y Year
M Month
d day
h Hour
m Minute
s Second
S Millisecond

Examples:

{d-1y} = current date - 1 year
{dt-2h-3m-8s-340S} = current date and time minus 2 hours, minus 3 minutes, minus 8 seconds, minus 340 milliseconds
{dm+6h} = current date at midnight, plus 6 hours ... basically today at 6am.

- SQL Example:
[This query will return a list of orders that have occurred in the last month]
SELECT * FROM ORDERS WHERE ORDER_DATE > &myparam={dt-1M}

select * from orders
where orderdate > &myparam={dt-10y-4M-18d}