Database Call

Overview

The Database Call node lets you write and execute SQL queries directly within Corezoid, removing the need for writing custom APIs to access databases. With the Database Call node, managing and accessing data in your databases becomes straightforward and streamlined.
Supported databases:

• MySQL
• Microsoft SQL Server
• PostgreSQL
• Oracle
• MongoDB

Preparations for Working with the Database Call

1. Create a database: create a database by clicking Create-Database.

2. Fill in all required fields: specify all the required parameters, such as database name, users, passwords, etc. and select the database type in the drop-down list.

3. Create a process: create a new process in your system and add the DB Call node to it.

4. Connect the node to the database: link the DB Call node to your database by specifying the connection parameters in the Connection field.

5. Execute a query: configure a query in the node. For example, select the SQL in the Language field and enter a query to retrieve information from your database.

6. Save changes and switch to the View mode: Click Deploy to apply all changes and switch to the View mode.

7. Send an empty task to your process to check its operation: In the View mode, click +New task, select Create, and click Add task to send a new empty task to your process.

Check whether your task in the final node contains information retrieved from the database.

After checking your process operation, you can configure and use your Database Call node.

Settings

The Database Call node has the following parameters:

1. (Optional) Title and Description: Name and details of the node.

2. Basic settings:
   Connection: Shows the database connection
   Note: For more information about how to connect your database to Corezoid, go to Examples.

3. SQL Query: Indicates your SQL query.
   Note: To access task parameters, use curly braces: {{some_parameter}}. Ensure that the query result does not surpass the maximum task size limit.

4. Code editor: Shows the JSON format of the entered key and value. You can write and include additional code, which will be executed when a task enters the node.

5. Other:

   • Alert if the number of tasks in the node queue reaches the following number: Helps monitor whether the number of tasks in the node exceeds the specified threshold. When selecting the checkbox, you have to enter the needed number of tasks in the field that appears below.
   • Maximum interval, for which the task stays in the node before being forwarded: The amount of time a task is allowed to be in the node; can be set in seconds, minutes, hours, and days.
     Note: The checkbox has a minimum value of 30 seconds. You can set a shorter interval by using the Unixtime function.

   

Examples

• Create a table

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

• Insert a new row into a table

  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 data based on task parameters
  Change the value of the hash field with the value of {{URL}} parameter for a row with conveyor_id equals to {{conv_id}}.

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

Error handling & troubleshooting

When an error occurs during the task processing, you may see the following error parameter names in the task.