Help Center

Menu

JDBC actions

Select query

Select Query

The action will execute an SQL query that can return multiple results. It has limitations on the query and is suited only for SELECT-type queries. You can use clause variables with specific data types in an SQL query. Internally, we use prepared statements, so all incoming data is validated against SQL injection. A connection had to be established from JavaScript to the SQL data types. Consequently, when working with prepared statements, it is necessary to append :type to each variable in the prepared statement.

Prepared statement variables' names could contain any characters between a-z or A-Z, a digit, and an underscore ([a-zA-Z0-9_]).

For instance, consider the following SQL statement:

SELECT
FROM users
WHERE userid = @id AND language = @lang

In this case, add :type to each @parameter. Consequently, your SQL query should be modified as follows:

SELECT
FROM users
WHERE userid = @id:number AND language = @lang:string

The following types are supported:

  • String

  • Number

  • Bigint

  • Boolean

  • Float

  • Date

Select Query

The Emit Behavior drop-down contains the following possible options:

Fetch all

A single message with an array of results containing all the objects (rows) will be emitted.

Emit Individually

Multiple messages (one message per one row) will be emitted.

Expect Single

A single message with one result row will be emitted. If more than one row is returned, an error will be thrown. A boolean input Allow Zero Results (defaults to false) appears at input metadata. If false, an error will be thrown; else, the empty object will be emitted.

Select Query

Execute query action

Action to execute a custom SQL query from the provided request string.

SQL requests will be executed according to the chosen database JDBC specification.

The execution result returns as an array of objects. If the request contains multiple SQL statements, they will execute inside one transaction. If one of the statements fails, the transaction will be rolled back.

Execute custom query action

Input fields

As input metadata, you will get one field named query to provide the request string.

Query samples:

Select:

SELECT name, size FROM stars

Update:

[source.copy,INSERT INTO stars values (1,'Taurus', '2015-02-19 10:10:10.0', 123, 5, 'true', '2015-02-19')]

PostgreSQL batch multiple statements request: :

DELETE FROM stars WHERE id = 1;
UPDATE stars SET radius = 5 WHERE id = 2;

Lookup row by primary key action

Look up Row by Primary Key

This action will execute a select query from a Table drop-down field. The only usable criteria is PRIMARY KEY.

The Don’t Throw Error on an Empty Result checkbox allows emitting an empty response; otherwise, you will get an error on an empty response.

Input fields

look up row by primary key 2

Insert action

The action will execute an INSERT command into the table from the Table drop-down list with the values specified in the body.

Insert Action

Configuration fields

Enable Rebound

If Yes, in case of deadlocks, rebound a message using the Sailor rebound mechanism. The number of rebound can be specified via environment variable (only the product team can make the configuration update).

Input fields

This action contains only one configuration field, Table, which has a drop-down list with available table names.

insert action 2

Input metadata

As input metadata, you will get all fields of the selected table except for fields with the auto-increment or auto-calculated properties.

Output metadata

As output metadata, you will get the execution insert result like:

{
  "result": true
}

Delete row by primary key action

Delete Row By Primary Key

This action will execute a delete query from a Table drop-down field. The only usable criteria is PRIMARY KEY. The action returns the count of affected rows. The checkbox Don’t Throw Error on an Empty Result allows emitting an empty response; otherwise, you will get an error on an empty response.

Configuration fields

Enable Rebound

If Yes, in case of deadlocks, rebound a message using Sailor rebound mechanism. The number of rebound can be specified via environment variable (only the product team can make the configuration update).

Input fields

As an input metadata, you will get a Primary Key field to provide the data inside as a clause value.

Execute stored procedure action

This action calls a stored procedure from the selected DB Schema and Stored procedure name.

Input fields

DB Schema

A schema that contains a procedure to call. Must be selected from the drop-down list before the Stored procedure name.

Stored procedure

A name of a procedure to call, can be selected from the drop-down list.

Metadata generates automatically using IN & IN OUT procedure parameters for input and OUT & IN OUT procedure parameters for output.

As array fields, this action now supports ONLY:

  • CURSOR (as SQL type).

  • REF CURSOR (as ORACLE type) The result for this type of fields would be returned as an array of JSON objects.

This action does not process the MSSQL @RETURN_VALUE.

  • In the MySQL component, 'DATABASE' is equivalent to 'SCHEMA' by definition. Therefore, the DB SCHEMA drop-down is empty for MySQL.

  • MSSQL DB stored procedures have only IN and INOUT fields.

Example use case

For Oracle DB procedure:

create PROCEDURE "INSERT_EMPLOYEE"(
        i_emp_id IN EMPLOYEE.EMPID%TYPE,
        i_name IN EMPLOYEE.EMPNAME%TYPE,
        i_department IN EMPLOYEE.DEPARTMENT%TYPE)
IS
BEGIN
  INSERT INTO EMPLOYEE (EMPID, EMPNAME, DEPARTMENT)
  VALUES (i_emp_id, i_name, i_department);
END;

Component generates the next metadata:

execute store procedure input fields

Upsert row by primary key action

The action will execute a SELECT command from a Tables drop-down field, and as search criteria, only PRIMARY KEY can be used. It will execute an INSERT command by PRIMARY KEY with specified fields if the result does not found; else, the action will execute an UPDATE command by PRIMARY KEY with specified fields. The action returns only one result row (as a primary key is unique).

  1. Select a table from the Table drop-down list:

    upsert row by primary key 1

  2. Specify input (userid field is, in our case, a Primary key) data and click “Continue”. You can also enable the rebound mechanism if needed.

    upsert row by primary key 2

  3. Retrieve a sample result, click “Continue” and finish component configuration.

    upsert row by primary key 3

Input fields

Enable Rebound

If this is Yes, in case of deadlocks, rebound a message using the Sailor rebound mechanism. The number of rebound can be specified via environment variable (only the product team can make the configuration update).

As an input metadata, you will get all fields of the selected table. PRIMARY KEY is a required field, and other input fields are optional.

upsert row by primary key 4

Create or update record action (deprecated)

This action exists in the JDBC component only for backward compatibility. Please use Upsert row by primary key action instead.

Select action (deprecated)

This action exists in the JDBC component only for backward compatibility.