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

Owned by
Lakshmi Manda
Last updated: Jun 28, 2024 by
Kalpana Malladi

FAQ for the Vertica jOOQ Upgrade

What is happening?

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

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 as part of 437patches26900 (Latest release) on June 28, 2024, and will be part of the 4.38 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 jOOQ library JAR file that is in use, you can do it using command-line tools such as jar or by inspecting the metadata of the JAR file.

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 library. However, if you currently use the default version and want to revert to the previous version behavior, we recommend you use v3.9.1.

We recommend you investigate your pipelines and check the Snaps downstream of Vertica 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.

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 resulted in specific behavior changes in the Snap Pack. These changes are for status codes and success and failure messages. This table lists these changes:

  • Previously, when you passed NaN to Date/Integer/Timestamp data type, the Vertica -Execute Snap displayed a null value in the reason for the error message. The Snap now displays a query in the reason.

  • Change in the output message of Vertica Snaps where the Snaps displayed date, time, and timestamp as objects in the output message. Now, the Snap displays the values directly.

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/Integer/Timestamp 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"}]

When you pass a special 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"}}]

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}]

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}

When you provide a 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}}

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}

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

 

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