Building SQL Instructions

On this page, we provide an overview of SQL instructions used in configuring most of Sensedia Integrations' database connectors.

In this section, you will find:

Overview

An SQL instruction is a command or query that allows interaction with relational databases within an integration flow.

They are used to manipulate data, such as query, insert, update, or delete records. In this way, instructions are essential for the exchange and management of information between integrated systems.

To ensure these operations are executed correctly, each instruction must be written according to SQL’s native syntax.

You can use the Properties, on the left side of the screen, to select environment variables and payloads from previous steps that you want to include in the instruction.

Below, see the configuration form for the SQL Server 2022 database connector:

sql server form

Operations

SQL operations are instructions that perform actions on data within a database, with the most common ones being SELECT, INSERT, UPDATE, and DELETE.

  1. SELECT: queries data from one or more tables.

    SELECT first_name, last_name, email
FROM employees
WHERE department = 'Sales';

    The instruction selects the first_name, last_name and email columns from the employees table for all the employees in the Sales department.

  2. INSERT: adds new records to a table.

    INSERT INTO employees (first_name, last_name, department, hire_date)
VALUES ('John', 'Doe', 'Marketing', '2024-11-01');

    The instruction inserts a new record into the employees table, with the values provided for the first_name, last_name, department and hire_date columns.

  3. UPDATE: modifies existing data in a table.

    UPDATE employees
SET department = 'Marketing', hire_date = '2024-11-01'
WHERE employee_id = 123;

    The instruction updates the employees table for the employee with the employee_id = 123 data, changing their department and hire date.

  4. DELETE: removes records from a table.

    DELETE FROM employees
WHERE employee_id = 123;

    The instruction deletes the record of the employee with employee_id = 123 from the employees table.

  5. JOIN: combines records from two or more tables, based on a relationship condition between them. There are different types of JOINs, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

    SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.department_id;

    The instruction performs a join (using INNER JOIN) between the employees and departments tables. The join occurs when the value of the department_id column matches in both tables.

  6. CREATE and ALTER: define or modify the structure of the database (creating new objects or altering existing objects).

    • CREATE: creates a new table or another object.

      CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  department VARCHAR(50),
  hire_date DATE
);

      The instruction creates a table called employees, where:

      • employee_id will be the primary column and will contain only numbers (integer).

      • The columns first_name and last_name must contain a string with up to 100 characters.

      • The column department must contain a string with up to 50 characters.

      • The column hire_date must contain a date.

    • ALTER: modifies the structure of an existing table, such as adding or deleting columns.

      ALTER TABLE employees
ADD email VARCHAR(100);

      The instruction modifies the employees table by adding the email column, which must contain a string with up to 100 characters.

  7. GROUP BY and HAVING: GROUP BY groups records that have identical values in specific columns, allowing aggregate functions (such as COUNT, SUM, AVG) to be used to calculate results for each group. HAVING is used to filter the groups after applying GROUP BY.

    SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

    In the employees table, the instruction selects the departments and counts the number of employees in each. Then, it groups the data by department and displays only the departments that have more than 5 employees.

Syntax elements

