Using Task Commands

 

1 About Task Command
2 Create New Task Command
3 Create SQL Command
4 Using Precondition for the Task
5 Using Data Import Commands

1 About Task Command

In SQLMessenger, you can execute SQL statements or other commands at the start or end of a task to process data. For example, you can execute an SQL statement at the beginning of the task to check if there is data to be sent, and if not, abort the task. These commands are collectively referred to as Task Commands.

A task can have multiple Task commands.

The system currently supports two types of commands:

SQL Command: Execute some SQL statements within the command. For example, check if there is data to be sent.

Data Import Command: Import data from an Excel spreadsheet into a specified database table within the command.

The following is a typical task execution flow:

A typical task execution flow

2 Create New Task Command

You can click the "New Command" button on the "Task Commands" tab in the task editor to open the "Create New Command" dialog box.

Click the "New Command" button to open the "Create New Command" dialog box

3 Create SQL Command

You can use SQL Commands to execute certain SQL statements at the start or end of a task to process data. Additionally, you can use SQL Commands to check if there is no data that needs to be processed, and if so, abort the task.

New SQL Command dialog box

In this dialog box:

Command Name

Enter a command name for the task (optional).

Execute the command in

Choosing the context for the command execution.

The task itself: Execute this command only within the task instance itself.

The sub-tasks generated by the task: Execute this command only in the subtask instances generated by the task instance.

The task itself and its generated sub-tasks: Execute this command in both the task instance itself and the subtask instances it generates.

The above three options are only valid for tasks of the "Information Distribute Task" type. Using Information Distribute Task

Called by other commands in the task: This command is called by other commands. The system does not execute this command actively.

Execute the command when

Choose the timing for command execution.

The task starts: Execute this command when the task instance starts.

The task is successful: Execute this command after the task instance successfully completes.

The task fails: Execute this command if the task fails.

The task is finished(regardless of success or failure): Execute this command after the task ends (whether successful or failed).

Set as a precondition for the task

Set the SQL command as a precondition for the task. The timing for task execution must be "Execute the command when the task starts".

Using Precondition for the Task

The evaluation method of "Precondition"

Select the evaluation method for "Precondition".

Using Precondition for the Task

Prompt Message

When the task's precondition is not met, the "Prompt Message" will be logged in the task's log. If the task is initiated by a Data Extraction Code, the "Prompt Message" will be included in the email sent to the requester, informing them of the reason for the task failure. For an interactive query task, the "Prompt Message" will be displayed on the query interface.

Using Precondition for the Task

SQL Statements

Enter the SQL statements to be executed. If you need to determine whether the precondition is met based on the execution result of the command, then the last SQL statement should be a SELECT statement.

If you modify data within a statement, you need to commit the modified data using COMMIT. You can choose to COMMIT immediately after the modification as per your requirements, or use another SQL command connected to the same data source to execute the COMMIT statement at the end of the task.

Here is an example SQL statement. It first inserts data into the sales_report table, then queries whether there is data to process. If there is no data, the task is aborted.

--insert sales data into sales_stat 
INSERT INTO sales_stat
            (state,
             city,
             stat_month,
             sales_volume,
             sales_amount)
SELECT state,
       city,
       stat_month,
       Count(1)    AS sales_volume,
       Sum(amount) AS sales_amount
FROM   sales_list
WHERE  state = 'California'
GROUP  BY state,
          city,
          stat_month;

--commit inserted data
COMMIT;

--check if there is any report data that needs to be sent
SELECT 1
FROM   sales_stat
WHERE  state = 'California'; 

Using Precondition for the Task, Using SQL Statements in Tasks

4 Using Precondition for the Task

The evaluation methods of "Precondition"

When the query returns a record set with rows, the precondition is met and the task continues to run: If the last SQL statement in the SQL command returns records, the precondition is considered successful, and the task continues execution. Otherwise, the task is aborted.

Note: When aggregate functions (such as the COUNT function) are used in an SQL query, the database always returns a single row of records. When the system performs the "Precondition" check, it will consider that the SQL has returned records, and thus the task will continue. Therefore, this scenario should be avoided.

You can modify the SQL statement to avoid using the COUNT function, or use "Custom precondition evaluation method" to prevent this issue.

Solution 1: Modify the SQL statement to avoid using the COUNT function

Solution 2: Use custom precondition evaluation method

When the value of the first cell returned by the query (=, <>, >, >=, <, <=) xx, ,the precondition is met and the task continues to run: You can customize the evaluation condition. If the last SQL statement in the SQL Command returns records, and the data in the first row and first column meets the given condition, the precondition is considered successful, and the task continues execution. Otherwise, the task is aborted.

5 Using Data Import Commands

In SQLMessenger, you can use the Data Import Command to import data from an Excel file into a specified database table when the task is starting. The task will continue to execute after the data has been successfully imported.

For detailed usage of the Data Import Command, please visit Using Data Import Commands