SQL task

Prerequisites

To use this feature, you need XXL-JOB engine version 3.0.1 or later and XXL-JOB agent version 1.0.1 or later.

Procedure

Log in to the MSE XXL-JOB console and select a Region from the top menu bar.
On the task scheduling > XXL-JOB page, click the target instance. Make sure its engine version is 3.0.1 or later.
In the left navigation pane, select Application Management and click Create Application. Set Standard Application to Standard Application, and then click OK.
Connect an executor. The MSE-developed executor is recommended because it supports SQL task execution.
In the left navigation pane, go to Data Source Management and create a data source.
In the left navigation pane, go to Task Management and click Add Task. Set the Task Type to SQL, and select the appropriate Data Source Type and Data Source Instance. For SQL type, choose Query or Update, and enter your script in the SQL statement field.

SQL task parameters

Parameter	Description	Example
Data source type	Supported types: + MySQL + PostgreSQL	MySQL
Data source instance	The data source instance that the task connects to.	test
SQL type	The SQL execution mode.	query
SQL statement	The SQL script to execute.	select count(*) as num from person where age > 18
Pre-SQL statement	An SQL update statement to run before the main statement.	update person set age=age+1 where name='Bob'
Post-SQL statement	An SQL update statement to run after the main statement.	delete from person where name='Bob'

Update an SQL script

On the Running Mode page, locate your task and click Edit Script in the Running Mode column.

In the task list, the Running Mode column for your task displays an SQL tag and an Edit Script link. Click Edit Script to open the script editor.

Each time you save a script, you can define a custom version number for comparing the current script with previous versions.

In the script editor, enable the Compare Versions switch and select a previous version from the version drop-down list in the upper-right corner (for example, v1). The editor displays the differences between the Current Version and the selected Version History (Read-only) in a side-by-side view.

Configure custom parameters

Input parameters

In an SQL script, use ${variable_name} to reference values from the task's custom input parameters, flow system parameters, or the flow instance context.

For example, you can enter select count(*) as num from person where age > ${v_age} in the SQL statement field. Here, ${v_age} is a reference to an input parameter.

In the Custom Variables area of the task configuration, configure the corresponding input parameters: set the Parameter Name to v_age, select IN for Direction, select INTEGER for Type, and enter an actual value (such as 16) for Default Value. You can click + Add Parameter to add more parameters.

Output parameters

When a task is part of a flow, output parameters pass execution results to the flow instance context for use by subsequent nodes.

For a query that returns a single row, you can map a field from the result set to an output parameter by using the field's name as the parameter key.

For example, with the SQL statement select count(*) as num from person where age > ${v_age}, the num field from the result set can be used as the key for an output parameter.

In the Custom Variables section, configure the output parameter: set the parameter name to num, select OUT for Direction, and select INTEGER for Type. The value is optional.

For an SQL update, you can map the number of affected rows to a single output parameter.

For example, when the SQL statement update person set age=age+1 where name = 'Bob' executes, the number of affected rows is automatically mapped to the output parameter.

In the Custom Variables area, set the parameter name to num, select OUT for Direction, and select INTEGER for Type to output the number of updated rows to the flow context.