Help Center

Menu

JDBC

This is a component for working with object-relational database management systems. It works with MySQL, PostgreSQL, Oracle, and MSSQL DBs.

Credentials

You need to use the 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 later.

Oracle

1521

Compatible with Oracle Database 8.1.7 - 12.1.0.2.

MSSQL

1433

Compatible with Microsoft SQL Server 2008 R2 and later.

Connection URI

Provide the hostname of the server, for example, acme.com.

Database Name

Provide the name of the 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 for the user that has permissions to interact with the database.

Configuration properties

Optional field. Provide configuration properties for connections to the database. For example, useUnicode=true&serverTimezone=UTC.

The configuration properties' value may not be checked during Credentials Verification. Ensure it contains the correct input when using this field.

Triggers

The JDBC component uses the following triggers:

Trigger Description

Select trigger

This trigger will execute an SQL query that returns multiple results. It has limitations on the query and is suited only for SELECT type of queries. The trigger will remember the last execution timestamp and let you build queries on it.

Get rows polling trigger

This trigger will execute a select query from a specified table with simple criteria of selected datetime or timestamp table. The trigger will remember the last execution timestamp and let you build queries on it.

Select trigger (deprecated)

This action exists in JDBC component only for backward compatibility. Use the Select trigger instead.

Actions

The JDBC component includes the following actions:

Action

Description

Execute query action

Action to execute custom SQL query from provided request string.

Select query

This action will execute an SQL query that returns multiple results. It has limitations on the query and is suited only for SELECT queries.

Lookup row by primary key action

This action will execute a select query from a specified table. The only applicable criterion is the PRIMARY KEY. The action returns only one result (as a primary key is unique).

Insert action

This action will execute an insert query into the specified table. The action returns a boolean value indicating whether the insert was successful.

Delete row by primary key action

This action will execute a select query from a specified table. The only applicable criterion is the PRIMARY KEY. The action returns an integer value that indicates the number of rows affected; the returned value can be 0 or 1 (as a primary key is unique).

Execute stored procedure action

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

Upsert row by primary key action

This action performs a select query on a specified table. The only applicable criterion is the PRIMARY KEY. If the query result is not found, it will then execute an insert command using the specified PRIMARY KEY field. If the result is found, the action will execute an update command using the PRIMARY KEY with the specified field. The action returns only one result row, as a primary key is unique.

Limitations

Only tables with one primary key are supported.

You will see the message Table has not Primary Key. Should be one Primary Key if the selected table does not have a primary key. Also, you will see the message Composite Primary Key is not supported if the selected table has a composite primary key.

The current action Upsert By Primary Key implementation does not mark non-nullable fields as required fields at dynamic metadata

When updating such fields with an empty value, you will get an 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 the data in those fields does not change.

The current implementation of the action Execute stored procedure does not support

  • ResultSet MSSQL output.

  • Any array types parameters.

  • MySQL schemas dropdown list. (MySQL does not have schemas by definition)

Rebound mechanism only works for this SQL State

  • MySQL: 40001, XA102

  • Oracle: 61000

  • MSSQL: 40001

  • PostgreSQL: 40P01

JDBC driver may convert the time appropriately if your database server is configured to a timezone other than UTC

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 the database, it will be saved as 2022-01-01 17:00:00.