Skip to end of banner
Go to start of banner

Configuring JDBC Accounts

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 42 Next »

On this Page

Overview

You can create a Generic JDBC Account to connect to data sources that you want to use in your Pipelines. You can configure your Generic JDBC account in SnapLogic using either the Designer or the Manager.

Snap-Account Compatibility

Snap

Generic JDBC Account

Generic JDBC - Execute

✔

Generic JDBC - Insert

✔

Generic JDBC - Select

✔

Generic JDBC - Update

✔


Configuring Generic JDBC Account Using SnapLogic Designer

Drag a Generic JDBC Snap to the Canvas and click the Snap to open its settings. Click the Account tab. You can now either use an existing account or create a new one.

Selecting an existing account

SnapLogic organizes and displays all accounts to which you have access, sorting them by account type and location. To select an existing account:

  1. In the Account tab, click the Expand(blue star)  icon to view the accounts to which you have access, and select the account that you want to use.

  2. Click the Save (blue star) icon.

Creating an account

  1. In the Account tab, click Add Account below the Account Reference field.

  2. Select the Location in which you want to create the account, select the Generic JDBC Account Type, and click Continue. The Add Account dialog window associated with the account type is displayed.

  3. Enter the required account details. Learn more on how to provide information for the account type in Generic JDBC account.

  4. Click Validate to verify the account, if the account type supports validation.

  5. Click Apply to complete configuring the Generic JDBC account.

Enter additional information on this account in the Notes field of the Info tab. This will help you–and other users–understand the purpose of the account, especially if there are multiple accounts of the same type.

Configuring Generic JDBC Account Using SnapLogic Manager

You can use Manager to create accounts without associating them immediately with Pipelines.

Accounts in SnapLogic are associated with projects. You can use accounts created in other projects only if you have at least Read access to them.

  1. In the left pane, browse to the project in which you want to create the account and click (blue star) > Account > JDBC > Generic JDBC Account, followed by the appropriate account type. The Create Account dialog associated with the selected account type is displayed.

  2. Repeat steps 3 through 5 in the Creating an account section. 

Avoid updating account credentials while Pipelines using that account are executing. Doing so may lead to unexpected results, including your account getting locked.

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 fields are encrypted for each sensitivity level selected for this account.

  • High: Password

  • Medium + High: Username, password

  • Low + Medium + High: Username, password, JDBC URL

Active Directory authentication

SnapLogic supports Active Directory authentication for SQL server for mssql-jdbc-6.2.2-jre8.jar driver. Ensure that you have installed the 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:

    • Url property name and Url property value:
      IntegratedSecurity | True
      AuthenticationScheme | JavaKerberos

    • Enter your Active Directory Username and Password.

Connecting to Athena Database Using Athena JDBC Driver

Athena JDBC Driver 1.x versions are deprecated, and the Generic JDBC Snaps are not compatible with them. Therefore, we recommend you to use Athena JDBC Driver 2.x or later for the AWS Athena database when using Generic JDBC Snaps.

Connecting to Cassandra Database Using Cassandra Simba Driver

To connect to the 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. Learn more about the JAR version to be used: JDBC Install Guide.

Besides the JAR files, configure the following settings 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

Typical JDBC Connection Properties for Various Databases

SAP HANA

To download SAP HANA you must have an SAP ID and 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 a zip file. First you need to unzip this file and locate the jconn4.jar file which usually is stored in the classes directory.

You can download the Sybase JDBC Driver from here: 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

Examples

This section provides examples of JDBC connection details for different sources. Note that specifics may vary based on the Operating System or Database version.

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.

Informix

Log into Informix with your IBM account to be able to download the Informix JDBC drivers and upload them into SnapLogic.

  • JDBC jars: shared/ifxjdbc.jar

  • JDBC Driver Class: com.informix.jdbc.IfxDriver

  • JDBC URL: jdbc:informix-sqll://cxdn-rpt.cx.claremont.edu:1550/hmudd:INFORMIXSERVER=cxdbreplica

JDBC ODBC Bridge

  • JDBC Driver Class: sun.jdbc.odbc.JdbcOdbcDriver

  • JDBC URL: jdbc:odbc:ODBCConnectionName

Oracle has stopped support for JDBC-ODBC Bridge from Java 8 and recommends using JDBC drivers provided by the vendor for database instead of the JDBC-ODBC Bridge. However, if the database vendor supports the JDBC ODBC bridge, then you can use it with the SnapLogic JDBC Snap Pack, but SnapLogic does not certify any JDBC ODBC bridge connections.

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> 

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

Troubleshooting

  • If you have several JDBC accounts, one for system A and another for system B, then you must ensure that all drivers can be loaded. If driver A fails to load, then driver B will always fail, even if it is a valid driver, as this is the expected behavior of the JDBC driver registry. It loads all drivers and goes through them in sequence. If any driver fails in the process, all drivers loaded later will also fail. Once you fix the driver for the failing account, you must restart the JCC needs.

  • 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.

  

Release 

Snap Pack Version

Date

Type

  Updates

November 2024

main29029

Stable

Updated and certified against the current SnapLogic Platform release.

August 2024

438patches28541

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

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

Stable

Updated and certified against the current Snaplogic Platform release.

May 2024

437patches27416

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

Stable

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:

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.

4.27

main12833

 

Stable

4.26 

main11181

 

Stable

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

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 In Schema. If the Output Fields property is empty all the columns are visible.

4.23 Patch

423patches8027

 

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.23

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

422patches6772

 

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

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.21 Patch

421patches6272

 

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

 

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

 

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 database.

4.20

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 Update condition property.

  • Input data contain the character '?'.

4.19

snaprsmrc528

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.18

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

ALL7402

 

Latest

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

4.17

snapsmrc515

 

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

 

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

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.15

snapsmrc500

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.14

snapsmrc490

 

Stable

Upgraded with the latest SnapLogic Platform release.

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

 

Latest

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

4.13

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

 

Stable

Upgraded with the latest SnapLogic Platform release.

4.11

snapsmrc465

 

Stable

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

jdbc3133

 

Latest

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

4.9.0 Patch

jdbc3069

 

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

 

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

 

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

  • No labels

Have feedback? Email documentation@snaplogic.com | Ask a question in the SnapLogic Community
© 2017-2024 SnapLogic, Inc.