DB Query

The Database Query component allows you to make SQL-Queries. The component makes a connection to the specified SQL-like server using its configuration.

Supported databases are:

• MySQL
• MSSQL

Note Oracle and Postgres support are activated but not further implemented or validated. Feel free to try it out. Also, note that it might require third-party libraries.

Handlebars

This component lets you use Handlebars templates. More information about Handlebars can be found in this section

Configuration

Connection info

Connection String

The Connection string to the SQL server.

Example: mysql://username:password@localhost:3306/xill4

Advanced Configuration

Any additional configuration that should be used to connect to the database. For more information on what options can be used, refer to this documentation

Example:

{ 
  "keepAlive": true,
  "application_name": "Xill4"
}

This is an example for connecting to Postgres.

{ "options": {
    "trustServerCertificate": true,
    "tdsVersion": "7_1",
    "encrypt": false,
    "packetSize": 16368
}}

This setting could be used for connecting to MS SQL Server 2000 SP4. Note that "options" is only mandatory for MSSQL, not for others.

Client Certificate File

If TLS is enabled, a Client Certificate File is required. This field should contain the path to the certificate.

Example: .\client-cert.pem

Client Key File

If TLS is enabled, a Client Key File is required. This field should contain the path to the key.

Example: .\client-key.pem

Certificate Authority File

If TLS is enabled, a Certificate Authority File can be supplied. It is only required in case Self-Signed Certificates are used. This field should contain the path to the certificate.

Example: .\ca.pem

Query info

Query

The SQL Query to execute.

Examples: SELECT 1 SELECT * FROM users INSERT INTO users (full_name, email) VALUES ("alice", "alice@gmail.com")

Rate Limit It is possible that the query that will be executed returns too many records at once. This could cause all kinds of issues, like a decrease in performance but also unwanted side effects. To remedy this, you can enable rate limiting. It will perform the query in batches of a given limit, and interval.

When this toggle is enabled, it is required to update the query to include two magic variables; $skip and $limit or $end. These variables will be used to iterate over the rows and make subsequent calls to the database. In most scenarios, you will want to use $skip and $limit. $end is useful when working with a database that does not support the LIMIT (or similar) clause.

An example for using it with MySQL would be: SELECT * FROM users LIMIT $limit OFFSET $skip

An example for using it with DB2 would be:

SELECT * FROM (SELECT * FROM (SELECT ROW_NUMBER() OVER() AS rn, * FROM users) tmp WHERE rn <= $end) tmp2 WHERE rn > $skip

$end is used to limit the total amount of rows that will be returned. This is used to determine when to stop querying. But since it is based on the row number it needs to be increased by itself each iteration. This automatically done by the component and this is the difference between $end and $limit. Limit is a fixed number that is used to determine how many rows should be returned each iteration and is increased by itself each iteration.

Depending on the database dialect the exact usage of the $skip and $limit variables might change. Consult the documentation of your database's query language to see how to limit the number of rows returned.

Limit The maximum number of rows that should be collected in a single query execution.

Example: 1000

Interval The amount of time in milliseconds to wait between query executions.

Example: 5000

Inputs

Input

The incoming message isn't used unless the query uses a variable from this object. The incoming message can be empty if you don't need any variables.

Example:

//query in config:
SELECT * FROM documents WHERE kind="{{kind}}"

//incoming message:
{
  kind: "CONTAINER"
}

Outputs

Output

Each retrieved row, combined with the incoming message.

Example:

{
  value: {
    // the incoming message, if there isn't any this is an empty object
  },
  result : {
    // the resulting record
  }
}

Finished Output

Once all queries are executed, a message will be sent to this output. This indicates that the component is finished.

Example:

{
  value: {
    // the incoming message, if there isn't any this is an empty object
  },
  resultCount: 10 // The amount of documents that were send to `output`
}