Database query
The Database query (DB Query), a processor function, helps the user request data from a database and enrich the data in your stream. The data retrieved from the database can be from multiple tables.
To configure the Database query function, specify the following information:
The output from the DB Query is kept in the parameter queryResult (including any sub-parameter). The queryResult is added to the payload and is passed on to the next function in the stream. The output is made immutable, that is, it can not be edited or changed. If you still want to use and modify an object, you can use a script node to clone the complete content of queryResult, or any sub-parameter in it.
Note!
Cloning the complete queryResult will impact performance, resulting in high CPU load. The impact is related to the size of queryResult.
The following example shows how to clone queryResult.
Cloning queryResult
const lodash = require('lodash');
const result = lodash.cloneDeep(payload.queryResult);
result.foo = 'bar';Database info
The information required to access the external database.
Setting | Description |
|---|---|
Database type | This selects the database type. The supported options are:
|
Database name | Specify the name of the database you want to retrieve the data. |
Hostname or IP address | Specify the hostname or the IP address of the database. Note! The IP address must be a public IP address for Usage Engine to reach it. See Database query | Secure Database Connectivity below for information on how to set this up in a secure way. |
Port | Specify the port used by the database. When a Database Type is selected, the default port number is specified for each type of database. The default port numbers are:
|
Encryption | Note! If Oracle is selected as the Database type, the Encryption checkbox is displayed. Select the encryption type.  Oracle Encryption type dropdown options When the Encryption checkbox is enabled, select the configured Encryption type that matches the configuration on your database server from the dropdown list:
|
The configuration of TLS support for different database types are shown in the table below:
Database Type | TLS Configuration |
|---|---|
Oracle | For Oracle, the function always attempts to connect with TLS if the server supports it. If not, the connection falls back to an unencrypted channel. This approach ensures compatibility with the server while encouraging secure connections when available. In addition, Native Network Encryption is also supported. |
MS SQL | For MS SQL, TLS is required and always enabled. Unencrypted connections are not permitted. |
PostgreSQL | For PostgreSQL, the |
Note!
For PostgreSQL and Oracle databases, if you require all connections from Usage Engine Cloud Edition to your database to be encrypted, you must configure your database server to deny unsecured (non-TLS) connections. Maintaining a secure communication channel is a shared responsibility between you and Digital Route’s Cloud Edition.
Authentication
The authentication information to log in to the external database.
Setting | Description |
|---|---|
Username | Specify the username for the authentication. |
Password | Specify the password for the authentication. |
SQL SELECT statement
In this input field, you specify the statement that you can use to query data from the selected database. For example, SELECT, AS and OR, and so on.
Select the Enable cache checkbox to minimize the number of repeated database access requests using the same SELECT statement.
Enter a suitable number for Maximum items in cache (0 = no limit). The default value is 100. There is no upper limit to this value.
Note!
A high value can result in an error due to memory outage. If so, the stream fails with the following error message in the log:
“Stream failed to complete due to reaching resource limits”
However, this error is received for memory issues in general and can have other causes than a high value for the maximum number of items in the cache.
Enter a suitable number of seconds for Items are removed from cache after. The default value is 300. The minimum value is 1 and there is no upper limit to this value.
Example - SQL SELECT statements
Example using SELECT
SELECT *
FROM movies;Example using AS
SELECT name
AS 'movie_title'
FROM movies;Example using OR
SELECT name
FROM customers
WHERE state = "ca"
OR state = "ny";Note!
The Database query function supports Variable insertion when writing SQL statements.
Example - Interpolation
SELECT dep FROM emp WHERE id = ${payload.id} AND name = ${payload.name};