Manual Configuration

When creating a SQL Connector call manually, you can see two tabs: ‘General’ and ‘Input Parameters’.

General

Here, you can configure the basic properties for your Connector call.

Mode

Use Dynamic WHERE Clause

Only set to true if you want to use a Dynamic WHERE Clause. Get further information in the documentation section ‘Mode: query‘.

Query

Insert the SQL statement for this Connector call.

To make a reference to an input parameter, set the input parameter name in colons, for example:

SELECT id, name, city FROM person WHERE id = :personalId:;

Output

Define the data type of the output parameter.

Input Parameters

In this tab, you can define the input parameters for your Connector call and their data type. When in your SQL statement, you have already mentioned an input parameter, an entry will be added automatically in the Input Parameter tab.

Advanced

Set the switch ‘Advanced’ in the upper right corner of the dialog to true to view more configuration options for the input and output parameters.

For example, you can define if the parameters are required or optional, or if they should have a constant value.

Mode: Simple

The ‘Simple Call’ corresponds to an unparameterized SQL call.

The SQL request is defined by a ‘String’ without variable substitution.

In order to be able to process the output of the SQL request, it must be defined in the output parameter of the connector call.

Currently, you can still map the entire JSON result to a single parameter.
This is possible by specifying a ‘/’ as the parameter name and the data type ‘String’ in the output parameters.

Mode: Query

The ‘Query Call’ is used to transfer the result from an SQL statement to the Simplifier.
(Usually, SELECT statements have a result)

The SQL request of the ‘Query Call’ is parameterized (in contrary to the ‘Simple Call’).

Definition of the constant value for the request parameter

The input value of the request parameter is represented by a name and a Simplifier data type. In our example, we used the name ‘mail’ and the data type ‘String’.
A parameter definition is initiated and ended with a colon. Between the colons is a pair of values, separated by a colon as well, which represents the name and the type of the parameter.
In a parameterized SQL request:

SELECT * FROM user_data WHERE email LIKE :mail:

If you use more values in an SQL statement you have to use a syntax separated by a comma, e.g. VALUES (:id:,:mail:)..

Dynamic WHERE clause

For executing dynamic-created WHERE Clauses – please read the following lines:

Let’s see how we need to configure our Connector Call to make use of this function.

Here you can see that you only need to write your regular SQL statement without the WHERE clause after it in the ‘SQL’ tab of the Connector Call.

In the input mapping we need to specify a new parameter called ‘where’ which acts as a placeholder for your WHERE clause that you want to append to the SQL statement.

Here’s how our result looks like if we use different values for the ‘where’ parameter and test the Connector Call in the Simplifier UI:

And this is a demonstration of the aforementioned bad word filter if you try to pass reserved words from the list (please see below) to the ‘where’ parameter.

For Security Reasons, we filter out bad commands that will result in a rejection of the call:

• ALTER
• CREATE
• DELETE
• DROP
• EXEC(UTE)
• INSERT (INTO)
• MERGE
• SELECT
• UPDATE
• UNION (ALL)

Please note that this filter will only trigger if it finds the exact same word in your WHERE clause.

Mode: Execute

The ‘Execute Call’ is quite similar to the ‘Query Call’. But with the ‘Execute Call’, no result is sent back to the Simplifier.

Typically SQL INSERTs, UPDATEs, or DELETEs could be used in that way.

Mode: Transaction

To execute multiple queries in a single transaction, use the mode ‘Transaction’. It bundles all executed statements as one database transaction.

The transaction mode has a delimiter setting, which is semicolon by default. Each statement is executed in the specified order and returns the result set. MySQL, MS SQL, and SQLite additionally return values for automatic increment.

Mode: Repeatable Statement

To execute a SQL statement for each entry in a collection, you can use the repeatable statement mode.

You can simply select the mode “repeatable statement”  from the dropdown menu as you are used to with other modes like ‘execute’, ‘query’ etc.

After selecting this mode you can write any SELECT, INSERT or UPDATE statement that you want to have executed for each entry in a collection that is passed to the Connector Call by means of a new parameter called ‘parameterCollection’.

You can specify it as an input parameter in your call and then pick the appropriate collection data type for it.

Repeatable Statement in combination with Transactions

Since Simplifier version 6.5.201, the repeatable statement mode can be used with multiple SQL statements (similar to the transaction mode), separated by a delimiter.

All defined statements are executed for all objects in the input parameter collection in one transaction.

In the example in the screenshot above, two SQL statements are defined in the SQL editor. Both statements will be executed for each object that is inside the input parameter collection.