Script Variables

Script Variables provide you with the flexibility of using a single script in multiple scenarios. 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
• var_name - variable name
• var_value- optional variable value
• CHAR, VARCHAR, ... - optional variable type

To reference script variable you use following syntax: for all databases except Oracle: $(var_name). In case of Oracle: &var_name

Script variables can appear anywhere in text

Example 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

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 variable 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.

Script variables maintain values until they changed by DEFINE command.

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 variables:

• $(TODAY)
• $(NOW)
• $(CURRENT_SCHEMA)