Database

The Database connector helps you to exchange data with relational databases by using a JDBC driver.

Limitations

General limitations
In stored procedure notifications, parameter types IN, INOUT, and ORACLE CURSOR (INOUT) are not supported.
Amazon Redshift
  • Actions not supported
    • Stored Procedure
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
MariaDB
  • Notifications
    • Trigger conditions are not supported in update and delete notifications.
  • If you configure an operation for MariaDB by using the driver versions greater than or equal to 2.4.0, select the table under <current catalog>.
Microsoft SQL
  • Notifications
    • Trigger conditions that are not supported in update and delete notifications.
    • Microsoft SQL 2000 and 2005 does not allow the retrieval of both the old and the new values in a single Update notification. The notification can retrieve either the old or the new value.
    • The WHERE tab is not supported in the insert and updates notification. An exception is generated when you try to use this tab.
Driver Database Limitations
Microsoft JDBC Driver Version 2.2.0019 Microsoft SQL Server 2000
  • When running the SelectSQL adapter service using the not null real type, the following error appears - Value cannot be converted to requested type. It is a driver issue for both the DataDirect Connect for JDBC and the Microsoft SQL Server 2000 Driver for JDBC.
  • This driver does not support retrieving table names from a database when the database’s name contains special characters.
Microsoft JDBC Driver Version 1.0.809.102 Microsoft SQL Server 2005
  • This driver returns incorrect data type TEXT, IMAGE, and NTEXT for MS SQL data types VARCHAR(max), VARBINARY(max), and NVARCHAR(max).
  • This driver returns invalid JDBC data type for MS SQL UNIQUEIDENTIFIER data type.
DataDirect Connect Microsoft SQL Server 2000
  • When running the SelectSQL action that uses the not null real type, the following error appears - Value cannot be converted to requested type.
Microsoft SQL Server JDBC Driver 3.0 and 4.0 Microsoft SQL Azure
  • Microsoft Azure SQL Database does not support distributed transactions.
MySQL
  • Notifications
    • Trigger conditions are not supported in update and delete notifications.
    • MySQL version 5.0.x does not support multiple triggers with the same event (insert, delete, or update) for one table. In MySQL, when monitoring a table by using Insert, Update, or Delete notifications, you must first disable the existing notification before you enable a new one for the same operation type (Insert, Update, or Delete) on that table.
    • In Update notification with a MySQL database, updating any database field with the same value does not start a trigger.
Oracle
  • Notifications
    • Trigger conditions that are not supported in Update and Delete notifications.
  • In the WHERE tab, if you use a CHAR(n) data type and enter a value in the Input Field, then the JDBC data type is automatically set to ORACLEFIXED_CHAR.
Driver Database Limitations
  • Oracle JDBC Thin Driver
 All supported Oracle databases
  • NUMBER and NUMBER(n,m) Oracle data types map to java.math.BigDecimal in all the adapter services by default.
  • BLOB and CLOB data types cannot be used in a table definition when configuring the adapter notifications.
  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
 All supported Oracle databases
  • StoredProcedure actions and notifications are not compatible with stored procedures that include a parameter of type Oracle Cursor, when the ref cursor is declared as a cursor type that is defined as a strong type with the %ROWTYPE attribute. The workaround is to define the ref cursor as a weak type.
  • Oracle JDBC Thin Driver 9.0.1 (Mac OS)
  • Oracle JDBC Thin Driver (all other OSs)
  • DataDirect driver
  • Oracle 10g
  • Oracle 11g
  • Oracle 11g R2
  • Oracle 12c
  • Oracle 18c
  • Oracle 19c
  • With Oracle Database 10 g, you cannot configure adapter services or notifications with BINARY_DOUBLE or BINARY_FLOAT databases that use the Adapter Service Editor. In these cases, if you try to insert a row, the corresponding JDBC data type does not appear in the Adapter Service Editor. As an alternative, use the CustomSQL adapter service when configuring services that involves these data types.
PostgreSQL

PostgreSQL 11.2 and PostgreSQL 11.5

  • Actions not supported
    • Stored Procedure
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
  • If you configure an operation to select a table, you must always select the table under the current catalog.
SAP HANA
  • Notifications
    • Trigger conditions are not supported in Update and Delete notifications.
  • SAP HANA requires tunnelling in SAP HANA Cloud Platform SDK Version 1.82.21.2.
  • Metadata lookup for ARRAY data type is not supported. Only CustomSQL and DynamicSQL actions can be used to run operations by using these data types.
  • SAP HANA trigger definition does not support the WHERE clause thereby rendering the WHERE tab in the Insert, Update, Delete notification useless.
  • StoredProcedure Action and StoredProcedure notification do not support ResultSet.

JDBC driver for SAP HANA2

  • SSL connection is supported by using SAP HANA file-based truststores (PSEs).
Snowflake
  • Actions not supported
    • Stored Procedure
    • StoredProcedureWithSignature
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
    • StoredProcedureNotificationsWithSignature,
    • OrderedNotifications
  • Batch services
Note:
Run the following query on the Snowflake backend to change the format if the SELECT operation fails. 

ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT=JSONALTER USER 
 SET JDBC_QUERY_RESULT_FORMAT=JSON
Snowflake
  • Actions not supported
    • Stored Procedure
    • StoredProcedureWithSignature
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
    • StoredProcedureNotificationsWithSignature,
    • OrderedNotifications
  • Batch services
Note:
Run the following query on the Snowflake backend to change the format if the SELECT operation fails. 

ALTER SESSION SET JDBC_QUERY_RESULT_FORMAT=JSONALTER USER 
 SET JDBC_QUERY_RESULT_FORMAT=JSON
Teradata Vantage
  • Actions not supported
    • Stored Procedure
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
Tibero
  • Actions not supported
    • Stored Procedure
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
Vertica
  • Actions not supported
    • Stored Procedure
  • Notifications not supported
    • Insert notifications
    • Update notifications
    • Delete notifications
    • StoredProcedure notifications
Deploy Anywhere Flow Services
  • Only JDBC jars that are certified by IBM® for the database driver are authorized for uploading with deploy anywhere flow services.

  • The following databases are supported.

    • Amazon Redshift
    • MariaDB
    • Microsoft SQL Server
    • MySQL
    • Oracle
    • PostgreSQL
    • SAP HANA
    • Snowflake
    • Teradata Vantage
    • Tibero
    • Vertica

  • The following features are not supported.

    • Connection pool configuration
    • One-way SSL due to lack of SSL keystore deployment
    • Polling notifications
    • Kerberos-based authentication
    • Templates
      • Run service
      • Stored procedure without signature