Page Comparison

...

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:

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

When you pass NaN to the Timestamp data type.

[{"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 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\""}]

[{"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"}]

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

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

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

{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 the response.

IS_AUTOINCREMENT column details do not appear in the 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}

...

Versions Compared

Old Version 4

New Version 5

Key