Overview

The JDBC Snap Pack enables seamless integration and data connectivity with diverse databases through JDBC (Java Database Connectivity) technology. This Snap Pack supports DCL commands (Grant and Revoke).

In this Section

This Snap Pack supports integration with AWS Athena database; however, it supports only create, alter, insert, and select operations.
Athena JDBC Driver 1.x versions are deprecated, and the Generic JDBC Snaps are incompatible with the Athena JDBC 1.x drivers. Hence, we recommend you to use Athena JDBC Driver 2.x or higher version when connecting to the Athena database.
Upgraded the JOOQ library of the JDBC Snap Pack from v3.9.1 to v3.17.x. Learn more.

This Snap Pack contains the following Snaps:

Generic JDBC - Execute: Execute a SQL statement/query against a SQL database.
Generic JDBC - Select: Retrieves data from a SQL database by executing a SQL SELECT statement/query.
Generic JDBC - Update: Updates data in a SQL database by executing a SQL UPDATE statement/query.
Generic JDBC - Insert: Inserts data into a SQL database by executing a SQL INSERT statement/query.
Generic JDBC - Schema List: Obtains a list of schemas for the specified account from the database.
Generic JDBC - Table List: Obtains a list of tables associated with a specified schema from the database.

Prerequisites

Appropriate permissions to perform CRUD operations on the database.
The Snaplex should have access to database IP and Port.
Timestamp format with respect to the database you are connecting must match.
A JDBC driver and .jar files must be installed and configured for the appropriate database to make a successful JDBC connection. The JDBC driver for each type of database you want to connect to may contain typically one .jar file or in some cases multiple .jar files.
Contact your database vendor to get the appropriate driver. Additionally, there are third-party companies that sell commercial JDBC drivers.

Breaking Change

Your existing pipelines that use JDBC Snaps to integrate with Oracle or Redshift databases using the bundled Oracle or Redshift JDBC drivers will stop functioning with the 438patches28052.
Workaround
You must manually upload the Oracle or Redshift JDBC driver in the JDBC Database account to run your pipelines successfully.
Download the drivers:
OJDBC: Maven Repository: com.oracle.database.jdbc » ojdbc6 » 11.2.0.4
Redshift: Maven Repository: com.amazon.redshift » redshift-jdbc42 » 2.1.0.11

Supported Versions

This Snap Pack is tested against the following databases:

Database	Version	Driver

Database	Version	Driver
Cassandra	3.0	`cassandra-jdbc-wrapper-3.7.2.jar`
DB2	v11.5.4.0	`db2jcc.jar`
Informix	Informix Dynamic Server 14.10.FC5DE	`shared/ifxjdbc.jar`
MySQL	5.6.34 8.0.17	`sql-jdbc-3.9.1.jar` `sqljdbc4-2.0.jar`
Maria DB	10.5.5	`mariadb-java-client-2.6.0.jar`
Mongo DB	2.6.12 3.4.2
SQL Server	11.00.2100, 12.0.20008, 13.0.4224.16, 14.0.1000.169 (Windows AD) 16.0	`mssql-jdbc-7.4.1.jre8.jar` `mssql-jdbc-9.4.jre8.jar`
Oracle	19c	`v11.2.0.4.0` or higher
SAPHANA	2.00.040.00.1553674765	`ngdbc.jar`
Redshift	RedshiftJDBC42-2.1.0.11 RedshiftJDBC42-1.2.10.1009	`Redshift-JDBC 42-2.0.0.4.jar`
Snowflake	3.13.25	Snowflake JDBC jar `3.0.0` or higher
PostgreSQL	PostgreSQL 9.6.19, PostgreSQL 10.12	`postgresql-9.4.1207.jar`
Teradata	15.10.07.08,16.20.22.01	`terajdbc4_13.jar, tdgssconfig_13.jar`
Vertica	v10.0	`shared/Vertica-6.1.0.jar`

Supported Objects and Task Operations

This Snap Pack provides support for the following objects and task operations:

Snap	Operation

Snap	Operation
Generic JDBC - Insert Snap	Database Insert
Generic JDBC - Execute Snap	Database Query, Database Update Database Insert, Database Delete
Generic JDBC - Select Snap	Database Select
Generic JDBC - Update Snap	Database Update

Snap Pack History

