Target addresses
Most SQL scripted targets support a target address with a syntax similar to:
{server=<database_system_identifier>;[instance=<script_variable_instance>;]script=<scriptname>;}The <database_system_identifier> is a proprietary addressing syntax unique to each vendor as below:
Vendor | Database system identifier |
|---|---|
DB2 | <system DSN> |
ODBC | <system DSN> |
Oracle | <TNS NAME> — <connect descriptor> |
OS400 | <server name> |
MS SQL | <server name>[/<MS SQL Instance name>]|<alias> |
Sybase | <server name> |
Cache | <server name> |
The <server name> is the DNS hostname.
The <script_variable_instance> is a value that can be passed from the address line to the script as the script callback %k:_instance% This callback can be used anywhere in the script to refer to a database name, a table name, or an argument for a stored procedure.
For example, a custom application that manages employees and contractors differently is targeted twice, each target using a unique varible to be passed to a common script:
Target ID: HR_EMPL
Target Address: mssql.example.com\HR/employee//common_script.sql
Target ID: HR_CONT
Target Address: mssql.example.com\HR/contractor//common_script.sql
where "mssql.example.com" is the DNS hostname of the Microsoft SQL Sever, "HR" is the Microsoft SQL Server instance name and "employee" or "contractor" is the unique variable passed to the common script named common_script.sql.
In common_script.sql, you can have the following SQL code incorporate the variable:
To use the variable as a table name:
"sql" = "select longid, empid, fullname, department, email from %k:_instance%"
Depending on the target address, this command would list users from either the employee or contractor table.
To use the variable in a stored procedure call:
"sql" = "GETCORPID(%k:_acctID%, %k:_instance%)"
This stored procedure requires two arguments, the account ID and the employment status (employee or contractor)
You can define additional address attributes by including the AddressAttr operation in the configuration script. See Address attributes for details.