Older Versions|Additional Resources|Related Links|Related Information
Java database connectivity (JDBC) is a Java-based data access technology
The JDBC Snap Pack is designed to select, insert, update, delete & truncate records (i.e. CRUD operations) in a database. It may be also used to create and drop databases (depend on the database type). To perform the operations mentioned, an appropriate permission may be required.
You need the JDBC driver .jar files 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.
Please contact your database vendor to get the appropriate driver. Additionally, there are third-party companies that sell commercial JDBC drivers. If you are unable to find a JDBC driver for your database, as a last resort, you may consider using a JDBC-ODBC bridge.
Panel
bgColor
#ebf7e1
borderStyle
solid
In this Section
Child pages (Children Display)
all
true
depth
2
Supported Objects and Task Operations
Generic JDBC - Insert Snap
Database Insert
Generic JDBC - Execute Snap
Database Query
Database Update
Database Upsert
Database Delete
Generic JDBC - Select Snap
Database Select
Generic JDBC - Update Snap
Database Update
Prerequisites
The JDBC Snap Pack requires the following:
The Snaplex server should have access to database IP and Port.
A JDBC driver must be installed and configured.
Timestamp format must match.
Typical JDBC Connection Properties for Various Databases
jConnect provides high performance native access to the complete family of Sybase products including Adaptive Server Enterprise, Adaptive Server Anywhere, Adaptive Server IQ, and Replication Server.
The jConnect JDBC driver is usually delivered as zip file. First you need to unzip this file and locate then jconn4.jar file which usually is stored in the classes directory.
Older Versions|Additional Resources|Related Links|Related Information
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).
Panel
bgColor
#ebf7e1
borderStyle
solid
In this Section
Child pages (Children Display)
all
true
depth
2
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.
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.
Supported Versions
This Snap Pack is tested against the following databases:
Updated and certified against the current SnapLogic Platform release.
February 2024
436patches25244
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
Stable
Added the following new Snaps to the JDBC Snap Pack:
Generic JDBC - Table List: Obtains a list of tables associated with a specified schema from the database.
November 2023
435patches24781
Latest
The database connection error message in the Generic Database Account is improved for clarity and readability.
November 2023
main23721
Stable
Updated and certified against the current SnapLogic Platform release.
August 2023
main22460
Stable
Updated and certified against the current SnapLogic Platform release.
May 2023
main21015
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
Stable
Upgraded with the latest SnapLogic Platform release.
November 2022
main18944
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
Latest
The Generic JDBC Snaps connecting to the DB2 database now take lesser time to execute thereby improving the performance.
August 2022
main17386
Stable
Enhanced the Generic Database account Support dynamic values for Account, Advanced, and URL properties.
4.29 Patch
429patches16588
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
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
Stable
Enhanced the following Snaps to support DML (Data Manipulation Language) operations (CREATE, ALTER, INSERT, and SELECT) when using AWS Athena database.
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.
Upgraded with the latest SnapLogic Platform release.
4.25 Patch
425patches11008
Latest
Fixed an issue with Generic JDBC - Select Snap where if there are URL properties in the account, the unPooledConnection does not use the properties when creating a connection. This causes the auto-detect functionality to use the wrong database name.
Fixed an issue with MySQL and MariaDB in Generic JDBC - Update Snap when a table is incorrectly reported as not being found.
4.25
main9554
Stable
Upgraded with the latest SnapLogic Platform release.
4.24 Patch
4.24patches8903
Latest
Fixes the Pipeline execution failure issue when using the JDBC Snaps with a Microsoft Access account by setting the correct quotation handler for the configured account
.4.24 (main8614)
.
4.24
main8556
Stable
Enhances the Generic JDBC - Select Snap to return only the fields (provided in the Output Fields) in the output schema (second output view), through a new check box Fetch Output Fields InSchema. If the Output Fields property is empty all the columns are visible.
field is enabled for each execution of the Snap by storing a copy of the database name.
4.23 Patch
423patches7800
423patches7800
Latest
Fixes an issue with JDBC account through a Test Query option that establishes a database connection upon using Auto detect option for the Database name.
4.23
(main7430)
main7430
Stable
Fixes an issue with the JDBC account that displays a connection is closed error when connecting with the SQL server.
Fixes an issue with the JDBC Snaps
thatfail
that fail while displaying the javax.management.MalformedObjectNameException: Invalid character '=' in value part of property error repeatedly when there is “=“ or ”:” in the JDBC URL connection.
4.
4.22 Patch 422patches7246
22 Patch
422patches7246
Latest
Fixes the file descriptor error in the Snap
Pack by
Pack by removing logging exception which was causing log file spam.
Snap generates two output documents despite no changes
to
to Cause-historization
fields with
fields with DATE, TIME and
TIMESTAMP
TIMESTAMP Snowflake data types, and
with
with Ignore unchanged rows
field
field selected.
4.21 Patch
421patches6144
Latest
Fixes the following issues with DB Snaps:
The connection thread waits indefinitely causing the subsequent connection requests to become unresponsive.
Connection leaks occur during Pipeline execution.
4.21 Patch
MULTIPLE8841
Latest
Fixes the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends.
4.21
(snapsmrc542)
No updates made.
4.20 Patch jdbc8783
snapsmrc542
Stable
Upgraded with the latest SnapLogic Platform release.
4.20 Patch
jdbc8783
Latest
Fixes the Generic JDBC Execute Snap that adds the value of the previous column's field to a NULL field when SELECT queries having duplicate column names are run on
the PostgreSQL
the PostgreSQL database.
4.20
(snapsmrc535)No updates made
snapsmrc535
Stable
Upgraded with the latest SnapLogic Platform release.
4.19 Patch
db/jdbc8405
Latest
Fixes an issue with the Generic JDBC - Update Snap wherein the Snap is unable to perform operations when:
An expression is used in
the
the Update condition
property
property.
Input data contain the character '?'.
4.19
(snapsmrc528)No updates made
snaprsmrc528
Stable
Upgraded with the latest SnapLogic Platform release.
4.18
(snapsmrc523)
snapsmrc523
Stable
Added support for JDBC account Active Directory authentication for SQL server for driver JAR version mssql-jdbc-6.2.2-jre8.jar.
4.17
Patch ALL7402
ALL7402
Latest
Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.
4.17
(snapsmrc515)
snapsmrc515
Latest
Fixes an issue with
the Generic
the Generic JDBC Execute
Snap wherein
Snap wherein the Snap would send the input document to the output view even if the Pass through field is not selected in the Snap configuration. With this fix, the Snap sends the input document to the output view, under the key original, only if you select the Pass through field.
Added the Snap Execution field to all Standard-mode Snaps. In some Snaps, this field replaces the existing Execute during preview check box.
4.16
Patch MULTIPLE7123
Patch
MULTIPLE7123
Latest
Added two new properties, Number of retries and Retry interval, to Execute, Insert, Select, and Update Snaps that enables you to handle retry attempts during a connection failure.
4.16
(snapsmrc508)No updates made. Automatic rebuild with a platform
snapsmrc508
Stable
Upgraded with the latest SnapLogic Platform release.
4.15
(snapsmrc500)No updates made. Automatic rebuild with a platform
snapsmrc500
Stable
Upgraded with the latest SnapLogic Platform release.
4.14
(snapsmrc490)No updates made. Automatic rebuild with a platform
snapsmrc490
Stable
Upgraded with the latest SnapLogic Platform release.
4.
13 Patch jdbc5231
13 Patch
jdbc5231
Latest
Added binding information for DB2 database connection on the Generic JDBC Snap Pack to ensure that pipelines correctly validate and execute.
4.13
Patch jdbc5229
Patch
jdbc5229
Latest
Added test connection query for SQLMX database in the JDBC Accounts to check if the database connection is valid.
4.13
(snapsmrc486)No updates made. Automatic rebuild with a platform
snapsmrc486
Stable
Upgraded with the latest SnapLogic Platform release.
4.12 Patch
MULTIPLE4744
Latest
Added support to execute multiple queries in a single batch for Snowflake, this patch affects Generic JDBC Execute.
4.12
(snapsmrc480)No updates made. Automatic rebuild with a platform
snapsmrc480
Stable
Upgraded with the latest SnapLogic Platform release.
4.
11 (snapsmrc465)No updates made. Automatic rebuild with a platform
11
snapsmrc465
Stable
Upgraded with the latest SnapLogic Platform release.
4.10
(snapsmrc414)Adds Auto commit property
snapsmrc414
Stable
Adds Auto commit property to the Select and Execute Snaps at the Snap level to support overriding of
the
the Auto commit
property
property at the Account level.
4.9.0 Patch
jdbc3242
Latest
Addresses an issue in JDBC Execute Snap with functions not working (fixes by executing commit() at the end of the processing query).
4.9.0 Patch
jdbc3205
Latest
Fixes auto-commit issue for PostgreSQL SELECT query.
4.9.0
Patch db/jdbc3133Error in handling
Patch
jdbc3133
Latest
Fixed error handling of the limit clause
has been fixed
by adding Apache Derby support.
4.9.0 Patch
jdbc3069
Fixes
Latest
Fixed an issue regarding connection not closed after login failure; Expose autocommit for "Select into" statement in PostgreSQL Execute Snap and Redshift Execute Snap.
4.9
(snapsmrc405)No updates made. Automatic rebuild with a platform
snapsmrc405
Stable
Upgraded with the latest SnapLogic Platform release.
4.8.0 Patch
jdbc2801
Latest
Addressed an issue with Generic JDBC - Execute Snap holding the resultset in memory, exhausting memory and causing a core dump.
4.8.0 Patch
jdbc2753
Latest
Potential fix for JDBC deadlock issue
.
4.8 (snapsmrc398)
Updated the default value for the property Min pool size to 0.
Info tab added to accounts.
Database accounts now invalidate connection pools if account properties are modified and login attempts fail
.
4.
7 Patch jdbc2195
Fixes an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.
4.6
Enhanced Generic JDBC Execute Snap to fully support SQL statements with/without expressions & SQL bind variables.
Resolved an issue in Generic JDBC Insert Snap that caused unexpectedly failures on Snowflake and connecting to DB2.
Resolved an issue in Generic JDBC Select Snap that caused errors with Teradata database instance.
Resolved an issue in Generic JDBC Select Snap with Snowflake account that caused an error when auto-commit was not selected.
Resolved an issue in Generic JDBC Select Snap with Snowflake account that caused an error when a merge statement was used in the Snap.
Info
title
Known Issue
JDBC Insert: "The Maximum Possible Row Length in the Table is too Large" error message: You will see this message if trying to insert data into Teradata that is larger than Teradata can handle (rows are limited to approximately 64,000 bytes). Workaround: Use smaller default column size, e.g., 32000 bytes.
4.5.1
Resolved an issue with JDBC Select failing with a syntax error when special characters were used.
4.5.0
Verified Generic JDBC Snaps connections to Hive. See JDBC Account for configuration information. SELECT and INSERT are supported. SnapLogic does not support UPDATE, DELETE, or MERGE due to limitations in the drivers.
8
snapsmrc398
Stable
Updated the default value for the property Min pool size to 0.
Info tab added to accounts.
Database accounts now invalidate connection pools if account properties are modified and login attempts fail.
4.7 Patch
jdbc2195
Latest
Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.
4.7
snapsmrc382
Stable
Upgraded with the latest SnapLogic Platform release.
4.6
snapsmrc362
Stable
Enhanced Generic JDBC Execute Snap to fully support SQL statements with/without expressions & SQL bind variables.
Resolved an issue in Generic JDBC Insert Snap that caused unexpectedly failures on Snowflake and connecting to DB2.
Resolved an issue in Generic JDBC Select Snap that caused errors with Teradata database instance.
Resolved an issue in Generic JDBC Select Snap with Snowflake account that caused an error when auto-commit was not selected.
Resolved an issue in Generic JDBC
Insert
Select Snap with Snowflake account that
occurred when inserting records in batches.
Resolved an issue in Generic JDBC Insert Snap that occurred because of an SQL execution (internal) error.
Resolved an issue in Generic JDBC Select Snap that occurred because of an SQL query execution.
4.4.0
Added expression support to the Where clause property for Select Snaps.
Resolved an issue with the JDBC Select Snap failing with Teradata because of a Limit Offset.
Resolved an issue with JDBC Execute failing to execute a create or replace table statement with Snowflake.
Resolved issues with JDBC Insert Snap:
Failing to create a table when Create table if not present is selected.
An exception occurred when attempting to insert record containing date columns into Teradata.
4.3.2
Resolved an issue with Generic JDBC Update updating null into database when an invalid string value is provided as input for time datatype.
4.3.1
New! Generic JDBC SELECT Snap
New! Generic JDBC UPDATE Snap
Feature: JDBC Snaps - add a new property 'Database name' to JDBC Account
Defect: JDBC Execute Snap fails in preview for Oracle timestamp data type
Defect: JDBC Insert to Snowflake throws an error
Defect: Generic JDBC Execute with JDBC4OLAP drivers to connect to Essbase resulted in com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to parse content type: null
4.2.2
Ignore empty result added to Execute Snap. The option will not write any document to the output view for select statements producing no results.
Addressed an issue with idle timeouts.
Resolved an issue in JDBC Execute throwing an exception, unable to get driver.
4.2.1
Resolved Generic JDBC Execute not working with value substitution
Resolved JDBC Execute unable to read CLOB columns via jTDS/JDBC
June 27, 2015 (2015.22)
JDBC Execute Snap: SQL operation failed with an invalid batch command when using table name as an input variable.
JSON paths in WHERE clauses should be processed as bind values after the expression is evaluated.
June 6, 2015 (2015.20)
JDBC Insert Snap fails to insert joda LocalTime object to PosgreSQL TIME data type
Dynamic DB queries now supported in the Execute Snap. The SQL statement property now can be set as an expression property. When it is an expression, it will be evaluated with each input document and one SQL statement per each input document will be executed.
Info
title
Known Issue
When the SQL statement property is an expression, the pipeline parameters are shown in the suggest, but not the input schema.
Warning
With the SQL statement property set as an expression, the Snap can be exposed to SQL injection. Please use this feature with caution.
October 18, 2014
Addressed the following issues:
JDBC insert Snap giving invalid argument . error
JDBC insert Snap - null pointer exception
August 2013
Initial release of the JDBC Execute Snap, JDBC Insert Snap
caused an error when a merge statement was used in the Snap.
Known Issue:
JDBC Insert: "The Maximum Possible Row Length in the Table is too Large" error message: You will see this message if trying to insert data into Teradata that is larger than Teradata can handle (rows are limited to approximately 64,000 bytes). Workaround: Use smaller default column size, e.g., 32000 bytes.
4.5.1
snapsmrc344
Stable
Resolved an issue with JDBC Select failing with a syntax error when special characters were used.
4.5
snapsmrc344
Stable
Verified Generic JDBC Snaps connections to Hive. See JDBC Account for configuration information. SELECT and INSERT are supported. SnapLogic does not support UPDATE, DELETE, or MERGE due to limitations in the drivers.
Resolved an issue in Generic JDBC Insert Snap that occurred when inserting records in batches.
Resolved an issue in Generic JDBC Insert Snap that occurred because of an SQL execution (internal) error.
Resolved an issue in Generic JDBC Select Snap that occurred because of an SQL query execution.
4.4.1
N/A
Stable
Added expression support to the Where clause property for Select Snaps.
Resolved an issue with the JDBC Select Snap failing with Teradata because of a Limit Offset.
Resolved an issue with JDBC Execute failing to execute a create or replace table statement with Snowflake.
Resolved issues with JDBC Insert Snap:
Failing to create a table when Create table if not present is selected.
An exception occurred when attempting to insert record containing date columns into Teradata.
4.4
N/A
Stable
Upgraded with the latest SnapLogic Platform release.
4.3.2
N/A
Stable
Resolved an issue with Generic JDBC Update updating null into database when an invalid string value is provided as input for time datatype.
4.3.1
N/A
N/A
Stable
New! Generic JDBC SELECT Snap
New! Generic JDBC UPDATE Snap
Feature: JDBC Snaps - add a new property 'Database name' to JDBC Account
Defect: JDBC Execute Snap fails in preview for Oracle timestamp data type
Defect: JDBC Insert to Snowflake throws an error
Defect: Generic JDBC Execute with JDBC4OLAP drivers to connect to Essbase resulted in com.sun.xml.internal.messaging.saaj.SOAPExceptionImpl: Unable to parse content type: null
4.2.2
N/A
N/A
Stable
Ignore empty result added to Execute Snap. The option will not write any document to the output view for select statements producing no results.
Addressed an issue with idle timeouts.
Resolved an issue in JDBC Execute throwing an exception, unable to get driver.
4.2.1
N/A
N/A
Stable
Resolved Generic JDBC Execute not working with value substitution.
Resolved JDBC Execute unable to read CLOB columns via jTDS/JDBC.
