JDBC

A component to work with object-relational database management systems. It works with the `MySQL`, `PostgreSQL`, `Oracle` and `MSSQL` DBs.

---

Related pages:

JDBC Technical Notes

Table of Contents

• General information
  • Technical Notes
  • Environment variables
• Credentials
  • Connection URI
• Triggers
  • Select trigger
  • Get Rows Polling trigger
  • SELECT trigger (deprecated)
• Actions
  • Execute custom query
  • Select Query
  • Lookup Row By Primary Key
  • Insert
  • Delete Row By Primary Key
  • Execute stored procedure
  • Upsert Row By Primary Key)
  • Select (deprecated)
• Known Limitations

General information

A component to work with object-relational database management systems. It works with the MySQL, PostgreSQL, Oracle and MSSQL DBs.

Technical Notes

The technical notes page gives some technical details about JDBC component like changelog and completeness matrix.

Environment variables

For integration-testing is needed to specify following environment variables:

Connection to DB:

• CONN_USER_name - User login.
• CONN_PASSWORD_name - User password.
• CONN_DBNAME_name - DataBase name.
• CONN_HOST_name - DataBase host.
• CONN_PORT_name - DataBase port

Where name can have these values: MySQL, PostgreSQL, Oracle or MSSQL. For example: CONN_USER_MySQL.

Credentials

You need to use following properties to configure credentials:

DB Engine	Port	Limitation
MySQL	3306	compatible with MySQL Server 5.5, 5.6, 5.7 and 8.0
PostgreSQL	5432	compatible with PostgreSQL 8.2 and higher
Oracle	1521	compatible with Oracle Database 8.1.7 - 12.1.0.2
MSSQL	1433	compatible with Microsoft SQL Server 2008 R2 and higher

Connection URI

Provide hostname of the server, e.g. acme.com.

• Database Name - Provide name of database at the instance that you want to interact with.

• User - Provide a username that has permissions to interact with the Database.

• Password - Provide a password of the user that has permissions to interact with the Database.

• Configuration properties - Optional field. Provide a configuration properties for connections to the Database, e.g. useUnicode=true&serverTimezone=UTC

Limitation: Configuration properties value may not be checked during Credentials Verification, so in case of using this field make sure that it contains correct input.

Triggers

JDBC component includes the following triggers:

Select trigger

You are able to provide SELECT query with last execution timestamp as WHERE clause criteria.

Before executing the the statement %%EIO_LAST_POLL%% will be replaced with ISO Date of the last execution or max value of the last pooled datetime, for example 2018-08-01T00:00:00.000. During the first execution, date will be equal to “start” of Unix Time - 1970-01-01 00:00:00.000. Precision of the polling clause can be till milliseconds. The format of Start Polling From (optional) field should be like yyyy-mm-dd hh:mi:ss[.sss], where

• yyyy - year
• mm - month
• dd - day
• hh - hour
• mi - minute
• ss - second
• sss - millisecond (optional)

Get Rows Polling trigger

This trigger can polling data from provided table. As WHERE clause you can use column, which has datatype like DATE or TIMESTAMP.

Before executing the the statement %%EIO_LAST_POLL%% will be replaced with ISO Date of the last execution or max value of the last pooled datetime, for example 2018-08-01T00:00:00.000. During the first execution, date will be equal to “start” of Unix Time - 1970-01-01 00:00:00.000. Precision of the polling clause can be till milliseconds. The format of Start Polling From (optional) field should be like yyyy-mm-dd hh:mi:ss[.sss], where:

• yyyy - year
• mm - month
• dd - day
• hh - hour
• mi - minute
• ss - second
• sss - millisecond (optional)

Please note that Component Snapshot will not be overwritten in Real-Time flows due to platform behaviour, so we strongly recommend to use Get Rows Polling trigger in Keen Flows only*

Input fields description

• Tables List

Dropdown list with available table names, required field

• Timestamp (or similar) Column

Dropdown list with available Column names, that have a type like java.sql.Date or java.sql.Timestamp, required field

• Start Polling From (optional)

Optional field, indicates the beginning time to start polling from (defaults to the current time).

SELECT trigger (Deprecated)

This action exists in JDBC component only for backward compatibility. New Select trigger is recommended to use.

Actions

JDBC component includes the following actions:

Select Query

The action will execute an SQL query that can return multiple results, it has limitations on the query and suited only for SELECT type of queries. In SQL query you can use clause variables with specific data types. Internally we use prepared statements, so all incoming data is validated against SQL injection, however we had to build a connection from JavaScript types to the SQL data types therefore when doing a prepared statements, you would need to add :type to each prepared statement variable.

