Connection info
Connection StringThe Connection string to the SQL server.
Example: mysql://username:password@localhost:3306/xill4
Advanced ConfigurationAny additional configuration that should be used to connect to the database. For more information on what options can be used, refer to this documentation
The
Connection Options input field uses HandleBars. For more information, please read
HandleBars 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 FileIf TLS is enabled, a Client Certificate File is required. This field should contain the path to the certificate.
Example: .\client-cert.pem
Client Key FileIf TLS is enabled, a Client Key File is required. This field should contain the path to the key.
Example: .\client-key.pem
Certificate Authority FileIf 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
QueryThe SQL Query to execute.
The
Query input field uses HandleBars. For more information, please read
HandleBarsExamples:
SELECT 1
SELECT * FROM users
INSERT INTO users (full_name, email) VALUES ("alice", "alice@gmail.com")
Rate LimitIt 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.
LimitThe maximum number of rows that should be collected in a single query execution.
Example: 1000
IntervalThe amount of time in milliseconds to wait between query executions.
Example: 5000