Troubleshooting Generic JDBC Connections

[intapiuser]

This article is meant to provide initial troubleshooting steps for resolving Generic JDBC driver issues when trying to set this up in Sisense.

The JDBC connector allows you to:

• Establish a connection from Sisense to a datasource that may not have a built-in connector available
• Send queries between Sisense and the datasource (importing data, custom import queries)
• Potentially update your connection from ODBC (.NET) to JDBC (Java)

When troubleshooting it is helpful to understand at what point you are running into the connection issue

Some notable points in the workflow are:

On attempting to establish a connection 

On listing the databases to connect to

On listing the schema(s) and tables

Resources:

Connecting to JDBC - Sisense documentation on configuring a Generic JDBC data source

CDATA - Drivers with certified Sisense connectivity

Getting Help:

Please review the topics below in order to troubleshoot the connection. If you are continuing to see issues with connecting the JDBC driver, please create a ticket with our Support Team with the following information:

• Datasource you are trying to connect to
• Zipped up JAR file(s) for the driver
• Connection info (if you are able to share that info)
• Connection string
• What step you are getting stuck on in the Sisense add data workflow
• What you have tried so far to resolve
• Any other datasource-specific info

Topics:

Verify Sisense Set Up

Configure Connection String

Check Logs

Compare Behavior with DBeaver Database Tool

Verify Sisense Set Up

Folder Location

...\ProgramData\Sisense\DataConnectors\jdbcdrivers\

Be sure to save off your JDBC driver into a folder that Sisense can access.
By default we recommend you save off your .jar (and associated files) in the following folder:

If you have downloaded a driver from a resource like CDATA, you can use the folder created by unpacking the files according to their instructions:

...\Program Files\CData\<driver name>\lib

If your driver requests you use a particular folder path that is ok as long as you reference the path in this parameter in the connection: "JDBC JARs Folder"

Using the JDBC Connection

The ability to use Generic JDBC connections in Sisense is supported in Sisense Web. If you do not see the generic JDBC icon in your list of available connectors in Sisense web, please log into your sever to establish the connection. This instability was resolved on Sisense Version 7.3+.

To set the connection up, please reference this documentation for specific steps: Connecting to JDBC

You will need the following information to set up the connection:

• Connection String
• JDBC JARs Folder
• Driver's Class Name
• Username
• Password

Configure Connection String

Each JDBC driver will have specific information that is required to be passed to make a connection to the database. Typically there will be information such as a URL and target server IP or host name. Beyond these pieces of information, each driver will require varying parameters.

To formulate the connection string, take a look at the documentation that comes with the driver. The connection string will typically start with "jdbc:" and each parameter will be separated by a semicolon (;)

If you are having trouble establishing a connection, please review the documentation of the driver to see if there are any parameters that you can use to adjust the connection such as:

• Connection Timeout: Sometimes there are parameters that can set the timeout manually
• Logging: Some drivers allow you to designate an additional log file path (such as C:\temp) and verbosity 
• Streaming Method: Some datasources have configuration on which type of streaming is needed to pull data (such as via API)
• Authentication Method: Some sources (such as AWS drivers) allow multiple authentication methods
• Port: Some connections require a port. Be sure the port you are using is open on both the Sisense server and the target datasource server

Check Logs

The JDBC driver writes to the following folder: 

%ProgramData%\Sisense\DataConnectors\JVMContainer\Connectors\GenericJDBC

These logs are helpful to determine at what point in the connection process the driver is failing. Additionally, some of the red text errors you may see when the connection to the datasource fails () are written to this log.

Compare the Behavior with DBeaver Database Tool

If the steps above do not assist in getting you closer to successfully connecting to your datasource through a JDBC connection, we recommend testing the connection with a free database connectivity tool. Any tool that allows JDBC connectivity is sufficient, below are instructions to set this up on DBeaver.

Using this tool will help narrow down if there are connectivity issues regardless of the tool to connect to the database or if the issue is specific to connecting with Sisense.

In a multi-server deployment set up, this tool should installed on the node where builds are being performed.

Steps to Download and Set up the JDBC connection:

1. Download DBeaver Community here
2. Run the installer
3. Open DBeaver and navigate to Database > Driver Manager
   
   
   
4. Click New
   
   
   
5. Enter a Driver Name (you choose the name) and the Class Name (as determined by the driver you are using). The driver type should be "Generic"
   
   
   
6. Then click "Add File" and select the driver file(s) you put in the jdbcdrivers folder (or location you put the JDBC driver). Then click "OK"
   
   
   
7. Once the database driver is set up, then click on Database > New Database Connection
   
   
   
8. Select your driver (in this case "TestConnection") then click "Next >"
   
   
   
9. Enter the connection string in JDBC URL as well as the Username and Password (if applicable). Then click "Finish"
   
   
   
10. On the left-hand side of the window you should now see a new connection. Double click on it to attempt to connect. 
    
    
    
    
11. If the connection is successful (you can connect to the database, preview data in tables, etc) there is troubleshooting that will need to be done specifically between Sisense and the datasource.
    
    If the connection is not successful in one or more of the steps to view tables, this means there is likely additional troubleshooting that is necessary for the connection string/connection/database settings.