Below are some basic syntax rules for constructing an instruction:

  1. Single quotes (') for strings:

    Strings are delimited by single quotes.

    • Example: SELECT * FROM users WHERE name = 'João';

      The instruction returns all columns of records where the value of the name column is João.

  2. Double quotes (") for identifiers:

    In some cases, double quotes can be used for column or table identifiers (such as "id", "users"), but this is not mandatory in all databases and may vary.

    • Example: SELECT "id" FROM "users";

      The instruction selects only the id column from the users table.

  3. Comma (,):

    The comma is used to separate arguments or elements within a function, parameter list, or string concatenation.

    • Example: SELECT id, name FROM users WHERE age > 18;

      The instruction filters the users from the users table who are over 18 years old and displays only their id and name.

  4. Semicolon (;):

    The semicolon is used to indicate the end of a command, allowing multiple instructions to be executed within the same block.

    • Example: SELECT * FROM users; UPDATE users SET age = 30 WHERE id = 1;

      The instruction returns all columns from the users table and updates the age column to 30 for the record where id is 1.

  5. Escaping internal quotes:

    If you need to use single quotes inside a string that is also delimited by single quotes, you must escape the internal quotes using additional single quotes.

    • Example: SELECT * FROM users WHERE name = 'O''Malley';

      The instruction returns all columns of records where the value of the name column is 'O’Malley'`.

  6. Asterisk to select all columns from a record:

    • Example: SELECT * FROM orders WHERE total_amount < 100;

      The instruction returns all columns from the orders table for records where the total amount is less than 100.

  7. WHERE for filtering:

    The WHERE clause is always placed after the FROM statement to define filtering conditions.

    • Example: SELECT * FROM users WHERE id = 1;

      The instruction returns all columns from the users table where the id is equal to 1.

  8. Logical operators:

    The logical operators AND, OR, and NOT are used to combine conditions within the WHERE clause.

    • Example: SELECT * FROM users WHERE age > 18 AND status = 'active';

      The instruction returns all columns from the users table for records where age is greater than 18 and status is active.

  9. Relational operators:

    The relational operators: =, !=, <, >, ⇐, >=, BETWEEN, LIKE, IN are used to compare values in conditions.

    • Example: SELECT * FROM products WHERE price BETWEEN 10 AND 100;

      The instruction returns all columns from the products table, but only for records where the value of the price column is within the range of 10 to 100.

Examples

Now, let’s explore some examples of how to apply instructions in different contexts within Sensedia Integrations.

  • Example 1

    concat("SELECT * FROM users WHERE id = ", $.For_Each.Input.Payload.id)

    • The instruction retrieves all data for a specific user from a table, based on an id value:

      • concat: The Concat function is used to join (or concatenate) two or more strings into a single string.

      • "SELECT * FROM users WHERE id = ": instructs the database to:

        • Select (SELECT)

        • all columns (*)

        • from (FROM)

        • the users table

        • and filter the search (WHERE)

        • to return only the rows where the id column matches a specific value, indicated after the equals sign (id =).

        • $.For_Each.Input.Payload.id: value to be filtered by the instruction, extracted from the id field in the input payload of the For Each step.

  • Example 2

    concat(
    "INSERT INTO clients (id, name, last_name)
    VALUES (",
    $.For_Each.Input.Payload.id, ", '",
    $.For_Each.Input.Payload.first_name, "', '",
    $.For_Each.Input.Payload.last_name, "') ",
    "ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    last_name = VALUES(last_name);"
)

    • The instruction inserts a new record into the clients table with the values id, name and last_name.

    • The specific values are taken from the For Each step’s input payload.

    • If the id (or another primary key) already exists in the table, the instruction does not insert a new record, but updates the fields:

      • name

      • last_name

  • Example 3

    CREATE TABLE IF NOT EXISTS clients (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  last_name VARCHAR(255)
);

    • The instruction is a CREATE TABLE command, which creates a table in the database.

    • IF NOT EXISTS: this clause ensures that the table is created only if it does not already exist. The table will be named clients and will be used to store customer information.

    • The following are the definitions for the three columns:

      • id INT PRIMARY KEY: the column name will be id, and the data type to be inserted will be an integer. This will be the primary column, meaning it must contain unique and non-null values.

      • name VARCHAR(255): the column name will be name, and the data type to be inserted will be a string with up to 255 characters. The VARCHAR type is used to store short texts.

      • last_name VARCHAR(255): the column name will be last_name, and it has the same characteristics as the second column.

  • Example 4

    concat(
  "DECLARE ",
  "v_json CLOB := '", $.CombinarDados.Response.Payload, "';",
  "BEGIN ",
  "INSERT_PROCEDURE_DATA(v_json);",
  "END;"
)

    • The instruction starts with the concat function, which is used to dynamically build a PL/SQL command (Oracle’s programming language) that executes the following steps:

      • Declares (declare) a variable v_json of type CLOB (Character Large Object). The CLOB type is used to store large volumes of text, such as documents or large payloads.

      • Assigns the value extracted from a payload (CombinarDados.Response.Payload). The payload is expected to be a large block of text or data, which is why the value is inserted between single quotes.

      • After the variable declaration, the BEGIN command starts the execution block of the PL/SQL code, where the actual logic of execution will occur. Inside this block, the procedure INSERT_PROCEDURE_DATA is called, which takes the variable v_json as an argument to process or insert the data into the database. The block is finalized with the END command, concluding the execution of the instruction sequence.

        A procedure is a set of SQL instructions that are stored in the database and can be executed repeatedly.
Thanks for your feedback!
EDIT

Share your suggestions with us!
Click here and then [+ Submit idea]