On this Page

This account is used by the Snaps in the JDBC Snap Pack.

You can create an account from Designer or Manager. In Designer, when working on pipelines, every Snap that needs an account prompts you to create a new account or use an existing account. The accounts can be created in or used from:

Your private project folder: This folder contains the pipelines that will use the account.
Your Project Space’s shared folder: This folder is accessible to all the users that belong to the Project Space.
The global shared folder: This folder is accessible to all the users within an organization in the SnapLogic instance.

Account Configuration

In Manager, you can navigate to the required folder and create an account in it (see Accounts). To create an account for a generic JDBC driver:

If you have not already done so, upload the JDBC driver for this database as a file for the specific project.
Click Create, then select JDBC > Generic Database Account.
Supply an account label.
Supply the account properties for your database.
(Optional) Supply additional information on this account in the Notes field of the Info tab.
Click Apply.

Avoid changing account credentials while pipelines using them are in progress. This may lead to unexpected results, including locking the account.

If you have several JDBC accounts, one to system A and one to system B, then you need to make sure all drivers can be loaded. If driver A fails to load for some reason, then driver B will always fail, even though it is a valid driver, as this is how the JDBC driver registry works. It loads all drivers and goes through them in sequence. If any driver fails on the way, all drivers loaded later will also fail. Once you fix the driver for the failing account, the JCC needs to be restarted.
To disable the mysql-cj-abandoned-connection-cleanup thread that is generated by MySQL JDBC driver, use the latest version of MySQL (later than 8.0.22), and add -Dcom.mysql.cj.disableAbandonedConnectionCleanup=true to the jcc.jvm_options in the node properties under ‘Global Properties’.
If the Snap fails to connect to the database, it will retry three more times.

Account Encryption

Standard Encryption

If you are using Standard Encryption, the High sensitivity settings under Enhanced Encryption are followed.

Enhanced Encryption

If you have the Enhanced Account Encryption feature, the following describes which fields are encrypted for each sensitivity level selected for this account.

Account:

High: Password
Medium + High: Username, password
Low + Medium + High: Username, password, JDBC UR

Auto Commit with Execute Snaps

For a DB Execute Snap, assume that a stream of documents enters the input view of the Snap and the SQL statement property has JSON paths in the WHERE clause. If the number of documents are large, the Snap executes in more than one batches rather than executing one per each document. Each batch would contain a certain number of WHERE clause values. If Auto commit is turned on, a failure would only roll back the records in the current batch. If Auto commit is turned off, the entire operation would be rolled back. For a single execute statement (with no input view), the setting has no practical effect.

Active Directory authentication

SnapLogic supports Active Directory authentication for SQL server for driver JAR version mssql-jdbc-6.2.2-jre8.jar. Ensure that you have installed mssql-jdbc-6.2.2-jre8.jar file. Also, use the following configurations in Account Settings to configure Active Directory authentication:

JDBC Driver class: com.microsoft.sqlserver.jdbc.SQLServerDriver
JDBC Connection URL: jdbc:sqlserver://ServerNameFQDN:portNumber;databaseName=DBNAME

SnapLogic supports Active Directory authentication for SQL Server using the User impersonation method. The prerequisites are as follows:

In the account settings, add the following to Url property name and Url property value:
Url property name
Url property value
IntegratedSecurity True
AuthenticationScheme JavaKerberos
In the account settings, enter your Active Directory Username and Password.

Connecting to Cassandra Database Using Cassandra Simba Driver

To connect to Cassandra database using the Cassandra Simba driver ensure that the appropriate Simba JAR files are installed based on the version of JDK in the Snaplex node. Contact your Org admin to know the JDK in the Snaplex node. To see which JAR version is to be used, see JDBC Install Guide for details.

Aside from the JAR files, use the following configurations in the Account Settings when using the Cassandra Simba driver:

JDBC Driver class: "com.simba.cassandra.jdbc42.Driver"
JDBC URL: jdbc:cassandra://<host>:<port>;AuthMech=1;UID=<user id>;PWD=<password>;DefaultKeyspace=<database name>
Database name: Auto detect

