When creating a SQL connector call, you can create your SQL statement in the first tab “SQL”. Select the Mode parameter: simple, execute, query or transaction.
In the tab “Input Parameters”, the “mode” and “request” parameters are filled with the values from the “SQL” tab. These parameters are not optional and cannot be removed.
Their data type is “string” and the name cannot be changed.
An SQL connector call requires the two parameters “mode” and “request“.
You can choose between 3 different modes:
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.
The ‘Query Call’ is used to transfer the result from an SQL statement to the Simplifier.
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:).
Definition of the parameter in the Connector Call
|In order to use the parameter that has been defined in the SQL request via the connector call interface, a new connector call parameter must now be inserted, which corresponds to the parameter definition of the SQL request.
All connector call parameters that refer to an SQL request have the prefix /params/.
In the example above, a new parameter name /params/mail with the Simplifier data type ‘String’ has been created.
The parameter expressions ‘alias’, ‘description’ and ‘constant value’ are equivalent to the usual connector calls.
The result of a ‘Query Call’ can be assigned via the output parameters equivalent to the ‘Simple Call’.
Definition of a dynamic WHERE clause
A parameter called ‘where’ needs to be added to the connector call.
The whole WHERE clause of the statement can be passed to this parameter and needs to be left out in the ‘SQL’ tab.
There are several bad words that will result in a rejection of the call:
Please note that this filter will only trigger if it finds the exact same word in your WHERE clause.
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 are displayed in that way.
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.
To execute a SQL statement for each entry in a collection, you can use the repeatable statement mode. The collection can be passed via the parameter ‘repeatableCollection’.
When opening the Wizard, all tables available for the schema are displayed.
First Step – Select Queries:
In the first step, you can search for the table, and then select under “Actions” whether you want to select all columns (right icon) or only certain ones (left icon).
|If you only want to select certain ones, click on the left icon. A new popup opens in which you can select the desired columns. Then click OK.|
Second Step – Configure Connector Calls:
When you have edited the calls, click on “Create connectorcalls”.