Release	Snap Pack Version	Date	Type	Updates
November 2024	439patches29008	Nov 28, 2024	Latest	Upgraded the JOOQ library of the JDBC Snap Pack from v3.9.1 to v3.17.x. Pipelines using the Generic - JDBC Snaps might be impacted by the jOOQ upgrade because the latest jOOQ version is not fully backward-compatible. Learn more. Fixed an issue with Generic JDBC - Execute and Select Snaps when connecting to the Oracle database where the data was displayed in an unexpected structure and without the actual row address value when querying table columns of ROWID type. Now, the Snaps handle the ROWID type columns correctly to prevent the display of the nested output when ROWID is combined in the query with other selected columns. Breaking change: With the `439patches29008` Snap Pack version, if any of your existing pipelines with the Generic JDBC Execute and Select Snaps when connecting to the Oracle endpoint use the encoded ROWID(by manually handling the Base64 encoded data), your pipelines might break. To prevent your pipelines from failing, you must update your Snap Pack version to the previous version. Behavior change: Earlier, the ROWID columns were displayed in binary (Base64 encoded data) form in the output. With the `439patches29008`, the Generic JDBC Execute and Select Snaps that integrate with the Oracle endpoint display the ROWID columns in string form in the output (which can be used for other operations downstream).
November 2024	main29029	Nov 13, 2024	Stable	Updated and certified against the current SnapLogic Platform release.
August 2024	438patches28541	Oct 22, 2024	Latest	The JDBC Snap Pack version `438patches28052`, which removed the default bundled Oracle and Redshift drivers (JAR files), is now rolled back with `438patches28541`. Now, the drivers are bundled by default with the Oracle and Redshift Snap Packs. This rollback might cause a connectivity issue when connecting the JDBC Snaps to the Sybase database using the `jConnect` Driver. Based on your use case requirements, follow the below steps: To manually upload the custom Oracle or Redshift JDBC driver in the JDBC Database account, you must use the `438patches28052` Snap Pack version. To use the bundled Oracle and Redshift drivers, you must use the latest JDBC Snap Pack version `438patches28541`.
August 2024	438patches28052	Sep 18, 2024	Latest	Fixed a connectivity issue with the Generic JDBC Snaps that resulted in an error when attempting to connect the JDBC account to a Sybase database using the `jConnect` driver.
August 2024	main27765	Aug 21, 2024	Stable	Updated and certified against the current Snaplogic Platform release.
May 2024	437patches27416	Jul 29, 2024	Latest	Fixed an issue with the Generic JDBC—Insert Snap that exposed sensitive information in the error message when the batch operation was not successful.
May 2024	main26341	May 8, 2024	Stable	Updated and certified against the current SnapLogic Platform release.
February 2024	436patches25244	Feb 26, 2024	Latest	Fixed an issue with the Generic JDBC-Select, Insert, Update, and Table List Snaps that incorrectly displayed the Schema Name suggestions with the Cassandra driver connection. Fixed an issue with the Generic JDBC-Select Snap that displayed an empty result with the MariaDB driver connection for output 1.
February 2024	main25112	Feb 14, 2024	Stable	Added the following new Snaps to the JDBC Snap Pack: Generic JDBC - Schema List: Obtains a list of schemas for the specified account from the database. Generic JDBC - Table List: Obtains a list of tables associated with a specified schema from the database.
November 2023	435patches24781	Feb 6, 2024	Latest	The database connection error message in the Generic Database Account is improved for clarity and readability.
November 2023	main23721	Nov 8, 2023	Stable	Updated and certified against the current SnapLogic Platform release.
August 2023	main22460	Aug 16, 2023	Stable	Updated and certified against the current SnapLogic Platform release.
May 2023	main21015	May 10, 2023	Stable	The Generic Database Account now supports the SSH Tunneling connection. You can now encrypt the network connection between your client and the database server, ensuring a highly secure connection.
February 2023	main19844	Feb 9, 2023	Stable	Upgraded with the latest SnapLogic Platform release.
November 2022	main18944	Nov 10, 2022	Stable	The Generic JDBC - Insert Snap previously took a longer time to execute and was aborted after a few minutes, which affected the performance of the Snap. The Snap's performance is optimized now and takes lesser time for execution.
September 2022	430patches17894	Sep 16, 2022	Latest	The Generic JDBC Snaps connecting to the DB2 database now take lesser time to execute thereby improving the performance.
August 2022	main17386	Aug 11, 2022	Stable	Enhanced the Generic Database account Support dynamic values for Account, Advanced, and URL properties.
4.29 Patch	429patches16588	Jun 24, 2022	Latest	Fixed an issue with JDBC Select and JDBC Insert Snaps where the Snaps did not load suggestions for the Table property. Now the Snaps load the suggestions correctly. Enhanced the Generic Database account to support dynamic values for Account properties, Advanced properties, and URL properties so that you can pass these values through Pipeline parameters.
4.29	main15993	May 14, 2022	Stable	Enhanced the Generic Database Account in the JDBC Snap pack to include Netsuite as an option for the Database name field to connect to the NetSuite database. While configuring the account, if you select Netsuite or Autodetect (if the Snap detects the target database as NetSuite) then the Limit rows field in the Generic JDBC - Select Snap is ignored.
4.28	main14627	Feb 12, 2022	Stable	Enhanced the following Snaps to support DML (Data Manipulation Language) operations (CREATE, ALTER, INSERT, and SELECT) when using AWS Athena database. Generic JDBC - Execute Generic JDBC - Insert Generic JDBC - Select
4.27	main12833	Nov 13, 2021	Stable	Enhanced the Generic JDBC Database Account to skip batch execution, when the Batch size value is one. When the Batch size is greater than one, the batch is executed. Enhanced the Generic JDBC - Execute Snap to invoke stored procedures.