FAQ for the Vertica jOOQ Upgrade

What is happening?

We plan to upgrade the jOOQ library for the Vertica Snap Pack from v3.9.1 to v3.17.x, which will be part of the Latest release.

Why are we upgrading the Vertica jOOQ library?

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

What is the timeline for this upgrade?

This upgrade will be available on <TBD> (Latest release), 2024, and will be part of the GA release on August 14, 2024 (Stable release).

Will I be impacted?

Yes, you will be impacted if you use the default version. The impact might be minimal if you use the latest library or a higher version than the v3.17.x.

To check the version of the current jOOQ version you are using, check the POM File (if using Maven).
To know the JAR file that is in use, follow these steps:
1. Open the JAR file using a ZIP utility or a JAR viewer tool.
2. Look for the META-INF/MANIFEST.MF file in the JAR and check the version.

You will not be impacted if you provide your own custom driver.

Do I need to reconfigure my accounts?

No, you do not have to reconfigure your account settings or drivers; the account can remain as-is to work with the latest driver. However, if you currently use the default version and want to revert to the previous version behavior, we recommend you use the postgres 9.4.1207.jar.

We recommend you investigate your pipelines and check the Snaps downstream of PostgreSQL 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. Learn more about the change in errors and outputs for the new (v42.7.2) JDBC driver.

How can I verify that the changes work?

In a production Org

If you have a production Org to validate connections to your Vertica instances, you may upload a PostgreSQL JDBC driver JAR compatible with postgres 9.4.1207.jar.
Upload that JAR file to your Org’s shared folder and specify it in the PostgreSQL Account.
Validate the account.
After you verify that the updated JDBC driver works, you may remove it.

How do you override the behavior changes?

Use the jOOQ library v3.17.x from v3.9.1 to maintain the same behavior as before the library upgrade.

Behavior Changes

This library upgrade will result in specific behavior changes in the Snap Pack. These changes are for status codes and success and failure messages. This table lists these changes:

Vertica Snaps	Scenario	Error/Output from the Old (v3.9.1) JOOQ Library	Error/Output from the New (v3.17.x) JOOQ Library
Vertica-Execute	When you pass `NaN` to `Date` data type.	[{"error":"SQL operation failed","reason":"SQL [null]; [Vertica][VJDBC](2624) ERROR: Column \"c_date\" does not exist"}]	[{"error":"SQL operation failed","reason":"SQL [insert into table_NaN_test (c_date) values ('NaN')]; [Vertica][VJDBC](2624) ERROR: Column \"c_date\" does not exist"}]
Vertica-Execute	When you pass `NaN` to an `Integer` data type.	[{"error":"SQL operation failed","reason":"SQL [null]; [Vertica][VJDBC](3681) ERROR: Invalid input syntax for integer: \"NaN\""}]	[{"error":"SQL operation failed","reason":"SQL [insert into public.table_nan (c_int) values ('NaN')]; [Vertica][VJDBC](3681) ERROR: Invalid input syntax for integer: \"NaN\""}]
Vertica-Execute	When you pass `NaN` to the `Timestamp` data type.	[{"error":"SQL operation failed","reason":"SQL [null]; [Vertica][VJDBC](3679) ERROR: Invalid input syntax for date: \"NaN\""}]	[{"error":"SQL operation failed","reason":"SQL [insert into public.table_nan (c_date) values ('NaN')]; [Vertica][VJDBC](4883) ERROR: Table \"public.table_nan\" does not exist"}]
Vertica-Execute	when you pass a spl character( ' )	[{"reason": "Invalid query: INSERT INTO \"public\".\"emp\"(ename) VALUES ('windyi'eee');","resolution": "Please check for valid Snap properties and input data."} ]	[{"reason":"SQL [INSERT INTO \"public\".\"emp\"(ename) VALUES ('windyi'eee');]; [Vertica][VJDBC](100111) Missing a matching closing delimiter in statement INSERT INTO \"public\".\"emp\"(ename) VALUES ('windyi'eee');;","resolution":"Please check for valid Snap properties and input data."}]
Vertica-Select	When Order by field is expression enabled	Output message: `[{"empno":602,"job":"qa","ename":"Coco","doj":{"Date":"2014-03-04"},"original":{"empno":"empno"}}]`	Output message: `[{"empno":602,"job":"qa","ename":"Coco","doj":"2014-03-04","original":{"empno":"empno"}}]`
Vertica-Select	When you provide a value in Order by field	Output message: `{empno=602, job=qa, ename=Coco, doj={Date=2014-03-04}}`	Output message: `{empno=602, job=qa, ename=Coco, doj=2014-03-04}]`
Vertica-Select	When you provide table and schema name as pipeline parameters	Output message: `{ename=Coco, job=qa, empno=602, doj={Date=2014-03-04}}`	Output message: `{empno=602, job=qa, ename=Coco, doj=2014-03-04}`
Vertica-Select	When you provide table and schema name as input from Snap.	Output message: `{empno=602, job=qa, ename=Coco, doj={Date=2014-03-04}, original={schema=public, table=emp}}`	Output message: `{empno=602, job=qa, ename=Coco, doj=2014-03-04, original={schema=public, table=emp}}`
Vertic- Select	When you select the Match data types checkbox.	Output message: `{C_DATE={Date=2017-05-05}, C_TIME={Time=00:00:00}, C_TIMESTAMP={Timestamp=2017-05-06T00:00:00.000+0000}, C_TIMESTAMPTZ={TimestampTz=2017-05-06T00:00:00.000+0000}}`	Output message: `{C_DATE=2017-05-05, C_TIME=00:00:00.000, C_TIMESTAMP=2017-05-06T00:00:00.000, C_TIMESTAMPTZ=2017-05-05T17:00:00.000}`
Vertica-Select	When you preserve date and time values	Output message: `{C_DATE={Date=2017-05-05}, C_TIME={Time=12:00:00}, C_TIMESTAMP={Timestamp=2017-07-05T12:00:00.000+0000}, C_TIMESTAMPTZ={TimestampTz=2017-08-05T19:00:00.000+0000}}`	Output message: `{C_DATE=2017-05-05, C_TIME=17:30:00.000, C_TIMESTAMP=2017-07-05T17:30:00.000, C_TIMESTAMPTZ=2017-08-05T17:30:00.000}`
Vertica-Select	Basic test	Output message: `{ename=Coco, job=qa, empno=602, doj={Date=2014-03-04}}`	Output message: `{empno=602, job=qa, ename=Coco, doj=2014-03-04}`
Vertica-Select	When you enable the second output view	IS_AUTOINCREMENT column details appear in response.	IS_AUTOINCREMENT column details do not appear in response.
Vertica-Bulk Load	When the Auto commit checkbox is deselected	Output message: `{datecol={Date=1999-01-08}, Timestamp={Timestamp=2004-10-19T10:23:54.000+0000}, Timewithtimezone={TimestampTz=2004-10-19T14:23:54.000+0000}}`	Output message: `{datecol=1999-01-08, Timestmp=2004-10-19 10:23:54, Timewithtimezone=2004-10-19 10:23:54-04}`
Vertica-Lookup Vertica-Delete	When you provide a value in the Number of retries	Output message: `{ID=4, Name=john @$%^ Test, TESTDATE={Timestamp=2021-02-10T10:13:29.000+0000}, TESTDATETIMEZONE={TimestampTz=2007-05-08T07:05:29.000+0000}, VALUE=1580000.54545455, original={ID=4, Name=ashok @$%^ Test, TESTDATE={Timestamp=2021-02-10T10:13:29.000+0000}, TESTDATETIMEZONE={TimestampTz=2007-05-08T07:05:29.000+0000}, VALUE=1580000.54545455}}`	Output message: `{ID=4, Name=ashok @$%^ Test, TESTDATE=2021-02-10T10:13:29.000, TESTDATETIMEZONE=2007-05-08T12:35:29.000, VALUE=1580000.54545455, original={ID=4, Name=ashok @$%^ Test, TESTDATE=2021-02-10T10:13:29.000, TESTDATETIMEZONE=2007-05-08T12:35:29.000, VALUE=1580000.54545455}}`
All Snaps	Boundary values check	Output message: `{EmployeeID=-2147483648, FirstName=Jane, LastName=Smith, DateOfBirth={Date=1995-12-31}, Gender=F, Salary=-9999999999999.99, IsActive=false, DepartmentID=-32768, JoiningDate={Timestamp=2022-01-02T10:00:00.000+0000}, Image=null}`	Output message: `{EmployeeID=-2147483648, FirstName=Jane, LastName=Smith, DateOfBirth=1995-12-31, Gender=F, Salary=-9999999999999.99, IsActive=false, DepartmentID=-32768, JoiningDate=2022-01-02T10:00:00.000, Image=null}`

Vertica jOOQ Upgrade from v3.9.1 to v3.17.x