CALL SQL

The CALL SQL operator allows you to execute arbitrary SQL queries on the specified database, using data available on the input bus, as well as design and global parameters.

Depending on the configuration, the operator can execute:

DML commands (e.g. SELECT, INSERT, UPDATE, DELETE) - for data manipulation,
DDL commands (e.g. CREATE, ALTER, DROP) - to manage the database structure.

By default, an SQL query is executed for each record on the input bus. However, it is possible to use Single execution mode, so that the query will be executed only once - regardless of the number of records.

In the construction of the query you can use:

bits of the data bus (individually or an array of bits in JSON format in Single execution mode),
project parameters
global variables
GRAVITY meta-language
text substitution functions, allowing you to dynamically modify the content of the query based on the processed data.

Thanks to its flexible syntax and the ability to bind queries to input data, the CALL SQL operator is ideal for database integration, including:

retrieving and populating auxiliary data,
saving processing results,
implementing business logic directly in the database.

An example of a GRAVITY project using the CALL SQL operator:

OPERATOR CONFIGURATION

The operator is used to execute SQL in the database, so first of all you need to indicate the connection to the selected database.

The method of defining the connection to the database has already been described in the case of the

INPUT SQL operator and is identical.

You have two variants of using the operator ( STATEMENT tab).

The first option is the variant of calling SQL for each record, located in the data stream on the input bus.

However, you can opt for the variant of calling the query once for all records, in which case the SINGLE EXECUTION option should be checked.

If in Single execution mode you want to use data values from the bus it is possible using the @GET_JSON[] function.

SQL can return a result that is processed in a way, depending on the TARGET RESULT setting (see illustration above):

VALUE INTO AN EXISTING COLUMN → the result of the SQL statement will be placed in the indicated output bus column (the rest of the input bus data retains its values);

If you do not have a column on the data bus where you can store the result of the query, consider creating one using the

WIDTH BUSBAR operator.

The result of the command in this case should be understood as the data returned in the first column of the first record returned by the SQL. The remaining data will be ignored.

VALUES FROM FIRST ROW TO THE EXISTING COLUMNS → the column values of the SQL query can be written to already existing columns of the bus. By selecting this option you will see an additional Column matching tab.

Match the corresponding bus bit to the column returned from the Column name query . You can match multiple columns to multiple bits. If you do not see the columns returned by the query use the Refresh sql columns button.

If you do not have columns on the bus where you can pass the result of the query, consider creating it using the

WIDTH BUSBAR operator.

If the query returns more than one row only the data from the first row will be substituted for the bus bits.

CREATE A BUSBAR FROM RESULT → the output bus is converted into the result of the SQL statement.

If you call the query for each bus record, the output bus will be the sum of the data from all calls.

ADD TO THE BUSBAR THE DATA FROM THE QUERY → The columns retrieved from the SQL query will be added to the output bus data.

If the query returns more than one record for the bus record being processed, the bus data will be duplicated as many times as the number of records returned by the SQL query

IGNORE RESULT → the data returned by the SQL command is ignored; the bus output will have a data stream identical to the input bus;

The SEPARATE TRANSACTION field allows you to run SQL in a separate transaction, which will be terminated after the query is executed.

The new connection to the separate transaction is used to execute the SQL statement for all the records of the input bus.

If the project fails, and the Call SQL operator has already managed to execute the SQL statement successfully, using SEPARATE TRANSACTION mode there will be no way to roll back the changes. For more on transaction management, read the Managing Database Transactions in a Project chapter.

SQL QUERY CONSTRUCTION

In the STATEMENT field, you can edit the SQL statement. Access to the input bus bits, design parameters and built-in functions is provided by the dictionary. You can test the entire expression by selecting TEST.

The project parameters and bus bits are inserted into the query in the form @[name_bit]and are subject to parsing to eliminate SQL injection attack.

Example of using a bit named "id" in a query:

select id, name from g.customer where id=@id

In addition to the ability to insert bus bits and design parameters, it is possible to insert built-in functions and meta-language.

@EXECUTEID → a parameter storing the unique identifier of the process being processed. You can use it in any SQL query.

Example of use in a query:

INSERT INTO etl.articles(index, index_name, code, unit, processId) VALUES (@index, @index_name, @code, @unit, @EXECUTEID);

@GET_JSON[] → parameter that returns an array of JSON objects consisting of all the indicated bus bits.

Definition example:

We assume that the bus looks as follows:

`id`	`name`	`unit`
1	name 1	t
2	name 1	t

executing the query:

select @GET_JSON[@id, @name] as json_data

we will get the following value of the json_data field:

[{"id": 1,"name":"name 1","unit":"t"},{"id":2,"name":"name 2","unit":"t"}]

Depending on the setting of the SINGLE EXECUTION switch we will get one record on the output bus (with the field selected) or as many records as there are records on the input bus.

You can use the @GET_JSON[] parameter using the SINGLE EXECUTION mode to make only one query, and you care about using the data from the bus.

Using the database function, you can convert JSON arrays to the data table data format and use in a SQL query.

An example for a PostgreSQL database using the json_to_recordset function:

select id, name from json_to_recordset(cast(@GET_JSON[@id, @name] as json)) as x(id int, name text)

Full usage in the query:

select id, index, index_name, code, unit from etl.articles where id in ( select id from json_to_recordset(cast(@GET_JSON[@id, @name] as json)) as x(id int, name text) )

Since the value returned by the @GET_JSON[] parameter is in text form it should be cast to the json type first

{metalanguage_phrase} → a function that evaluates a condition: {[expression] ? “expression satisfied” : “expression not satisfied”}

Example usage in SQL:

select id, name from g.customer {@id != null && @id != 0 ? " where and id = @id" : ""}

{IF_NOT_EMPTY} → builds a metalanguage phrase that checks if the selected bit has a value and is not null.

Example usage in a query:

select id, name from g.customer {@id != null && @id != 0 ? " where id = @id" : "''"}

{TO_SQLTEXT} → function that adds parsable text (to prevent SQL injection attack) in apostrophes.

Example usage in a query:

select id, name, {TO_SQLTEXT(@email)} as email from g.customer

{TO_PURE_VALUE} → function that adds text without any intervention: {TO_PURE_VALUE([text pasted])}

Example use in a query:

select id, name from {TO_PURE_VALUE(@schema)}.customer

Inserting text in this way is vulnerable to a SQL injection attack, because the inserted text is not subject to parsing. Use this type only for data you are absolutely sure of.