Mysql
Postgresql
Redshift
Oracle
Snowflake

SQL Sever
Maria DB
DB2
Informix
Cassandra

Teradata
Sybase ASE
Denodo
Vertica
SAPHANA

Database

Latest certified and recommended drivers

Dependencies

SQL Server

mssql-jdbc-11.2.0.jre11.jar

For this driver version, you must set the URL property in the Generic Database Account as trustServerCertificate=true

MySQL

mysql-connector-java-5.1.47.jar (MySQL 5.x.x server version)
mysql-connector-j-8.0.17.jar (MySQL 8.x.x server version)

Oracle

ojdbc10.jar

Snowflake

snowflake-jdbc-3.13.25.jar

Redshift

RedshiftJDBC42-1.1.17.1017.jar

Postgre SQL

postgresql-42.7.2.jar

Vertica

vertica-jdbc-11.0.0-0.jar

Teradata

17.20.12

Cassandra

v4.11.1

Maria DB

mariadb-java-client-2.6.0.jar

Informix

ifxjdbc.jar

Db2

db2jcc4.jar

SAP HANA

ngdbc-2.8.14.jar

IBM Db2 i

jt400-20.0.7.jar

Sybase

jconn4.jar

Athena AWS

AthenaJDBC42_2.0.24.1000.jar

JDBC Snaps

Endpoints

Scenario

Error/Output from the Old (v3.9.1) JOOQ Library

Error/Output from the New (v3.17.x) JOOQ Library

Generic JDBC - Execute

All databases - Execute

When calling stored procedures

Output message:

Message: Success

Output message:

Message: Success

$UPDATE_COUNT=-1, 0, or 1 (based on the Snap Pack behavior) in the output

Generic JDBC - Select and Generic JDBC - Execute

IBM DB2

When the Snaps integrate with the DB2 for the XML data type.

Output message:

{
"DATA":
{
"AS400JDBCSQLXMLLocator":
{
"binaryStream":
{
"AS400JDBCInputStream":
{
}
"string": "<root><element>Value</element</root>"
"characterStream":
{
"ConvTableReader":
{
"ccsid": 1208
"byteCacheSize": 2562
"cacheSize": 1024
"encoding": "UTF-8"
}}}}}}

Output message:

{ "DATA": "<root><element>Value</element></root>" }

Generic JDBC - Select and Generic JDBC - Execute

PostgreSQL

When the Snaps integrate with the PostgreSQL for the XML data type.

Output message:

"data":
{
"PgSQLXML":
{
"binaryStream":
{
"ByteArrayInputStream":
{
}
"characterStream":
{
"StringReader":
{
}
"string": "<book><title>PostgreSQL Essentials</title><author>John Doe</author></book>"}}}}}

Output message:

"data": "<book><title>PostgreSQL Essentials</title><author>John Doe</author></book>"

JDBC Snaps

Endpoints

Scenario

Error/Output from the Old (v3.9.1) JOOQ Library

Error/Output from the New (v3.17.x) JOOQ Library

Generic JDBC - Execute

Sybase - Execute

When Generic JDBC - Execute Snap inserts out-of-range values for int types like BigInt, SmallInt, and TinyInt

error=Batch operation failed, reason=Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478' to an int field. , error code: 0, SQL state: 22001, next exception: Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478.5807' to a MONEY field. , resolution=Please check for valid Snap properties and input data.

error=Batch operation failed, reason=Data truncation, error code: 0, SQL state: 22001, next exception: Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478' to an int field. , resolution=Please check for valid Snap properties and input data. Reason: Expected and input data are not equal, Resolution: Please check expected and input data.

Oracle - Execute

