JDBC Snap Pack

On this Page

Overview

Java database connectivity (JDBC) is a Java-based data access technology.

Use Snaps in this Snap Pack to:

  • Select, insert, update, and delete records in a database.
  • Create and remove databases based on the database type.

Prerequisites

  • Appropriate permissions to perform CRUD operations on the database.
  • The Snaplex server should have access to database IP and Port.
  • Timestamp format 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. If you are unable to find a JDBC driver for your database, as a last resort, you may consider using a JDBC-ODBC bridge.

Supported Versions

This Snap Pack is tested against the following databases:

DatabaseVersion
Cassandra1.2, 2.0, and 3.0.
MySQL

5.6.34
8.0.17

Mongo DB2.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

Oracle11.2.0.4.0, 19c
SAPHANA2.00.040.00.1553674765
RedshiftPostgreSQL 8.0.2
Redshift 1.0.21556
Snowflake2.48.1 (driver change version: 3.5.4
PostgreSQL

PostgreSQL 9.6.19,

PostgreSQL 10.12

Teradata15.10.07.08,16.20.22.01
Vertica and DB2 Vertica Analytic Database v10.0.0-0

Supported Objects and Task Operations

SnapOperation

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

Typical JDBC Connection Properties for Various Databases

SAP HANA

To download SAP HANA you will need an SAP ID. You can then download it at http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/webcontent/uuid/402aa158-6a7a-2f10-0195-f43595f6fe5f

Sybase

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.

  • Required File(s): jconn4.jar
  • DRIVER CLASS: com.sybase.jdbc4.jdbc.SybDriver
  • JDBC URL Format: jdbc:sybase:Tds:<host>:<port2048>/<database> 
    Example: jdbc:sybase:Tds:10.0.0.2:2048, jdbc:sybase:Tds:mupp:5000/dbvistest


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.


To download Sybase JDBC Driver please visit: http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit/jconnect

Another open source alternative to connect Sybase database is  jTDS Drivers, which you can find at http://jtds.sourceforge.net/

jTDS Driver 

  • DRIVER CLASS: net.sourceforge.jtds.jdbc.Driver 
  • JDBC URL FORMAT: jdbc:jtds:sybase://<host>[:<port>][/<database_name>] 
    Examples:  jdbc:jtds:sybase://127.0.0.1:5000/SAMPLE



Snap Pack History

 Click to view/expand
Release Snap Pack VersionDateType  Updates
4.27main12833 Stable
4.26 main11181 StableUpgraded with the latest SnapLogic Platform release.
4.25 Patch425patches11008 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.25main9554
 
StableUpgraded with the latest SnapLogic Platform release.
4.24 Patch4.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.24main8556
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 In Schema. If the Output Fields property is empty all the columns are visible.

4.23 Patch423patches8027 Latest

Fixes the multiple connection issue in the JDBC Snap Pack that occurs when the Auto Detect field is enabled for each execution of the Snap by storing a copy of the database name.

4.23 Patch

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.23main7430
 
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 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.22 Patch 422patches7246 Latest

Fixes the file descriptor error in the Snap Pack by removing logging exception which was causing log file spam.

4.22 Patch422patches6772 Latest

Fixes the issue with Auto detect in Generic JDBC - Execute Snap by implementing the JDBC Auto detect feature to use the correct database name.

4.22main6403
 
StableUpgraded with the latest SnapLogic Platform release.
4.21 Patch421patches6272 Latest

Fixes the issue where Snowflake SCD2 Snap generates two output documents despite no changes to Cause-historization fields with DATE, TIME and TIMESTAMP Snowflake data types, and with Ignore unchanged rows field selected.

4.21 Patch421patches6144 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 PatchMULTIPLE8841 Latest

Fixes the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends.

4.21snapsmrc542

 

StableUpgraded with the latest SnapLogic Platform release.
4.20 Patchjdbc8783 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 database.

4.20snapsmrc535
 
StableUpgraded 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 Update condition property.
  • Input data contain the character '?'.
4.19snaprsmrc528
 
StableUpgraded with the latest SnapLogic Platform release.
4.18snapsmrc523
 
Stable

Added support for JDBC account Active Directory authentication for SQL server for driver JAR version mssql-jdbc-6.2.2-jre8.jar.

4.17ALL7402
 
Latest

Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.

4.17snapsmrc515
 
Latest
  • Fixes an issue with the Generic JDBC Execute 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 PatchMULTIPLE7123 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.16snapsmrc508
 
StableUpgraded with the latest SnapLogic Platform release.
4.15snapsmrc500
 
StableUpgraded with the latest SnapLogic Platform release.
4.14snapsmrc490
 
StableUpgraded with the latest SnapLogic Platform release.
4.13 Patchjdbc5231 Latest

Added binding information for DB2 database connection on the Generic JDBC Snap Pack to ensure that pipelines correctly validate and execute.   

4.13 Patchjdbc5229 Latest

Added test connection query for SQLMX database in the JDBC Accounts to check if the database connection is valid.

4.13

snapsmrc486

 
StableUpgraded with the latest SnapLogic Platform release.
4.12 PatchMULTIPLE4744 Latest

Added support to execute multiple queries in a single batch for Snowflake, this patch affects Generic JDBC Execute. 

4.12

snapsmrc480

 
StableUpgraded with the latest SnapLogic Platform release.
4.11snapsmrc465
 
StableUpgraded with the latest SnapLogic Platform release.
4.10

snapsmrc414

 
Stable

Adds Auto commit property to the Select and Execute Snaps at the Snap level to support overriding of the Auto commit property at the Account level.

4.9.0 Patchjdbc3242 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 Patchjdbc3205 Latest

Fixes auto-commit issue for PostgreSQL SELECT query.

4.9.0 Patch jdbc3133 Latest

Fixed error handling of the limit clause by adding Apache Derby support.

4.9.0 Patchjdbc3069 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.9snapsmrc405
 
StableUpgraded with the latest SnapLogic Platform release.
4.8.0 Patchjdbc2801 Latest

Addressed an issue with Generic JDBC - Execute Snap holding the resultset in memory, exhausting memory and causing a core dump.

4.8.0 Patchjdbc2753 Latest

Potential fix for JDBC deadlock issue.

4.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 Patchjdbc2195 Latest

Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.

4.7

snapsmrc382

 
StableUpgraded with the latest SnapLogic Platform release.
4.6snapsmrc362
 
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 Select Snap with Snowflake account that 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.1N/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.4N/A StableUpgraded with the latest SnapLogic Platform release.
4.3.2N/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.1N/AN/AStable
  • 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.2N/AN/AStable
  • 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.1N/AN/AStable
  • Resolved Generic JDBC Execute not working with value substitution.

  • Resolved JDBC Execute unable to read CLOB columns via jTDS/JDBC.