For JDK 8 and Above

For JDK 8 and above, use version 4.2 of the Cassandra Simba Driver
Of the JARs included with version 4.2, do not include the following since they are already provided as part of the Snap pack:
- asm-5.0.3.jar
- guava-16.0.1.jar
- jackson-annotations-2.6.0.jar
- jackson-core-2.6.3.jar
- jackson-databind-2.6.3.jar
- joda-time-2.9.1.jar
- metrics-core-3.1.2.jar
- snappy-java-1.1.2.6.jar
- slf4j-api-1.7.7.jar
- netty-transport-4.0.37.Final.jar
- netty-handler-4.0.37.Final.jar
- netty-common-4.0.37.Final.jar
- netty-codec-4.0.37.Final.jar
- netty-buffer-4.0.37.Final.jar

Examples

This section provides examples of JDBC connection details for different sources. Note that specifics may vary based on operating system or database version.

Teradata

JDBC Driver Jar: terajdbc4_13.jar, tdgssconfig_13.jar
JDBC Driver Class: com.teradata.jdbc.TeraDriver
JDBC URL: jdbc:teradata://<host>:<port>/TMODE=ANSI,SHARSET=UTF8,DBS_PORT=1025

SAP Hana

JDBC Driver Jar: ngdbc.jar
JDBC Driver Class: com.sap.db.jdbc.Driver
JDBC URL: jdbc:sap://<host>:<port>/?currentschema=<your HANA Schema>

Sybase

JDBC Driver Jar: jconn4.jar
JDBC Driver Class: com.sybase.jdbc4.jdbc.SybDriver
JDBC URL: dbc:sybase:Tds:<host>:<port2048>/<database>

JDBC ODBC Bridge

JDBC Driver Jar:
JDBC Driver Class: sun.jdbc.odbc.JdbcOdbcDriver
JDBC URL: jdbc:odbc:ODBCConnectionName

Hive

Varies based on version.

See https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBC for general information on Hive JDBC Drivers for HiveServer2.

JDBC Driver Jar: Varies based on version. Hive is usually provided as part of a larger Hadoop cluster based on products from Cloudera, Hortonworks, or Amazon. You must use the appropriate drivers for your Hive instance.
- Cloudera: The necessary driver and support jars can be acquired from http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-4.html.
- Hortonworks: The necessary driver and support jars can be acquired from https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_dataintegration/content/hive-jdbc-odbc-drivers.html.
- Amazon EMR: The necessary driver and support jars can be acquired from http://docs.aws.amazon.com/ElasticMapReduce/latest/DeveloperGuide/HiveJDBCDriver.html.
JDBC Driver Class: com.cloudera.hive.jdbc4.HS2Driver
JDBC URL: jdbc:hive2://<host>:<port>

Informix

You will need to log into Informix with your IBM account to be able to download the Informix JDBC drivers and upload them into SnapLogic.