When you pass a special character (') within the single quotes ('').

"reason": "Invalid query: INSERT INTO \"enron\".\"EMP7400\"(NAME) VALUES ('windyi'eee')

reason=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"enron"."EMP7400"(NAME) VALUES ('windyi'eee')' at line 1, error code: 1064, SQL state: 42000

MySQL - Execute

When you pass a special character (') within the single quotes ('').

"reason": "Invalid query: INSERT INTO \"enron\".\"EMP7400\"(NAME) VALUES ('windyi'eee');"

reason=You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"enron"."EMP7400"(NAME) VALUES ('windyi'eee')' at line 1, error code: 1064, SQL state: 42000

Redshift - Execute

When you pass a special character (') within the single quotes ('').

"reason": "Invalid query: insert into singlequote values (101,'na'me')

"reason": "Invalid query: This may be raised due to limitations in the JDBC driver or syntax errors in the SQL statement",

Generic JDBC - Insert

SQL Server - Insert

When the Generic JDBC—Insert Snap has a datatype mismatch.

reason=Error occurred while converting the value of bigintcol field, error=Invalid value for number field definition

reason=Error converting value bigintcol to type class java.lang.Long, error=Error converting value

Oracle - Insert

reason=ORA-01438: value larger than specified precision allowed for this column , error code: 1438, SQL state: 22003, error=SQL operation failed

[{"reason":"The string value is not a number","error":"Invalid integer: value10"}]

SAP HANA - Insert

reason=The string value is not a long integer or too large for a long integer, error=Invalid long integer: bigintcol

reason=SQL [null]; SAP DBTech JDBC: Cannot convert data bigintcol to type long., error=SQL operation failed

Vertica - Insert

When you attempt to insert null, and, there is a datatype mismatch.

reason=Error converting value bigintcol to type class java.lang.Integer, error=Error converting value

reason=[Vertica][VJDBC](3681) ERROR: Invalid input syntax for integer: "bigintcol", error code: 3681, SQL state: 22V02, error=Batch operation failed

PostgreSQL

When you integrate with the PostgreSQL database and attempt to insert a String value in an Int type.

"error": "Invalid long integer: bigintcol",

"reason": "The string value is not a long integer or too large for a long integer"

"error": "Error converting value",

"reason": "Error converting value bigintcol to type class java.lang.Long"

DB2

When you integrate DB2 and JDBC Snaps with the char data type that is more than the prescribed range.

Error: SQLCODE=-433

Error: SQLCODE=-302

Sybase - Insert

When you insert more than the range of Int signed value.

error=Batch operation failed, reason=Data truncation, error code: 0, SQL state: 22001, next exception: Arithmetic overflow during implicit conversion of DECIMAL value '2147483648' to a INT field.

error=Number overflow: 2147483648, reason=Error occurred while converting the value of int_signed_col field, resolution=Please correct the value to match the expected data type, Reason: Expected and input data are not equal

When you insert varchar to type Int.

error=Batch operation failed, reason=Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query. , error code: 257, SQL state: 37000, resolution=Please check for valid Snap properties and input data.

error=Error converting value, reason=Error converting value P to type class java.lang.Integer, resolution=Please provide input data in the appropriate data type., Reason: Expected and input data are not equal, Resolution: Please check expected and input data.

When you insert varchar to type date.

error=Batch operation failed, reason=Syntax error during implicit conversion of VARCHAR value 'Hello' to a DATETIME field. , error code: 249, SQL state: S1000, resolution=Please check for valid Snap properties and input data.

error=Error converting value, reason=Error converting value Hello to type class java.sql.Timestamp, resolution=Please provide input data in the appropriate data type. Reason: Expected and input data are not equal, Resolution: Please check expected and input data.

When you insert varchar to type TinyInt Signed.

error=Error converting value, reason=Error converting value P to type class java.lang.Byte, resolution=Please provide input data in the appropriate data type.

error=Error converting value, reason=Error converting value P to type class org.jooq.types.UByte, resolution=Please provide input data in the appropriate data type. Reason: Expected and input data are not equal, Resolution: Please check expected and input data.

Generic JDBC - Execute and Generic JDBC - Insert

Sybase-Execute

When Generic JDBC - Execute Snap inserts out of range in Positive for money type.

reason=Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478.5807' to a MONEY field . , error code: 0, SQL state: 22001, next exception: Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478.5807' to a MONEY field

reason=Data truncation, error code: 0, SQL state: 22001, next exception: Arithmetic overflow during implicit conversion of NUMERIC value '922337203685478.5807' to a MONEY field.