Database Plugin - Macros - Execute SQL and SQL script

# EXECUTE_SQL {file} ON {database} AS {result}

What ?

This macro will load and execute an SQL query against the database, then the result will be returned under the name you typed for the last parameter.

Underlying instructions :

LOAD {file} AS __temp{%%rand1}.file

CONVERT __temp{%%rand1}.file TO file(param.relativedate) AS __temp{%%rand2}.file
CONVERT __temp{%%rand2}.file TO query.sql(query) AS __temp_{%%rand3}.query
EXECUTE execute WITH __temp_{%%rand3}.query ON {database} AS {result}

> Input :

• {file} : A SQL query (‘.sql’).
• {database} : The name (in the context) of the database to use (database type target).

Remark : The {file} must respect the same rules than a file which would serve to create an SKF query.sql type resource via the converter (From file to query.sql).

> Output :

• {result} : The name of the resource which will contain the result of the SQL query(result.sql type resource).

Example :

# EXECUTE_SQL path/to/query1.sql ON my_database AS result

Example with an INSERT query :

Database overview :

.sql file for the query :

SKF script :

The new employee has been inserted in the database :

Example with a SELECT query :

.sql file for the query :

SKF script :

To be able to see the result output, we added in this example the following instructions :

CONVERT result TO dataset.dbunit (dataset) USING $(tablename : user) AS dataset
CONVERT dataset TO xml (dbu.xml) AS my_xml_file

You can access to the result output in the following folder which contains temporary files :

Result output :

---

# EXECUTE_SQL_SCRIPT {file} ON {database} AS {result} WITH ENCODING {encoding} AND DELIMITER {delimiter}

What ?

This macro will load and execute an SQL script against the database, then the result will be returned under the name you typed for the last parameter.

Underlying instructions :

LOAD {file} AS __temp{%%rand1}.file
DEFINE $(encoding:{encoding}) AS encoding{%%rand1}.opts
DEFINE $(delimiter:{delimiter}) AS delimiter{%%rand1}.opts

CONVERT __temp{%%rand1}.file TO file(param.relativedate) AS __temp{%%rand2}.file USING encoding{%%rand1}.opts
CONVERT __temp{%%rand2}.file TO script.sql AS __temp_{%%rand3}.script USING encoding{%%rand1}.opts, delimiter{%%rand1}.opts
EXECUTE execute WITH __temp_{%%rand3}.script ON {database} AS {result}

> Input :

• {file} : An SQL script
• {database} : The name (in the context) of the database to use (database type target).
• Optional - {encoding} : Parameter representing the query file encoding. Default value : “UTF-8”.
• Optional - {delimiter} : Parameter representing the SQL block delimiter. Default value : “@@”. It can be used in conjunction with {encoding} or by itself - in which case {encoding} will take its value by default.

Remark : The {file} must respect the same rules as a file used to create an SKF script.sql type resource via the converter (From file to script.sql).

> Output :

• {result} : A free identifier for the result. As the ‘execute’ command with an sql script return an empty resource, this result resource will also be empty.

Example :

# EXECUTE_SQL_SCRIPT path/to/script.sql ON my_database AS result WITH ENCODING UTF-16 AND DELIMITER $$$

script.sql file :

DROP TABLE IF EXISTS  `skf`.`employee`;

CREATE TABLE `skf`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  PRIMARY KEY (`id`));

INSERT INTO `skf`.`employee` (`username`, `password`, `email`) VALUES ('bruceW', '?b@Tm@n!_1939', 'brucew@skf.com');
INSERT INTO `skf`.`employee` (`username`, `password`, `email`) VALUES ('jessicaJ', 'wh1sk3y', 'jessicaJ@skf.com');
INSERT INTO `skf`.`employee` (`username`, `password`, `email`) VALUES ('homerS', 'd0nuts', 'homers@skf.com');
INSERT INTO `skf`.`employee` (`username`, `password`, `email`) VALUES ('tonyS', 'tonyIsTheBest', 'tonys@skf.com');
INSERT INTO `skf`.`employee` (`username`, `password`, `email`) VALUES ('çàééééééèèèèèè', 'ççççççééééééé', 'test');

We encode the file in ISO-8859-1 and use special characters :

SKF script :

Database overview without specifying encoding in macro :

SKF script :

Database overview with encoding :

---

#EXECUTE_SQL_SCRIPT_BY_REMOVING_SEPARATOR {file} ON {database} AS {result}

What ?

This macro will load and execute an SQL script against the database, then the result will be returned under the name you typed for the last parameter. The separator (“;”) at the end of each SQL query of the script will be removed.

Underlying instructions:

LOAD {file} AS __temp{%%rand1}.file

CONVERT __temp{%%rand1}.file TO file(param.relativedate) AS __temp{%%rand2}.file
CONVERT __temp{%%rand2}.file TO script.sql AS __temp_{%%rand3}.script
EXECUTE execute WITH __temp_{%%rand3}.script ON {database} USING $(keep.separator:false) AS {result}

> Input :

• {file} : An SQL script.
• {database} : The name (in the context) of the database to use (database type target).

Remark : The {file} must respect the same rules than a file which would serve to create an SKF script.sql type resource via the converter (From file to script.sql).

> Output :

• {result} : A free identifier for the result. As the ‘execute’ command with an sql script returns an empty resource, this result resource will also be empty.

Example :

# EXECUTE_SQL_SCRIPT_BY_REMOVING_SEPARATOR path/to/my_script.sql ON my_database AS result