DB (Database) Connector Builder
Last updated
Last updated
DB connector builder is a component that provides the ability to connect to external or internal SQL databases. The internal database is available as an additional resource that can be provisioned from the organization settings page.
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.
The DB Connector has now been created.
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.
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, clickon + Add new and see all.
By clicking on + Add new and see all, the Select variables pop-up dialog will open. To select a variable, click on the variable name, then on the CONFIRM button.
If the required global variable is not in the list, clickon 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
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.
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.
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.
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.
Available query types 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.
The DB connector builder comes with a built-in testing function 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.
Next, click on 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.
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.
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.
When deploying the project as a WAR or JAR package, you can use Java environment variables to override database connection settings accordingly to the deployment environment.
To set them in Apache Tomcat containers, use the following configuration parameters:
where {name} is the name of the connector, and {property} is the connection property name ("jdbcUrl", "username", or "password").
To create a new DB Connector, select the Сonnector icon on the Project Explorer panel — left end panel. Make sure you are in the Connector entity. Now create a DB Connector by clicking on the Add icon on the Explorer panel and select the DB Connector. To create a connector in a folder, you need to select the desired folder and then click on the Add icon. In the pop-up window, enter the DB Connector name and click the CREATE button.
To add a new query, click on the Add icon in the Queries section. Enter the query name in the pop-up window, and then click on the CREATE button.
To filter through the connector queries click on the Filter icon. Enter the name of the query in the field and enter the requested value. The match results will be presented in a general list.
