Connecting to JDBC

Contents[Hide]

1. Overview

The JDBC generic data provider option lets you connect to data sources that have a JDBC driver. Since there are third-party drivers available for a wide range of databases and other data sources, this means that Dundas BI has the ability to connect to more data sources out-of-the-box than we can list. You can often use an existing JDBC driver rather than rely on a custom data provider.

Important
Only the 64-bit versions of the JDBC drivers are supported.

Here are some examples of data sources that have a JDBC driver:

  • Amazon Athena (Amazon S3)
  • Apache Phoenix
  • Hive
  • Impala
  • H2
  • Confluence

There are some limitations to be aware of when connecting via JDBC. See Dundas BI - Product Notes for more details.

Note
Dundas does not guarantee any specific third-party JDBC driver will work properly with Dundas BI. If you encounter any connection issues with a specific driver, contact Dundas Support and we'll do our best to help you investigate the problem.

2. Install

When you or your organization have your own Dundas BI installation, you can install whichever drivers you like on the computer(s) where Dundas BI is installed.

If Dundas BI is running on Windows, the JDBC data connector requires a 64-bit version of the Java SE Runtime Environment version 8 or above to be installed.

Download and install the 64-bit version of Java SE Runtime Environment version 8 or above
Download and install the 64-bit version of Java SE Runtime Environment version 8 or above

In Dundas BI when logged on as an administrator, indicate the location where Dundas BI will be able to locate your JDBC driver files in the Path To JDBC Drivers configuration setting.

Dundas BI needs read access to the files. On Windows, Dundas BI runs as the Network Service identity unless you chose a different option for the application pool when installing. On Linux, Dundas BI runs as the dundasbi user/group.

Indicate the path to JDBC driver libraries
Indicate the path to JDBC driver libraries

Note
Dundas BI's services must be restarted after changing this setting or the files. For an installed Dundas BI instance, see instructions for Windows and Linux.

3. Connect

With a JDBC driver installed and configured, you can create a data connector in Dundas BI that uses the driver.

From the main menu, click New, and then select Data Connector.

In the New Data Connector dialog, set a Name, and then set Data Provider to JDBC.

Create a new data connector for JDBC
Create a new data connector for JDBC

Next, supply the required credentials:

  1. Enter the JDBC URL. This will be different for every database and depends on the JDBC driver. For example, the JDBC URL for Amazon Athena is jdbc:awsathena://athena.us-east-1.amazonaws.com:443.
  2. Enter the User Name for your database account.
  3. Enter the Password for your database account.

If needed, click to expand the Advanced section to configure settings such as the following.

3.1. Driver properties

Some connection properties cannot be included directly in the JDBC URL and have to be provided separately in the Driver Properties field. Similar to the URL, the list of properties and their format are different for each database and are typically documented by the JDBC driver provider.

For example, as documented by Amazon, the Amazon Athena JDBC driver can take parameters as part of the JDBC URL:

jdbc:awsathena://athena.us-west-1.amazonaws.com:443?max_error_retries=20&connection_timeout=20000

But also requires the s3_staging_dir to indicate a location to which the query output is written:

s3_staging_dir=s3://aws-athena-query-results-154861344432-us-east-1

Provide driver properties that cannot be included in the URL
Provide driver properties that cannot be included in the URL

3.2. Driver class name

Older JDBC drivers may require a Driver Class Name to enable the classes in the JAR file. For example, the main JDBC driver class for Amazon Athena is com.amazonaws.athena.jdbc.AthenaDriver.

3.3. Use PreparedStatement

Certain non-JDBC compliant drivers may produce an error in certain cases, which can be avoided by unchecking the Use PrepareStatement advanced option.

4. Java VM path

Some JDBC drivers require additional binary libraries (examples include the DB2 and SQL Server databases when using Windows Authentication).

To indicate the location of such libraries, click Admin from the main menu when logged in as an administrator, click to expand Setup and select Config to navigate to configuration settings.

Choose the option to show advanced settings, find the setting JVM Library Path and edit it to indicate the folder path.

Indicate the path to libraries that are used by the JDBC drivers
Indicate the path to libraries that are used by the JDBC drivers

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri