SQL Script

Version

Application Scenario

To process data in a database, such as creating, updating, deleting, reading, and summarizing data, you can use the SQL Script node.

Data Processing and Data Insertion, Update, and Deletion

You can use SQL statements to process data.

After selecting the data source type and a data connection, you can write SQL statements to process data. Note that:

• The SQL statements must be written in accordance with the syntax of the connected database.

• You can reference custom parameters or assignment parameters from upstream nodes in SQL statements to perform data insertion, update, and deletion in the database. The following image is an example. For details about the parameter-referencing method in SQL Script, see Parameter Overview.

Note:

If the parameter to be referenced is generated by an upstream node with Closure Character enabled, you do not need to add quotation marks when referencing the parameter.

Calling Stored Procedures

You can call stored procedures when reading data from MySQL, Oracle, SQL Server, HP Vertica, GaussDB 200, IBM Db2, and SAP HANA databases. For details, see Calling Database Stored Procedures in Scheduled Tasks.

Setting SQL Execution Method

Starting from FineDataLink V4.1.11.2, SQL Script supports the execution of single and multiple SQL statements. You can input a stored procedure creation statement, and set SQL Execution Method to Execute A Single Statement, as shown in the following figure.

Note:

 SQL statements can be executed as a single statement. You can also add remarks to SQL statements, which do not affect execution.

SQL Execution Method Description	Description
Execute A Single Statement	The SQL script is sent to the database for execution without being split. Note: 1. Only statements for creating stored procedures are supported here; statements with command separators, such as delimiter, are not supported. 2. Stored procedures of Oracle databases are not supported.
Execute Multiple Statements (Separated by Semicolon (;)) Note: SQL script remarks cannot contain semicolons(;), as semicolons will invalidate the remark logic, causing SQL invalidity errors.	The system automatically splits the SQL script statements and submits them to the database in batches for processing.

CREATE PROCEDURE fdldemotest.Deletemoney1(IN nu INT)
BEGIN
    DELETE FROM  fdldemotest.Order_Customer_Information WHERE `Freight` = nu;
    select * from fdldemotest.Order_Customer_Information;
END

Click Run to execute the task. You can see the created stored procedure, as shown in the following figure.

This example demonstrates how to create two tables, insert data into one table, and store the summary data from that table in the other table using the SQL Script node in Data Development.

Creating a Database Table

1. Create a scheduled task, set the location and name, and enter Development Mode, as shown in the following figure.

2. Drag in an SQL Script node and configure it as shown in the following figure. The SQL statement creates two tables, order1 and order2, with the same fields ID and inventory, as shown in the following figure.

The SQL statement is as follows. 

USE `fdldemotest`;
CREATE TABLE IF NOT EXISTS `Order1` (
  `ID` INT,
  `inventory` INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `Order2` (
  `ID` INT,
  `inventory` INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Click Node Information to enter the tab page and rename the node Database Table Creation, as shown in the following figure.

Inserting Data into the Table

1. Drag in another SQL Script node and configure it as shown in the following figure. The SQL statement inserts 9 records into the order1 table, as shown in the following figure.

The SQL statement is as follows.

insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (1, 20);
insert into
  `fdldemotest`.`Order1`(ID, inventory)
values
  (1, 21);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (1, 22);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (2, 23);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (2, 24);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (2, 25);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (3, 26);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (3, 27);
insert into
  `fdldemotest`.`Order1` (ID, inventory)
values
  (3, 28);

2. Click Node Information to enter the tab page and rename the node Inserting Data into Order1, as shown in the following figure.

Summarizing Data

1. Drag in another SQL Script node and configure it as shown in the following figure. The SQL statement groups and summarizes data from Order1 and inserts the result data into Order2, as shown in the following figure.

The SQL statement is as follows.

insert into `fdldemotest`.`Order2`
select ID, sum(inventory) from `fdldemotest`.`Order1` group by ID

2. Click Node Information to enter the tab page and rename the node Inserting Grouped Data into Order2, as shown in the following figure.

Running the Task

Click Run to execute the task. After a successful execution, the log is displayed as shown in the following figure.

 After successful execution, you can see two new tables in the database:

Order1:

Order2:

Publishing the Task

After successful execution, click Publish to publish the task to Production Mode, as shown in the following figure.

Note:

You can set the task execution frequency in Production Mode.