A Temp Variable is a flexible variable type provided by SQLMessenger. You can use @@PutTempVar in SQL statements within a task to store the first row of query results in a Temp Variable, and retrieve these values later using @@GetTempVar.
Unlike task variables, Temp Variables do not need to be declared during task configuration. They are created by @@PutTempVar and are only valid within the task instance that generates them.
For example, the following SQL statement saves the first row of query results into the temp variable table:
--Query the user_name, age, and city of the user with user_id=1 and store them in the temp variable table of the task instance.
SELECT
user_name, age, city
FROM user_info
WHREE user_id = 1 #@@PutTempVar#
After executing this statement, three Temp Variables named user_name, age, and city are added to the Temp Variable table for the task instance.
You can retrieve these values elsewhere within the same task instance using @@GetTempVar:
--Query the list of users with the same user_name, age, and city as the previously queried user.
SELECT
user_id, user_name, age, city, create_time
FROM user_info
WHERE user_name = #[@@GetTempVar(user_name)]#
AND age = #[@@GetTempVar(age)]#
AND city = #[@@GetTempVar(city)]#
You can use @@PutTempVar in SQL statements to store the first row of query results into the temp variable table. The variable names correspond to the column names of the SQL query results.
For example, the following SQL statement saves the first row of query results into the Temp Variable table. After execution, the table will contain three variables: user_name, age, and city.
--Query the user_name, age, and city of the user with user_id=1 and store them in the temp variable table.
SELECT
user_name, age, city
FROM user_info
WHREE user_id = 1 #@@PutTempVar#
Variable names are case-insensitive. If a variable already exists, its value will be overwritten.
Note: When using @@PutTempVar, column names in the SQL query results must consist only of letters, numbers, and underscores. Otherwise, the task will throw an error.
You can use @@GetTempVar to retrieve the values of Temp Variables within a task.
For example, the following SQL statement retrieves the values of user_name, age, and city from the Temp Variable table and uses them as query parameters:
--Query the list of users with the same user_name, age, and city as the previously queried user.
SELECT
user_id, user_name, age, city, create_time
FROM user_info
WHERE user_name = #[@@GetTempVar(user_name)]#
AND age = #[@@GetTempVar(age)]#
AND city = #[@@GetTempVar(city)]#
In addition to @@PutTempVar, SQLMessenger provides the @@PutEncryptedTempVar method to create encrypted temp variables.
When a Temp Variable is set using @@PutEncryptedTempVar, its value is encrypted and stored in the temp variable table of the task instance. These values can only be retrieved using the @@GetEncryptedTempVarForHTTPCmd method in HTTP commands. In task logs, the variable values are displayed as "****".
This method is suitable for securely storing sensitive data, such as Access Tokens for Web APIs.
The usage of @@PutEncryptedTempVar is similar to @@PutTempVar. However, both methods cannot be used together in the same SQL statement.
For example, the following SQL statement creates an encrypted Temp Variable named access_token:
SELECT
'XXXX' AS access_token
FROM user_info
WHERE user_id = 1 #@@PutEncryptedTempVar#;
You can use @@GetEncryptedTempVarForHTTPCmd in HTTP command SQL statements to retrieve the values of encrypted Temp Variables.
For example, the following SQL statement retrieves the encrypted variable access_token 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 #@@GetEncryptedTempVarForHTTPCmd(access_token)#
header:Content-type=application/x-www-form-urlencoded
'
AS _http_request_