ProcessMIX Guide
  • Platform Overview
    • Introduction
    • Platform Purpose
    • Main Concepts
      • Organization Structure
      • Visual Language
      • Project Development Environment (IDE)
      • Deployment and Integration
      • Administration and Troubleshooting
    • First Steps
    • Change Log
      • 5.0.0
      • 5.0.5
      • 5.0.5.1
      • 5.0.5.2
      • 5.0.5.3
      • 5.0.5.4
  • Language Reference Guide
    • Overview
    • Data Structures and Types
      • Core Types (pdk.core)
      • Dictionary (pdk.asset.dictionary)
      • Scorecard (pdk.asset.scorecard)
      • Blockchain (pdk.blockchain)
      • Exceptions (pdk.ex)
      • Input/Output (pdk.io)
      • JSON Web Token (pdk.jwt)
      • Networking (pdk.net)
      • HTTP (pdk.net.http)
      • SOAP/Web Services (pdk.net.soap)
      • XML/DOM (pdk.net.xml)
      • S3 (pdk.s3)
      • SQL (pdk.sql)
      • Postgres (pdk.db.postgre)
      • Util (pdk.util)
      • Default Data Structure Field Values
    • Flows
      • Variables
        • Local Flow Variables
        • Node Variables
      • Flow Node Types
        • Core Nodes
          • In
          • Out
          • Assign
          • If
          • Switch
          • Iterator
          • Function Call
          • Subflow
          • Raise Exception
          • Exception Handler
          • Validator
        • Connector Nodes
          • Database Begin Transaction
          • Database Commit Transaction
          • Database Rollback Transaction
          • Database Query Executor
          • EVM Blockchain Functions
          • EVM Blockchain Smart Contract Functions
          • REST Service Executor
          • SOAP Service Executor
          • S3 Connector
          • SMB Connector
      • Expressions
      • Built-In Functions
        • Expression functions
          • pdk.util.Any
          • pdk.util.Array
          • pdk.util.Blockchain
          • pdk.util.Cast
          • pdk.util.Codec
          • pdk.util.Crypto
          • pdk.util.Date
          • pdk.util.File
          • pdk.util.Json
          • pdk.util.JWT
          • pdk.util.Map
          • pdk.util.Math
          • pdk.util.String
          • pdk.util.Util
        • Node functions
          • pdk.flow.Any
          • pdk.flow.Array
          • pdk.flow.File
          • pdk.flow.JsonArray
          • pdk.flow.JsonObject
          • pdk.flow.HttpRequest
          • pdk.flow.HttpResponse
          • pdk.flow.Logger
          • pdk.flow.Map
          • pdk.flow.Xml
            • pdk.flow.xml.XmlAttr
            • pdk.flow.xml.XmlElement
            • pdk.flow.xml.XmlNode
            • pdk.flow.xml.XmlDocument
            • pdk.flow.xml.XmlDocumentType
            • pdk.flow.xml.XmlDOMConfiguration
            • pdk.flow.xml.XmlDOMImplementation
            • pdk.flow.xml.XmlProcessingInstruction
            • pdk.flow.xml.XmlTypeInfo
            • pdk.flow.xml.XmlCharacterData
            • pdk.flow.xml.XmlText
            • pdk.flow.xml.XmlNamedNodeMap
          • pdk.node.BlockchainEth
          • pdk.node.S3
          • pdk.node.Smb
      • Exceptions
        • Checked Exceptions
        • Runtime Exceptions
    • Assets
      • Dictionary
      • Decision Table
      • Scorecard
      • PMML
    • Connectors
      • DB Connector
        • Prepared Query
        • Dynamic Query
        • Query input parameters
        • Stored Procedure
      • REST Connector
      • SOAP Connector
      • S3 Connector
      • SMB Connector
      • EVM Blockchain Connector
    • Global Variables
    • Appendices
      • Overview of Database Transactions
      • Reserved Words
  • Project Development Environment (IDE)
    • Project Explorer Panel
    • Flow Builder
    • Flow Node Editor
    • Data Structure Builder
    • Connector Builders
      • REST Connector Builder
      • SOAP Connector Builder
      • DB (Database) Connector Builder
      • EVM Blockchain Connectors Builder
      • S3 Connector Builder
    • Asset Builders
      • Dictionary Builder
      • Decision Table Builder
      • Scorecard Builder
      • PMML Asset Builder
    • Global Variables Panel
    • Expression Editor
    • Project Deployment and Execution
    • Cron Expression Generator
    • Test Helper
    • Debugging the Project
    • DB transactions
    • Team Collaboration Tools
      • Version Control
      • Conflict Resolver
    • Selectors
    • Error Panel
    • Local History
  • Home and Administration Guide
    • Organization and Subscription
    • Organization Team
    • Repositories and Projects
    • Environments
    • Deployments
    • Database Provisioning
    • Roles and Permissions
  • Logs and Troubleshooting
    • Request/Call Logs
    • Deployment Logs
    • Application Logs
  • Appendix: Example Project
    • Risk Mitigation Solution
Powered by GitBook
On this page
  • How to Create the DB Connector
  • How to Set up a DB Connector
  • How to Create Connector Queries
  • Query Settings
  • How to Test the DB Connector Query
  • Additional Operations With the DB Connector
  • Additional operations with Queries
  • How to Override Database Connection Settings During Deployment
  1. Project Development Environment (IDE)
  2. Connector Builders

DB (Database) Connector Builder

PreviousSOAP Connector BuilderNextEVM Blockchain Connectors Builder

Last updated 6 months ago

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.

How to Create the 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.

The DB Connector has now 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.

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 Set Up a Database 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, 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

How to Set Up the Database User Id

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 Set Up the Database 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.

Query Settings

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.

How to Test the DB Connector Query

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:

  1. Boolean — value “true” or “false”

  2. String — text, for example, “Peter Peterson”

  3. Integer — number, for example, “22”

  4. Float — floating-point or decimal number, for example, “0.1234”

  5. 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:

  1. Edit or simply click on the name of the connector — allows you to open the DB Connector.

  2. Copy — allows you to copy the connector to transfer it later..

  3. 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).

  4. Cut — allows you to cut connectors for transfer later.

  5. Paste — allows you to paste the connector. This is not available unless you have previously used the Copy or Cut options.

  6. Rename — allows you to rename the connector.

  7. 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:

  1. 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).

  2. Rename — allows you to rename the query.

  3. Delete — allows you to delete the query.

How to Override Database Connection Settings During Deployment

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:

project.connector.{name}.connection.{property} = {value}

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.

Clicking on the field displays the list of up to five global variables previously added through the s component. Select the name of the required variable (if any) presented in the list.

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.

Global Variable