Versions Compared

Old Version 1

changes.mady.by.user Lakshmi Manda

Saved on

compared with

New Version 2

changes.mady.by.user Lakshmi Manda

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Table of Contents
stylenone

FAQ for the

...

Vertica jOOQ Upgrade

What is happening?

We plan to upgrade the JDBC driver jOOQ library for the PostgreSQL Vertica Snap Pack from v9v3.49.1207 1 to v42v3.7.2 (Java 8). This upgrade 17.x, which will be part of the GA release on August 14, 2024 (Stable release). As a part of this upgrade, the 42.7.2 JDBC driver is bundled with the PostgreSQL Snap Pack as the default JDBC driverLatest release.

Why are we upgrading the

...

Vertica jOOQ library?

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

...

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

...

Yes, you will be impacted if you use the default version. The impact might be minimal if you use the latest driver library or a higher version than the v42v3.7.2 (Java 8) JDBC driver17.x.

  • To check the version of the current JAR file that jOOQ version you are using, refer to PostgreSQL's latest versionscheck 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.

...

  1. If you have a production Org to validate connections to your PostgreSQL Vertica instances, you may upload a PostgreSQL JDBC driver JAR compatible with postgres 9.4.1207.jar.

  2. Upload that JAR file to your Org’s shared folder and specify it in the PostgreSQL Account.

  3. Validate the account.

  4. After you verify that the updated JDBC driver works, you may remove it.

How do you override the behavior changes?

Use the postgres 9.4.1207.jar from v9.4.1207 jOOQ library v3.17.x from v3.9.1 to maintain the same behavior as before the driver library upgrade.

Behavior Changes

This driver 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:

PostgreSQL

Vertica Snaps

Scenario

Error/Output from the

old

Old (

v9

v3.

4

9.

1207

1)

JDBC Driver

JOOQ Library

Error/Output from the

new

New (

v42

v3.

7.2 (Java 8)) JDBC DriverPostgreSQL

17.x) JOOQ Library

Vertica-Execute

When you

provide multiple statements in the SQL Statement field, the Snap fails.

Status: 0

Output message: Success

Status: -3

Error message: Batch operation failed

PostgreSQL-Select

PostgreSQL-Lookup

PostgreSQL-Execute

Table columns of the Array type now output the data as a list instead of a string.

Output as a string:

{array_col_value={a, b, c}} 

Output message is now in an array:

{array_col_value=[a, b, c]}

PostgreSQL-Delete

PostgreSQL-Execute

PostgreSQL-Insert

PostgreSQL-Update

When the input value provided for a column is invalid for the column type.

Error Message: SQL operation failed

Reason: ERROR: invalid input syntax for type "DataType": "Invalid input" Position: <number>, error code: 0, SQL state: 22007

 

Error Message: Batch operation failed

Reason: Batch entry 0 <query> was aborted: ERROR: invalid input syntax for type 'Col DataType' : "invalid input" Position: <number> Call getNextException to see other errors in the batch., error code: 0, SQL state: 22007, next exception: ERROR: invalid input syntax for type 'Col DataType': "invalid input" Position: <number>

PostgreSQL-Select

 

When the metadata is returned from the Snap’s second output view.

The original number of columns in the input data is displayed in the output.

In Select Snap, the second output view, some new columns are introduced.
For example, IS_GENERATEDCOLUMN , REF_GENERATION, SELF_REFERENCING_COL_NAME, TYPE_CAT, TYPE_NAME, TYPE_SCHEM

PostgreSQL-Delete

 

When the delete condition has an unterminated quote string.

Error message: SQL operation failed
Reason : ERROR: unterminated quoted string at or near "' )"\n Position: <number>, error code: 0, SQL state: 42601

Error message: SQL operation failed
Reason: "SQL [<query>]; Unterminated string literal started at position <number> in SQL <query>. Expected char"

PostgreSQL-Execute

When you provide the following query:

"create table <name> as <query>"

Status : 0
Message : Success

Status: 5
The <number> for Status indicates the number of rows used from the source to create the new table.
Message: Success

All PostgreSQL Snaps

When the Dynamic Account has incorrect credentials.

Error : SQL operation failed
Reason : "java.lang.RuntimeException: Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true, Driver org.postgresql.Driver claims to not accept jdbcUrl, jdbc:postgresql://??postgresql-v10.nia.snaplogic.com:5432/snapteam?OpenSourceSubProtocolOverride=true"

Error message: SQL operation failed
Reason : "Failed to retrieve a database connection."

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}