Please Note: prepared statement variables name could contain: any characters between a-z or A-Z, a digit and a character _ ([a-zA-Z0-9_]).

For example if you have a following SQL statement:

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

you should add :type to each @parameter so your SQL query will looks like this:

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

Following types are supported:

• string
• number
• bigint
• boolean
• float
• date

Dropdown Emit Behaviour contains following possible options:

• Fetch all - a single message with an array 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 the error will be thrown. A boolean input “Allow Zero Results” (defaults to false) appears at input metadata. If false - error will be thrown, else - the empty object will be emitted.

Execute query

Action to execute custom SQL query from provided request string.

Note: SQL request will be executed according to chosen database JDBC specification.

Execution result returns as array of objects. If request contains multiple sql statements - them will execute inside one transaction. If one of statements fails, transaction will be rollbacked.

Input fields description

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

Query Samples:

Select:

SELECT name, size FROM stars

Update:

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

Posgresql batch multiple statements request:

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

Lookup Row By Primary Key

The action will execute select query from a Table dropdown field, as criteria can be used only PRIMARY KEY. The action returns only one result (a primary key is unique). Checkbox Don't throw Error on an Empty Result allows to emit an empty response, otherwise you will get an error on empty response.

Input fields description

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

Insert

The action will execute INSERT command into the table from Table dropdown list the values specified in the body.

List of Expected Config fields

• Enable Rebound if Yes in case of deadlocks rebound message using Sailor rebound mechanism, number of rebound can be specified via environment variable: ELASTICIO_REBOUND_LIMIT recommended value 3.

Input fields description

Action contains only one configuration field Table - dropdown list with available table names.

Expected input metadata

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

Expected output metadata

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

{
  "result": true
}

Delete Row By Primary Key

The action will execute delete query from a Table dropdown field, as criteria can be used only PRIMARY KEY. The action returns count of affected rows. Checkbox Don't throw Error on an Empty Result allows to emit an empty response, otherwise you will get an error on empty response.

Enable Rebound if Yes in case of deadlocks rebound message using Sailor rebound mechanism, number of rebound can be specified via environment variable: ELASTICIO_REBOUND_LIMIT recommended value 3.

Input fields description

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

Execute stored procedure

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

Input fields description

• DB Schema - a schema that contains a procedure to call. Must be selected from the dropdown list before Stored procedure name.
• Stored procedure - a name of a procedure to call, can be selected from the dropdown 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 support 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 processing MSSql @RETURN_VALUE.

• For MySQL component same to DATABASE is same to SCHEMA by it’s definition, so DB Schema dropdown is empty for MySQL.

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

Usage case example

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 next metadata:

Upsert Row By Primary Key

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

Select table from Table dropdown list,

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

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

Input fields description

• Enable Rebound if Yes in case of deadlocks rebound message using Sailor rebound mechanism, number of rebound can be specified via environment variable: ELASTICIO_REBOUND_LIMIT recommended value 3.

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

Create or update record (deprecated)

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

Select (deprecated)

This action exists in JDBC component only for backward compatibility. Please use NEW Select Query instead.

Known limitations

1. Only tables with one PRIMARY KEY is supported. You will see the message Table has not Primary Key. Should be one Primary Key , if the selected table doesn’t have a primary key. Also, you will see the message Composite Primary Key is not supported , if the selected table has composite primary key.

2. The current implementation of the action Upsert By Primary Key doesn’t mark non-nullable fields as required fields at a dynamic metadata. In case of updating such fields with an empty value you will get SQL Exception Cannot insert the value NULL into.... You should manually fill in all non-nullable fields with previous data, if you want to update part of columns in a row, even if data in that fields doesn’t change.

3. The current implementation of the action Execute stored procedure doesn’t support:
   • ResultSet MSSQL output.
   • Any array types parameters.
   • MySQL schemas dropdown list. (MySQL does not have schemas by definition).
4. Rebound mechanism only works for this SQL State:
   • MySQL: 40001, XA102
   • Oracle: 61000
   • MSSQL: 40001
   • PostgreSQL: 40P01
5. If your database server configured to custom timezone (differ from UTC) JDBC driver may convert time appropriate - for example if you want to use Insert action with MySQL which is configured to +2:00 time zone and provide 2022-01-01 15:00:00 as value to some datetime field in database it will be saved as 2022-01-01 17:00:00.