Script Variables

Script Variables provide you with the flexibility of using a single script in multiple scenarios or allows to define common expression used in multiple statements. For example if you need to run single script against multiple databases, instead of modifying the script for each database, you can use a scripting variable for the database name. Your define Script Variable by using following syntax:

where:

• = - optional assignment operator. If present assigns default value and on first reference user will be asked for value
• : - optional operator. If present than user will not be asked for value
• var_name - variable name
• var_value - optional variable value
• CHAR, VARCHAR, DATE, INT, NUMBER - optional variable type. If present helps to correctly convert var_value to type

To reference script variable you use following syntax: $(var_name).

Script variables can appear anywhere in text. var_value can spawn multiple lines. In this case last character on the line has to be "\".

Script variables become available either after $DEFINE statement executed or after script containing $DEFINE loaded. You do not need to explicitly use $define to create script variables. If script variable used without definition than user will be asked for the value on first use. In case of Oracle you can use &var_name syntax in this case.

Script variables maintain values until they are changed by another $DEFINE command. To reset variable just type $DEFINE var_name

You can view and modify Script Variables by choosing menu Connection->Edit Script Variables ...

Examples of using Script Variables:

Simple substitution

$DEFINE var_date = 01/01/07
$DEFINE var_string 'String_Value'


SELECT * FROM table_name where date_field = '$(var_date)' and string_field = $(var_string) 

The above select statement will be converted to:

SELECT * FROM table_name where date_field = '01/01/07' and string_field = 'String_Value'

Parameterized Query with substitution parameters

$DEFINE var_date
$DEFINE var_string


SELECT * FROM table_name where date_field = '$(var_date)' and string_field = $(var_string)

Parameterized Query with type safe substitution parameters

$DEFINE var_date DATE
$DEFINE var_string VARCHAR


SELECT * FROM table_name where date_field = $(var_date) and string_field = $(var_string)

If you try to execute script above then window will popup asking to supply values for defined variables which do not have values assigned to them. This will be happen every time you execute above script because $DEFINE statement will reset variable value to nothing. If Script variable contains type declaration then SqlDbx will try to convert specified values to corresponding type. If conversion fails then query will not be executed. For example if you specify string_value for var_string then it will be enclosed in single braces automatically or if type declared as DATE and your database is Oracle then it will be replaced by function TO_DATE(var_date, 'YYYY-MM-DD'). If no type specified or variable value provided then no conversion will apply.

Multiple queries using same set of columns

$DEFINE common_columns TRADE_DATE, PRICE, SIZE, TRANSACT_TIME \
						CLIENT_ID, SECURITY_NAME


SELECT $(common_columns) FROM trades_today;
SELECT $(common_columns) FROM trades_yesterday; 

Script Variables naming rules

• Variable names must not contain white space characters
• Variable names must not have the same form as a variable expression, like $(var)
• Scripting variable names are not case-insensitive

Predefined Variables

SqlDbx defines following glogbal variables:

• $(USER) - returns Windows login
• $(TODAY) - returns today's date
• $(NOW) - returns today's date and time
• $(CURRENT_SCHEMA) - returns current schema
• $(NEW_UUID) - returns new UUID