The SQLite Mapper Connector introduces small, fast, self-contained, server-less, full-featured SQL database engine allowing you to write and run SQL statments and thus manipulate data using the SQLite. For SQLite syntax and full list of supported commands and functions of the database engine, see documentation. The SQLite Mapper provides an alternative to another connector for manipulating data - JS Mapper.
Possible uses of the Multi input step include facilitating branch synchronization and enabling the acceptance of multiple inputs within integration processes. By incorporating this feature, you can streamline complex integration tasks and achieve better scalability, parallel processing, and system responsiveness. For more information, refer directly to the article dedicated to the Multi-Input Step
Accessing input data
If the input schema is selected, the input data will be predefined in the InputData table (see Predefined Variables). Input data are always in the form of an array, even if data contains a single record only.
Input structure will be always defined even without available input.
Predefined variables
| Variable | Data type | Description |
|---|---|---|
TaskRunID |
any | ID of the currently executed TaskRun. |
InputData |
table | The table filled with input data. This table will be deleted when the connector finishes the run. |
DataCheckpoint |
any | The value of stored column on last row from last TaskRun. |
EndpointTokenID |
any | When a task is executed via linked endpoint, the variable contains ID of the token used for the authorization (see table below). |
EndpointTokenName |
any | When a task is executed via linked endpoint, the variable contains Name of the token used for the authorization (see table below). |
LocalVariable |
temporary table | Store data specific to a single task run. |
| Authorization token | EndpointTokenID |
EndpointTokenName |
|---|---|---|
| None | null |
null |
| Primary | 0 |
"Primary" |
| Secondary | ID of the used secondary token |
Name of the used seconday token |
Returning output data
The output of the connector expects an array of objects that must match the output schema in the structure. The defined Statement represents the body of the function whose output is parsed and returned as the output of the connector itself. The last line of the Statement (see Configuration) must therefore be SELECT clause
Example
If the input schema is defined:
| Column | Data type |
|---|---|
| UserID | integer |
| UserLogin | string |
| CreatedDate | DateTime |
| EndpointTokenName | string |
If the output schema is defined:
| Column | Data type |
|---|---|
| ID | integer |
| Login | string |
and you want to return some custom data to the output, use the folowing statement:
SELECT
15 as ID,
'user15' as Login
or you want to return records from input to the output, use the following statement:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData
or you want to return records from the input to the output using some Predefined variables, use the following statement:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData, variables
WHERE InputData.EndpointTokenName = variables.EndpointTokenName
or use the Data checkpoint column value in Predefined variables for filtering:
SELECT
UserID as ID,
UserLogin as Login
FROM InputData, variables
WHERE
strftime('%s', InputData.CreatedDate) > strftime('%s', variables.DataCheckpoint)
ORDER BY
InputData.CreatedDate
Configuration
SQLite Mapping Statement configuration
Statement
SQLite statement to be executed using SQLite engine loaded in-memory. For a full list of supported commands and functions of this engine, see documentation.
The output of the connector expects an array of objects that must match the output schema in structure. The defined Statement represents the body of the function whose output is parsed and returned as the output of the connector itself. Variables TaskRunID, EndpointID, EndpointName or DataStorageColumn (i.e. Date) can be accessed by variables.<var> clause. The last line of the Statement must therefore be some SELECT clause.
Example
SELECT * FROM InputData
SELECT * FROM InputData, variables WHERE InputData.EndpointID = variables.EndpointID
Data checkpoint column
Name of column from which value will be stored for another runs as text. Value is taken from column on last row and can be accessed from SQLite with prefix variables. Example: variables.DataCheckpoint.
SELECT
Number AS Number,
'asdasd' AS Base64,
Date AS Date,
Json AS Json,
Double AS Double,
variables.DataCheckpoint AS Bool
FROM InputData, variables
Input & Output Schema
Input
Data schema is optional
The connector does not expect a specific schema. The required data structure can be achieved by correct configuration. Although the selected connector doesn't require a schema generally, the individual integration task step may need to match the output data structure of the preceding task step and use a data schema selected from the repository or create a new input schema.
Output
Data schema is mandatory
The connector requires mandatory input or output data schema, which must be selected by the user from the existing data schema repository or a new one must be created. The connector will fail without structured data.
Release notes
3.2.0
- Added support for Local variables.
3.1.0
- Multi input feature included - more info here.
3.0.6
- Connection and commands timeout is respecting task step timeout.
3.0.2
- Implementation of debug script generating.
3.0.1
- Map input data structure to output data structure using SQLite in-memory SQL engine.