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

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.