Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 15 Next »

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:

  1. Database Info
  2. Authentication
  3. SQL Select Statement


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.

FieldDescription
Database Type

 This selects the database type. The supported options are:

  • MySQL
  • PostgreSQL
  • Microsoft SQL
Database NameSpecify the name of the database you want to retrieve the data.
Hostname or IP AddressSpecify the hostname or the IP Address of the database
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:

  • MySQL: 3306
  • PostgreSQL: 5432
  • Microsoft SQL: 1433

Authentication

The authentication information to log in to the external database.

FieldDescription
UsernameSpecify the username for the authentication.
PasswordSpecify 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). 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 minimum value is 1 and there is no upper limit to this value. 

SQL Select Statement Examples

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 for Interpolation
SELECT dep FROM emp WHERE id = ${id} AND name = ${name}; 
  • No labels