Release	Snap Pack Version	Date	Type	Updates
November 2024	main29029	13 Nov 2024	Stable	Updated and certified against the current SnapLogic Platform release.
August 2024	438patches28541	22 Oct 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	18 Sep 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. 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
August 2024	main27765	21 Aug 2024	Stable	Updated and certified against the current Snaplogic Platform release.
May 2024	437patches27416	29 Jul 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	08 May 2024	Stable	Updated and certified against the current SnapLogic Platform release.
February 2024	436patches25244	26 Feb 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	14 Feb 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	06 Feb 2024	Latest	The database connection error message in the Generic Database Account is improved for clarity and readability.
November 2023	main23721	08 Nov 2023	Stable	Updated and certified against the current SnapLogic Platform release.
August 2023	main22460	16 Aug 2023	Stable	Updated and certified against the current SnapLogic Platform release.
May 2023	main21015	10 May 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	09 Feb 2023	Stable	Upgraded with the latest SnapLogic Platform release.
November 2022	main18944	10 Nov 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	16 Sep 2022	Latest	The Generic JDBC Snaps connecting to the DB2 database now take lesser time to execute thereby improving the performance.
August 2022	main17386	11 Aug 2022	Stable	Enhanced the Generic Database account Support dynamic values for Account, Advanced, and URL properties.
4.29 Patch	429patches16588	24 Jun 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	14 May 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	12 Feb 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	13 Nov 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.
4.26	main11181	14 Aug 2021	Stable	Upgraded with the latest SnapLogic Platform release.
4.25 Patch	425patches11008	04 Aug 2021	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	08 May 2021	Stable	Upgraded with the latest SnapLogic Platform release.
4.24 Patch	4.24patches8903	13 Mar 2021	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	main8556	13 Feb 2021	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 Patch	423patches8027	29 Jan 2021	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	21 Dec 2020	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	14 Nov 2020	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	06 Nov 2020	Latest	Fixes the file descriptor error in the Snap Pack by removing logging exception which was causing log file spam.
4.22 Patch	422patches6772	05 Oct 2020	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.22	main6403	12 Sep 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.21 Patch	421patches6272	22 Jan 2020	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 Patch	421patches6144	20 Jul 2020	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	19 May 2020	Latest	Fixes the connection issue in Database Snaps by detecting and closing open connections after the Snap execution ends.
4.21	snapsmrc542	09 May 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.20 Patch	jdbc8783	26 Mar 2020	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.20	snapsmrc535	08 Feb 2020	Stable	Upgraded with the latest SnapLogic Platform release.
4.19 Patch	db/jdbc8405	13 Dec 2019	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.19	snaprsmrc528	14 Nov 2019	Stable	Upgraded with the latest SnapLogic Platform release.
4.18	snapsmrc523	10 Aug 2019	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	ALL7402	11 Jun 2019	Latest	Pushed automatic rebuild of the latest version of each Snap Pack to SnapLogic UAT and Elastic servers.
4.17	snapsmrc515	11 Jun 2019	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 Patch	MULTIPLE7123	16 Feb 2019	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	16 Feb 2019	Stable	Upgraded with the latest SnapLogic Platform release.
4.15	snapsmrc500	15 Dec 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.14	snapsmrc490	11 Aug 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.13 Patch	jdbc5231	30 May 2018	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	24 May 2018	Latest	Added test connection query for SQLMX database in the JDBC Accounts to check if the database connection is valid.
4.13	snapsmrc486	12 May 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.12 Patch	MULTIPLE4744	27 Feb 2018	Latest	Added support to execute multiple queries in a single batch for Snowflake, this patch affects Generic JDBC Execute.
4.12	snapsmrc480	17 Feb 2018	Stable	Upgraded with the latest SnapLogic Platform release.
4.11	snapsmrc465	11 Nov 2017	Stable	Upgraded with the latest SnapLogic Platform release.
4.10	snapsmrc414	12 Aug 2017	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 Patch	jdbc3242	11 Jul 2017	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	06 Jul 2017	Latest	Fixes auto-commit issue for PostgreSQL SELECT query.
4.9.0 Patch	jdbc3133	29 Jun 2017	Latest	Fixed error handling of the limit clause by adding Apache Derby support.
4.9.0 Patch	jdbc3069	01 Jun 2017	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	13 May 2017	Stable	Upgraded with the latest SnapLogic Platform release.
4.8.0 Patch	jdbc2801	03 Apr 2017	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	27 Mar 2017	Latest	Potential fix for JDBC deadlock issue.
4.8	snapsmrc398	11 Feb 2017	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	28 Nov 2016	Latest	Fixed an issue for database Select Snaps regarding Limit rows not supporting an empty string from a pipeline parameter.
4.7	snapsmrc382	23 Nov 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.6	snapsmrc362	13 Aug 2016	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	18 May 2016	Stable	Resolved an issue with JDBC Select failing with a syntax error when special characters were used.
4.5	snapsmrc344	18 May 2016	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	18 Mar 2016	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	13 Feb 2016	Stable	Upgraded with the latest SnapLogic Platform release.
4.3.2	N/A	15 Jan 2016	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.

Url property name	Url property value
IntegratedSecurity	True
AuthenticationScheme	JavaKerberos

Configuring JDBC Accounts