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
• OracleDB (has been tested with OracleDB 23, support for older version might be limited)

note

Postgres is 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

• Name: Input

  • Description: 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

• Name: Output

  • Description: 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
      }
    }

• Name: Finished

  • Description: 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`
    }

Usage with OracleDB

To use the DB Query component with OracleDB, you will need to install some additional Oracle drivers. You can find the drivers on the Oracle website. You will need to download the Basic and SDK packages for your specific OS.

After downloading, you need to follow the following steps to install the drivers:

1. Extract the downloaded files to a location on your system. For windows we recommend extracting the files to C:\oracle\instantclient. For Linux, we recommend extracting the files to /opt/oracle/instantclient.
2. Update your LD_LIBRARY_PATH environment variable to include the location of the extracted files.
3. Update your ORACLE_HOME environment variable to include the location of the extracted files.

After installing the drivers, you can use the DB Query component with OracleDB. You will need to provide the connection string in the following format: oracle://username:password@host:port/service_name.