DB Call logic
  • 01 Dec 2020
  • 3 Minutes To Read
  • Contributors
  • Print
  • Share
  • Dark
    Light

DB Call logic

  • Print
  • Share
  • Dark
    Light

DB Call is a new type of Corezoid node that performs database queries and returns the result to the task. Requires a database connection pre-configuration. At present, MySQL, Microsoft SQL Server and PostgreSQL instances are supported.

Creating the database connection

To create new database connection:

  1. Click Create button in Workspace section;
  2. Select Database;
  3. Enter:
    • database instance type
    • database instance host
    • database instance port
    • database user login
    • database user password
    • database name
    • default connection timeout
    • use SSL
  4. Check connection setting by clicking Test Connection button;
  5. Click Ok button.

connecting db

Requirements:

  1. All fields are required to create a connection to the database;
  2. Maximum length of the name is 255 characters;
  3. Maximum Timeout value - 30000.

Editing the database connection

You can edit the created database connection - change its name and connection settings.

To edit the connection to the database::

  1. Select connection from the list;
  2. Click on it twice;
  3. Change the fields you need;
  4. Check the correctness of the connection settings by clicking the Test Connection button;
  5. Click the OK button.

editing connect to db

Infopanel

Info tab

The Info tab displays basic information about the database connection - its identifier, name, name and login of the owner, date of creation and changes. The tab is available to those users, whose got access rights to View and/or Modify database connection.

info panel

History tab

The History tab displays the history of the database connection changes - who, when and what changed. The tab is available to those users, whose got access rights to View and/or Modify database connection.

history

Access Management

Access management for database connections is similar to other Corezoid objects:

  • the View right allows viewing connection settings (and their history) without editing them;
  • the Modify right allows viewing and editing connection settings, as well as viewing the history of settings changes and providing access to the connection to other users;
  • the Task Management right does not give options to view or edit the connection, we do not recommend using it the owner of the connection can transfer ownership to another user.

Export/Import of database connections

You can export and import the database connection with all its settings similar to other Corezoid objects. Read more in this article.

Working with DB Call node in process editor

bd settings

We've added a new node - DB Call. By adding it to the process editor canvas and connecting it with other logic you can perform queries to the database and work with the resulting data in other process logic.

DB Call node settings:

  1. Basic settings - choose a connection to the database (we recommend to use different connections to regulate access to databases and tables);
  2. SQL Query - area of the SQL query editor. For ease of work, the SQL query editor can be expanded;
  3. Alert when there is tasks queue - configure logic behavior when a critical number of tasks is reached in a node. More in article;
  4. Limit the time of the task in the node - setting the time interval at which the task will go further in the process logic if the DB Call node does not respond.

SQL examples

Create table

CREATE TABLE alc (
    user_id integer,
    conveyor_id integer,
    name text,
    description text
);

Insert new row into table (you can use task parameters in VALUES)

INSERT INTO alc (user_id, conveyor_id, name, description)
VALUES (13, 777000, 'Send Message', 'Use to send text messages to phone number')

Call stored function with one parameter (PostgreSQL)

SELECT function_name(arg)

Select data from two tables using JOIN

SELECT alb.conveyor_id, CONCAT('https:://api.corezoid.com/api/2/json/', alb.hash) as URL, alc.* 
FROM "public"."alb" alb
LEFT JOIN "public"."alc" alc ON alc.conveyor_id = alb.conveyor_id
WHERE alc.conveyor_id is not null

Update table data based on task parameters (change value of hash field with value of{{URL}} parameter for row with conveyor_id equals to {{conv_id}})

UPDATE alb
SET hash = {{URL}}
WHERE conveyor_id = {{conv_id}}

Working with database connections via Corezoid API

You can manage database connections using Corezoid API methods. A full description of the protocol and examples of requests in the article.

Was This Article Helpful?