Skip to end of banner
Go to start of banner

FAQ for the JDBC jOOQ Upgrade from v3.9.1 to v3.17.x

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 10 Next »

What is happening?

The jOOQ library for the JDBC Snap Pack is upgraded from v3.9.1 to v3.17.x.

Why are we upgrading the JDBC jOOQ library?

To improve security, compatibility, performance, stability, and reliability.

What is the timeline for this upgrade?

This upgrade will be part of the Latest release after the November GA.

Will I be impacted?

Yes, you will be impacted if you use the latest version. If you prefer not to proceed with the jOOQ upgrade, you can revert to the previous version of the Snap Pack.

  • Given the various databases in use, we have tested against only these databases. However, we recommend testing your pipelines in your environment to ensure compatibility and functionality with your specific database before planning the jOOQ upgrade.

  • We recommend you investigate your pipelines and check the Snaps downstream of JDBC Snaps for any of the affected scenarios. Update any dependencies that the downstream Snaps might have with respect to the changing error and outputs (especially in expressions) to conform to the new expected errors and outputs.

Which databases were tested for this jOOQ upgrade?

Mysql
Postgresql
Redshift
Oracle
Snowflake

SQL Server
Maria DB
DB2
Informix
Cassandra

Teradata
Sybase ASE
Denodo
Vertica
SAPHANA

Do I need to reconfigure my accounts?

Yes, you need to reconfigure your accounts, if the current driver (JARs) is incompatible with the upgraded jOOQ version (v3.17.x). For more details, refer to the breaking changes.

What are the recommended driver JARs for different databases?

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-connector-j-8.0.17.jar

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

Breaking Changes

  • After upgrading the jOOQ library for the JDBC Snap Pack, previously functioning pipelines may fail. To prevent such issues, we recommend that you use the latest SnapLogic-certified drivers for the target databases. Here is a list of the SnapLogic-certified drivers.

  • Additionally, we highly recommend that you migrate to dedicated Database Snap Packs (for example, Oracle, SQL Server, MySQL, Redshift) instead of using the Generic JDBC Snap Pack for your integration needs. This will minimize potential pipeline failures.

Behavior Changes

This library upgrade will result in specific behavior changes in the Snap Pack, which affect the output, success, and failure messages.

  • Previously, when you ran multiple statements in the Generic JDBC - Execute Snap, only the reason appeared in the error. If your target database is Snowflake, the error reason now includes the Snowflake class appended in the beginning.
    Before: reason=Actual statement count 2 did not match the desired statement count 1
    After: reason=net.snowflake.client.jdbc.SnowflakeSQLException: Actual statement count 2 did not match the desired statement count 1

  • When Generic JDBC - Insert Snap inserts null into the database and there is a data type mismatch, a change in the error message has been observed.
    Before: reason=Error converting value bigintcol to type class java.lang.Integer, error=Error converting value
    After: reason=[Vertica][VJDBC](3681) ERROR: Invalid input syntax for integer: "bigintcol", error code: 3681, SQL state: 22V02, error=Batch operation failed

  • Previously, when you integrated DB2 and JDBC Snaps with the char data type, the SQLCODE=-433. Now, the error message is SQLCODE=-302.
    Before: SQLCODE=-433
    After: SQLCODE=-302

  • Previously, when you called stored procedures, the Execute Snap displayed only the success message in the output. Now, the Snap displays both the status and the message in the output for all databases.
    Before: Message: Success
    After: $UPDATE_COUNT=-1, 0, or 1 (based on the Snap Pack behavior) in the output.

  • Previously, when Generic JDBC—Select and Generic JDBC—Execute Snaps were integrated with PostgreSQL or IBM DB2, the metadata was displayed along with the XML data. Now, you only receive the XML data.
    Before: metadata + XML data
    After: XML data

  • Previously, when the Generic JDBC—Insert and Generic JDBC—Execute were integrated with the Sybase database and attempted to insert an out-of-range negative value for the money type, the error reason slightly changed.
    Before: reason=Arithmetic overflow during implicit conversion of NUMERIC value
    After: reason=Data truncation

  • Previously, when the Generic JDBC—Insert and Generic JDBC—Execute were integrated with the Sybase database and attempted to insert out-of-range values for int types like big int, small int, and tiny int, the error reason slightly changed.
    Before: reason=Arithmetic overflow during implicit conversion of NUMERIC value
    After: reason=Data truncation

  • Previously, when the Generic JDBC—Insert was integrated with the Sybase database and varchar was attempted to insert into int datatype, the error message and reason slightly changed.
    Before: 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.
    After: 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

  • Previously, when the Generic JDBC—Insert was integrated with the Sybase database and varchar was attempted to insert into the date datatype, the error message and reason slightly changed.
    Before: 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.
    After: 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.

  • Previously, when the Generic JDBC—Insert was integrated with the Sybase database and varchar was attempted to insert into tiny int datatype, the reason slightly changed.
    Before: reason=Error converting value P to type class java.lang.Byte
    After: reason=Error converting value P to type class org.jooq.types.UByte

  • Previously, when the Generic JDBC - Execute integrated with the Oracle, MySQL, PostgreSQL, or Redshift databases, and you attempted to pass a special character (') , the error reason slightly changed.
    Ex: Insert into table(ids) values ('windyi'eee')
    Before: "reason": "Invalid query: Insert into TECTONIC.SNPQA2055 (ids) values ('windyi'eee')"
    After: "reason": "error occurred during batching: ORA-00917: missing comma\n, error code: 17081",

The following table lists the change in the output messages:

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.

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.

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>"

The following table lists the change in the error messages:

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

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 .

 

 

  • No labels

Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.