SQLMessenger User Manual - Using Global Variable in Tasks

Using Global Variable in Tasks

4 Dynamically Setting Global Variables with @@PutGlobalVar

1 What is Global Variable

SQLMessenger provides a global variable feature that allows variables to be shared across all tasks. These variables can simplify system maintenance when shared values are needed.

Global variables in SQLMessenger are of two types: Plaintext Global Variables and Encrypted Global Variables.

The differences between Plaintext Global Variables and Encrypted Global Variables

	Plaintext Global Variable	Encrypted Global Variable
Store	Stored as plaintext	Stored in encrypted format
Display	Value visible in task logs	Displays as "****" in task logs
Usage and effective scope	Accessible anywhere using @@GetGlobalVar	Accessible in HTTP commands though @@GetGlobalVarForHTTPCmd only
Usage suggestions	Recommended for non-confidential data	High security for confidential data (e.g., API Access Tokens), used in HTTP commands only

2 Configuring Global Variables

To configure global variables:

1. Open the "Main Menu" in the SQLMessenger console.

2. Click "System Settings" and then click on the "Global Variable Manager" button in the dialog.

Click the "System Settings" item on the MainMenu

Click the "Global Variable Manager" button on the "System Settings" dialog

Global Variable Manager

In the Global Variable Manager, you can create, modify, and delete global variables.

Any modifications or deletions to global variables take effect immediately.

Creating Global Variables

Click the "Add Plaintext Global Variable" or "Add Encrypted Global Variable" button to open the configuration dialog. For differences between global variables created by the two buttons, see "The differences between Plaintext Global Variables and Encrypted Global Variables"。

The Global Variable Config dialog

Configuration Dialog Options:

Var Name	Assign a unique name (case-insensitive) to the variable. It must consist of letters, digits, and underscores, and cannot start with a digit.
Effective Range	For encrypted variables, this must be "Used in HTTP Commands Only". For plaintext variables, it is "Anywhere".
Value Source	Set value source for the Global Variable. User Input During Configuration: Set vallue for the variable by the user during variable creation. Dynamically Set by Tasks at Runtime: Set vallue for the variable during task execution using the @@PutGlobalVar method. For example: --Set the result of the SQL query as the value of the global variable access_token. SELECT 'XXXX' AS access_token @@PutGlobalVar;
Var Value	It the "Value Source" option is "User Input During Configuration", set value for the Global Variable here. For encrypted global variables, the Var Value is only displayed in plaintext when the variable is created. Afterwards, it will be displayed as "●●●●●●" on the configuration interface.
Apply To Domain	For encrypted variables only. If the "Effective Range" otpion is "Used in HTTP Commands Only", specify domains (e.g., "microsoft.com" or "google.com") where the variable can be used. Multiple domains can be separated by semicolons. If the variable is allowed to be used on multiple domains, please separate the domains with a semicolon ";". When the variable is used, the system will compare the trailing characters of the host part in the URL called by the HTTP command with the domains configured here. If an attempt is made to use this variable on a domain other than those specified in "Apply To Domain", SQLMessenger will report an error and abort the task. For example: microsoft.com means the variable can be used on all URLs ending with "microsoft.com". login.microsoft.com means the variable can be used on all URLs ending with "login.microsoft.com". microsoft.com;azure.com means the variable can be used on all URLs ending with "microsoft.com" or "azure.com".
Comments	Enter a comment for the global variable (optional).

3 Using Global Variable

You can use the following methods to access global variables in your tasks:

@@GetGlobalVar(VarName)	Fetches a plaintext variable.	It can be used anywhere in a task where variables are allowed.	Example: Use global variable "SystemName" in an SQL statement. SELECT #[@@GetGlobalVar(SystemName)]# AS system_name
@@GetGlobalVarForHTTPCmd(VarName)	Fetches an encrypted variable.	It can be used in HTTP commands only.	Example: Use global variable "Access_Token" in an HTTP Command to build an HTTP request. SELECT ' url:https://slack.com/api/conversations.list?limit=100&types=public_channel,private_channel,mpim,im header:Authorization=Bearer #@@GetGlobalVarForHTTPCmd(Access_Token)# header:Content-type=application/x-www-form-urlencoded ' AS _http_request_

4 Dynamically Setting Global Variables with @@PutGlobalVar

SQLMessenger allows global variables to be dynamically set using SQL statements if their "Value Source" is configured as "Dynamically Set by Tasks at Runtime".

Set the "Value Source" of the global variable to "Dynamically Set by Tasks at Runtime"

You can use the @@PutGlobalVar method in task SQL statements to set the value of a global variable. When this method appears in an SQL statement, SQLMessenger will treat the column names retrieved by the query as the names of global variables and assign the value of the first row of data to those variables. For example, the following statement sets the value of the global variable "access_token" to "XXXX":

SELECT 'XXXX' AS access_token #@@PutGlobalVar#;

The following statement sets the value of the global variable "system_name" to "My Report System" and the value of "manager_name" to "Andy":

SELECT 'My Report System' AS system_name, 'Andy' AS manager_name #@@PutGlobalVar#;

Note: If the column names in the SQL query results do not exist in the global variable table, @@PutGlobalVar will throw an error, and the task may be terminated. Therefore, ensure that the SQL statement only returns column names corresponding to the global variable names that need to be set.

Using Variables in Tasks