DB connectors builder
DB connector builder is a component that provides the ability to connect to external or internal databases. The internal database is available as an additional resource that can be added on the organization settings page.
How to create the first DB Connector?
The Connector's name should be longer than 1 symbol and shorter than 100. It can contain letters (Latin a-z A-Z), numbers 0-9, $, and underscores. It should begin with a letter or underscore character.
DB Connector has been created.
How to set up a DB Connector?
After creating a DB connector, you need to configure it and test the connection.
Select Database Type, URL, User, and Password. These field values are selected from Global variables: URL and User variables list. Password - from the passwords section of the Global variables pop-up.
Clicking on the field displays the list of up to five global variables previously added through the Global Variables component. Select the name of the required variable (if any) presented in the list.
To view all variables list or add a new one, click +Add new and see all.
One type of database is available in DB Connector builder: PostgreSQL. To select the database type, click the appropriate field.
How to select a URL?
To select the URL, click the appropriate field, then select the name of the variable (if any) presented in the list. To view the complete URL list or to add a new URL, click +Add new and see all.
By clicking + Add new and see all, the Select variables pop-up will open. To select a variable, click the required name, then the CONFIRM button.
If the required global variable is not in the list, click the ADD button.
Input the Name and Value of a new variable, and change a type if necessary. Then, click the ADD button.
All fields are required.
The Variable’s name should be longer than 1 symbol and shorter than 256. It can contain letters (Latin a-z A-Z), numbers 0-9, $, and underscores, beginning from a letter or an underscore character.
Value (String type) should be longer than 1 symbol and shorter than 257.
Value (Integer type) should be longer than 1 symbol and shorter than 20. Only numbers and minuses are allowed.
Value (Float type) should be longer than 1 symbol and shorter than 257. Only numbers, points, and minuses are allowed.
URL Pattern: jdbc:DBTYPE://URL:PORT/DBNAME?currentSchema=my_schema
jdbc - connection protocol and constant (required). DBTYPE - database type (required). URL - server name or connection URL. It can be a DNS or IP address (required). PORT - server port number (required). DBNAME - database name (required) . currentSchema=my_schema - schema name (optional).
If the schema is not specified, a query like that is applied to the «public» scheme by default. If you have access to a specific schema to work with, enter its full name either in the URL or the Query.
For example:
jdbc:postgresql://postgres:5432/postgresdb?currentSchema=sales
jdbc:postgresql://192.168.50.012:1416/subscription
How to select the USER?
To select the User, click the appropriate field, then select the name of the variable (if any) presented in the list. To view the complete User list or to add a new User, click +Add new and see all.
By clicking + Add new and see all, the Select variables pop-up will open. To select a variable click the required name, then click the CONFIRM button.
If the required global variable is not in the list, click the ADD button.
Input the Name and Value of a new variable, and change a type if necessary. Then, click the ADD button.
All fields are required.
The Variable’s name should be longer than 1 symbol and shorter than 256. It can contain letters (Latin a-z A-Z), numbers 0-9, $, and underscores, beginning from a letter or an underscore character.
Value (String type) should be longer than 1 symbol and shorter than 257.
Value (Integer type) should be longer than 1 symbol and shorter than 20. Only numbers and minuses are allowed.
Value (Float type) should be longer than 1 symbol and shorter than 257. Only numbers, points, and minuses are allowed.
How to select a password?
To select the Password, click the appropriate field, then select the name of the variable (if any) presented in the list. To view the complete Password list or to add a new Password, click + Add new and see all.
By clicking + Add new and see all, the Select variables pop-up will open. To select a password click the required name, then click the CONFIRM button.
If the required variable is not in the list, click the ADD button.
Enter the Name and Value of a new password, then click the ADD button.
All fields are required.
The Variable’s name should be longer than 1 symbol and shorter than 255. It can contain letters (Latin a-z A-Z), numbers 0-9, $, and an underscores, beginning from a letter or underscore character.
The Value should be longer than 1 symbol and shorter than 255. Only numbers, points, and minuses are allowed.
How to check the connection?
To establish a connection, select Database Type, URL, User, and Password. Then click the CHECK CONNECTION button. If the connection has been successfully established, the status "Connected" will be displayed in green.
If an error occurs, the error will be displayed in red. If the connection has not been established, verify the data you entered and try again.
You have connected to the database.
To add a Description to the DB Connector, click the description section and enter the desired value.
Preservation occurs when starting/switching work with another entity or closing a tab.
How to create connector queries?
The Connector's query name should be longer than 1 symbol and shorter than 255. It can contain letters (Latin a-z A-Z), numbers 0-9, $, and underscores. It should begin with a letter or underscore character.
Queries settings
Queries support all SQL operations, you can create tables, delete, add records to the database, etc.
If you need the platform to recognize a certain value as a parameter, the parameter must be enclosed in curly braces {..}, then the variable will appear in the testing section and will be available in the connector node settings to match the data used in a flow.
The internal database capacity is limited, if the memory capacity is full, you cannot add new data.
If you need to specify a schema to work with, and it is not included in the URL, you can specify it in the query itself by providing the full schema name before the table name, separated by a dot. If the schema is already specified in the URL, there is no need to include it in the query.
In cases where the schema is not specified in either the URL or the query, the system defaults to the "public" schema.
How to test the DB Connector query?
The Connector Builder has built-in functionality that allows the user to test queries against specific data and see the result. It also allows it to change its content if the request does not work correctly.
To test the query, you need to fill in Test values and select the necessary data type.
Make sure that the entered values match the expected types of data.
If the data type is an array, you should separate the parameter values with a comma.
Click the TEST button.
In the Query test results section, you can see the response of the database.
The output of the result is limited to three records. In the Message section, you will see a summary of the request or the error.
If you want to get the generated structure based on the results of the query to use it later during the flow creation, click the GENERATE button and select the parameters you need to add to the structure, and click the CONFIRM button.
If you need to have all the records matching your query as a result, then activate the checkbox "Does the result contain multiple rows?" Otherwise, your result will contain only the data of the first match.
Supported data types:
Boolean — value “true” or “false”
String — text, for example, “Peter Peterson”
Integer — number, for example, “22”
Float — floating-point or decimal number, for example, “0.1234”
Date — specify the date value in the format yyyy-mm-dd, for example ‘’1993-05-31’’
To select another structure, click the request body type field and select See All. You can select the desired structure by accessing the displayed structure selector.
Additional operations with the DB Connector
Select the DB Connector on the Project Explorer panel — left end panel. By clicking the right mouse button, a context menu opens through which a number of operations are available.
Let's see what operations are possible:
Edit or simply click on the name of the connector — allows you to open the DB Connector.
Copy — allows you to copy the connector to transfer it later..
Clone — allows you to create a clone of the connector. The cloned connector will have the same name as the Name of the parent connector + Clone(n).
Cut — allows you to cut connectors for transfer later.
Paste — allows you to paste the connector. This is not available unless you have previously used the Copy or Cut options.
Rename — allows you to rename the connector.
Delete — allows you to delete the connector.
Additional operations with Queries
Select Query on the queries section. By clicking the right mouse button, a context menu opens through which a number of operations are available.
Let's see what operations are possible:
Clone — allows you to create a clone of the query. The cloned query will have the same name as the Name of the parent query + Clone(n).
Rename — allows you to rename the query.
Delete — allows you to delete the query.
How to set environment variables to change DB settings using artifacts: war or jar?
To set environment variables use Tomcat containers.
Configuration parameters:
project.connector.{x}.connection.{y} = {value}
Where x is the name of the connector, and y is the property name (jdbcUrl, username, password).
Last updated
