JDBC
Related pages:
JDBC Technical Notes
JDBC actions
JDBC triggers
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
orMSSQL
. 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
This trigger will execute an SQL query that returns multiple results, it has limitations on the query and suited only for SELECT type of queries. The trigger will remember last execution timestamp and let you build queries on it. -
Get Rows Polling trigger
This trigger will execute select query from specified table with simple criteria of selected datetime or timestamp table. The trigger will remember last execution timestamp and let you build queries on it.s -
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:
-
Execute query action
Action to execute custom SQL query from provided request string. -
Select action
This action will execute an SQL query that returns multiple results, it has limitations on the query and suited only for SELECT type of queries. -
Lookup Row By Primary Key action
This action will execute select query from specified table, as criteria can be used only PRIMARY KEY. The action returns only one result (a primary key is unique). -
Insert action
This action will execute insert query into the specified table. The action returns boolean value is execution insert successful or not. -
Delete Row By Primary Key action
This action will execute delete query from specified table, as criteria can be used only PRIMARY KEY. The action returns an integer value that indicates the number of rows affected, the returned value can be 0 or 1 (a primary key is unique). -
Execute stored procedure action
This action calls stored procedure from selected DB Schema and Stored procedure name. -
Upsert Row By Primary Key action
This action will execute select command from specified table, 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). -
Create or update record action (Deprecated)
This action exists in JDBC component only for backward compatibility. Please use Upsert row by primary key instead.
Current limitations
-
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 messageComposite Primary Key is not supported
, if the selected table has composite primary key. -
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 ExceptionCannot 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. - 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).
- Rebound mechanism only works for this SQL State:
MySQL
: 40001, XA102Oracle
: 61000MSSQL
: 40001PostgreSQL
: 40P01
- 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
withMySQL
which is configured to+2:00
time zone and provide2022-01-01 15:00:00
as value to some datetime field in database it will be saved as2022-01-01 17:00:00
.