In SQLMessenger, you can configure and execute SQL statements to perform both query and modification operations on your database. You can also use variables in SQL statements to perform dynamic queries.
Using SQL statements in tasks
SQLMessenger provides an SQL statement editor where you can input and run your SQL statements.
SQL Statement Editor
Data Source |
Select the database to connect to. SQLMessenger will connect to this database to execute SQL statements. |
Options |
You can set the variable delimiter for the SQL statement here. The default variable delimiter is "##" (e.g., "#variable_name#"). If it conflicts with your database (such as SQL Server), you can change the variable delimiter to "${}" (e.g.,"${variable_name}") here.
Set the variable delimiter for the SQL statement |
Test SQL |
Run the SQL statement you have edited and retrieve the query result. If variables are used in the SQL statement, you need to input some values for the variables to run this SQL statement. Note: After clicking "Test SQL," the system will run the SQL statement you entered on the data source. If there are INSERT, UPDATE, DELETE, or other DDL statements in the query, they will be executed, and your database may be modified. Therefore, before executing the SQL statement, please carefully check to ensure that the SQL statement will not adversely affect your data. |
3 Using Variables in SQL Statements
You can use variables in SQL statements as query conditions to achieve dynamic queries.
Insert variable into SQL statement
SQLMessenger supports two variable processing methods: Regular Variables and JDBC Variables.
Tips: Generally speaking, for better execution speed and security, we recommend that you use JDBC Variables in SQL statements. Only in cases where you use the "IN" keyword or dynamically determine the query table name, should you consider using Regular Variables.
You can select the 
menu item from the context menu to insert a regular variable into the selected position of the SQL statement.
When the system processes a regular variable, it will first check the security of the variable value. Once the variable value passes the check, the system will replace the corresponding variable in the SQL statement with this value. For example:
SELECT *
FROM sales_stat
WHERE city_name = '#CityName#' --"#CityName#" represents the use of the task variable value of "CityName" as a query condition here.
;
Before executing this SQL statement, the system will check the security of the variable value, and then it will replace "#CityName#" in the statement with the value of the task variable "CityName". Assuming the value of the task variable "CityName" is "Washington", then the final executed statement becomes:
SELECT *
FROM sales_stat
WHERE city_name
= 'Washington' --"#CityName#" is replaced with the value of the variable "CityName".
;
3.2 Security Check for Regular Variables
By default, the system will perform a security check on variable values before replacing them in SQL statements. The rules for the check are as follows:
Position of Variables |
Checking Rules |
Solution |
|---|---|---|
The variable appears within a pair of single quotes, e.g., WHERE city_name = '#VarName#'. |
Single quotes within the variable value must appear in pairs and consecutively. |
Replace single quotes within the variable value with two consecutive single quotes when generating the variable value, or use JDBC-type variables in the SQL statement. |
The variable appears within a pair of double quotes, e.g., WHERE city_name = "#VarName#". |
Double quotes within the variable value must appear in pairs and consecutively. |
Replace double quotes within the variable value with two consecutive single quotes when generating the variable value, or use JDBC-type variables in the SQL statement. |
The variable appears outside of single quotes or double quotes in the statement, e.g., WHERE record_id = #VarName#. |
(1) The variable value can be a number. (2) When the variable value contains non-numeric characters, no other symbols besides the underscore can appear. |
Use JDBC Variables in the SQL statement. |
SQLMessenger supports the use of JDBC-type variables in SQL statements. The use of JDBC-type variables in statements has the following advantages:
(1) Faster execution speed. When using regular variables, the system replaces the variable text in the SQL statement with the variable value during command execution, which may result in different textual content for each executed SQL statement. However, when using JDBC variables, the system replaces the variable text in the statement with "?" (the question mark, which is the JDBC variable placeholder). The variable value is then processed by the database's JDBC driver, allowing for faster processing speed. Additionally, it can efficiently utilize the SQL statement caching feature of the database, reducing the resource consumption of the database.
(2) It can more effectively ensure the security of SQL statements. For example, when using regular variables, if the variable value contains single quotes or double quotes, it may fail the system's variable value security check, resulting in the task's execution failure. Using JDBC variables can avoid this issue.
For example, the following statement queries the sales report for a city, where the city name is given by the task variable "CityName".
SELECT *
FROM sales_stat
WHERE city_name = #[CityName]# ;
In this statement, #[CityName]# represents the place in the SQL statement where the value of the task variable CityName will be used for the query. When the task runs, #[CityName]# will be converted into a JDBC query parameter, and the value of the task variable CityName will be used as the value of that query parameter.
The final SQL statement that is actually executed is as follows:
SELECT *
FROM sales_stat
WHERE city_name = ?
--#[CityName]# is replaced with the JDBC parameter placeholder "?", and the system passes the value of the task variable "CityName" to this parameter.
;
3.4 Considerations for Using JDBC Variables
(1) The data type of JDBC variables in SQLMessenger is unified as VARCHAR. In practical use, you may need to convert the data type of JDBC parameters in the SQL statement according to the data type of the table being operated on, for example:
/*
In a PostgreSQL database, the table t_stat has a field named 'month' which is of the INT type.
If a JDBC parameter named "Month" with a varchar data type is directly used in a query, it will result in an error stating "data type mismatch".
Therefore, the "Month" parameter needs to be converted to an INT type in the query.
*/
SELECT *
FROM t_stat
WHERE month = #[month]#::int4
(2) The variable placeholder for JDBC variables should not be enclosed within double quotes or single quotes, for example, the following statement will result in an error stating "variable [CityName] not found" when executed.
SELECT *
FROM t_stat
WHERE city_name = '#[CityName]#'
The correct format should be:
SELECT *
FROM t_stat
WHERE city_name = #[CityName]#
(3) JDBC variables can only appear in the query conditions of a statement or in the SET clause of an UPDATE statement. For example:
SELECT *
FROM table
WHERE column1 = #[VarName1]#
AND column2 = #[VarName2]#
or
UPDATE table
SET column1 = #[VarName1]#
WHERE column1 = #[VarName2]#
(4) JDBC variables cannot appear in table names, column names, or other object names that are being queried, for example, the following SQL will result in an error when executed:
SELECT *
FROM t_stat_#[Month]#
In this statement, the "Month" part for the table name should use a regular variable, for example:
SELECT *
FROM t_stat_#Month#
(5) Due to limitations of the JDBC driver, when using the "IN" keyword in an SQL query, JDBC parameters cannot be utilized. For example, the execution result of the following query statement may be incorrect:
SELECT *
FROM t_stat
WHERE area_name IN (#[CityNames]#)
When "CityNames" contains only one city name (e.g., "Washington"), the query result is correct. However, if the value of "CityNames" is multiple city names (e.g., 'Washington', 'Pomona', 'Tulsa', 'Denton'), since the JDBC driver passes the entire value of "CityNames" as a single string variable to the database, the query condition effectively becomes (WHERE area_name = 'Washington,Pomona,Tulsa,Denton'), which results in an empty query result.
To avoid this issue, you can use regular variables after the IN keyword, for example:
SELECT *
FROM t_stat
WHERE area_name IN (#CityNames#)
Tips: In this mode, before running this SQL statement, the system will perform security checks on each element in the list of variable values.
You can also split the list of parameter values into a temporary table and then modify the SQL query condition to use that table, for example:
SELECT *
FROM t_stat
WHERE city_name IN (SELECT city_name
FROM t_city) --Modifying the query using a temporary table approach.
We recommend using the second approach of using a temporary table to handle the "IN" parameter issue.
4 Using Semicolon to Separate Multiple Statements
If you have multiple SQL statements to execute, please separate each SQL statement with a semicolon (;). For example:
--Insert sales statistics data
INSERT INTO sales_stat
(stat_month,
sales_volume,
sales_amount)
SELECT stat_month,
sales_volume,
sales_amount
FROM sales_detail
WHERE sales_month = #[month]#;
--Commit inserted data
COMMIT;
--Query sales report
SELECT *
FROM sales_stat
WHERE sales_month = #[month]#;
5 Using the COMMIT Statement to Commit Changes
By default, when SQLMessenger connects to a database, "transaction support" is enabled. After executing data modification statements such as INSERT, UPDATE, DELETE in a task, the changes made need to be explicitly committed using the COMMIT statement for them to take effect. For example:
INSERT INTO t_stat
(area_name,
month,
sales_volume,
sales_amount)
VALUES ('Washington',
202401,
180,
1992.3);
--Explicitly commit transactions after inserting data
COMMIT;
If the COMMIT statement is not used to commit the transaction, all changes will be rolled back when the task ends.
If you do not need to use transaction support, you can select the "Auto Commit (Do not use transactions)" option in the "Advanced Options" of the data source configuration dialog. After selecting this option, modifications made on this data source will be committed immediately without the need for a COMMIT statement.
Select the "Auto Commit (Do not use transactions)" option
Note: After selecting this option, the changes made by INSERT, UPDATE, and DELETE statements will be committed immediately upon execution. There may be potential data consistency issues in the event of task execution failure. Please use it with caution.