Installing & Using the OpenLink Lite Edition ODBC–to–JDBC Bridge Driver, on Windows

Connecting ODBC Applications to Third-party JDBC Drivers

The steps below will guide you through installation and configuration of our Lite Edition (Single-Tier) ODBC-JDBC Bridge Drivers for Windows. Once successfully installed, the JDBC Data Sources on your Windows-based PC will be accessible to any ODBC compliant application running there.

License Generation

Visit our License Generator Page, and fill in the License Selection Form as shown:

ODBC-JDBC Bridge Driver Selection

Note: You must choose the Driver Operating System which matches the bitness of your ODBC client application. That is, for a 32-bit application, choose Windows 98/XP/Vista/7/8/10/20xx (32 Bit) (x86), even if it’s running on 64-bit Windows. Only choose Windows XP/Vista/7/8/10/20xx (64 Bit) (x86_64) if your ODBC application is 64-bit. For more details, see "32-bit and 64-bit issues," below.

Then click on the “Generate License” button to trigger:

  • Creation of page that includes links to the ODBC-JDBC Bridge installer archive and associated installation and configuration guides
Page provided by license generator that references installer program and associated guides
  • Generation and dispatch of an email that includes a link to the License File you just generated

Once you’ve received this email, please locate your license file and save it to your Windows computer, prior to running the installer program.

Software Installation

Download the installer package, double-click on its icon, and follow the interaction prompts as and when presented by the installer.

Note: The prompt for the location of your ODBC-JDBC Driver License File is the most important step in this process. Forgetting to install the License File will adversely affect usage of these drivers, and introduce significant complexity to an otherwise very simple process. Please note that the installer asks you to select the folder that holds the license file, not the license file itself.

Installation Verification

To confirm that your driver installation was successful, launch the appropriate ODBC Administrator application, and click to the Drivers tab. This presents you with a list of installed drivers.

Now that installation of your driver has been confirmed you can proceed to create and test an ODBC Data Source Name (DSN) with the ODBC-JDBC Bridge Driver.

Confirmation of Prerequisite JDBC Connectivity

To successfully use an ODBC-JDBC Bridge driver, your target JDBC Driver must be properly installed and functioning. You can use our JDBC demo apps (installed with our JDBC Drivers) to simplify this process.

First, locate our sample applications. By default, these will be found at C:\Program Files\OpenLink Software\UDA\Samples or C:\Program Files (x86)\OpenLink Software\UDA\Samples. Then drill down to the folder that matches your current Java installation (JDBC\jdk1.8\ holds the latest for Java 8.x).

Assuming Java 8.x, go to the folder C:\Program Files\OpenLink Software\UDA\Samples\JDBC\jdk1.8\JDBCDemo, and then open JDBCDemo.bat.

At this point you have a simple JDBC application instantiated for testing with your JDBC Driver. Here are some screenshots that guide you through the process of establishing a connection and executing a simple SQL query using the Athena JDBC Driver provided by Amazon with regards to Amazon AWS hosted instances.

Initial Application Screen with a default JDBC Connection URL in place

Change the default JDBC connection URL to what’s required by your JDBC Driver.

JDBC URL for our Virtuoso JDBC Driver

Enter a SQL Query, in this case targeting the Northwind Demo DB schema.

Scroll through your query results (which is also a simple test of Scrollable Cursor functionality)

Data Source Name (DSN) Configuration

Pick the non-Unicode driver if you’re working with any single-byte, 8-bit codepage or character set. Pick the Unicode driver only if you’re working with a multi-byte codepage or character set.

Provide a name that identifies your DSN. You can also include an optional description, but most tools will not show this to you when asking you to pick the DSN to which they’ll connect.

Type your JDBC driver’s classname (e.g., virtuoso.jdbc4.Driver, openlink.jdbc4.Driver) into the JDBC Driver text box. If you already know your JDBC URL, you can type or paste it into the URL String text box. Alternatively, you can click “…” button to bring up the JDBC connection string builder.

The protocol part of your URL will start with “jdbc:” which identifies JDBC as the main protocol followed by a sub-protocol scheme identifier specific to each driver. Generally, URL syntax follow the basic form “jdbc:{driver-name}://{hostname}[:{port}]/”. Additional database server connection attributes and values may be added one at a time through the “Add” button.

Note that you need to terminate your URL prefix and attribute=value pairs using termination characters supported by the driver in question. For instance, “/” terminates the prefix and “;” for the connection attributes when using our drivers. Thus, a JDBC URL that included UID and PWD conneciton attributes would be represented as: jdbc:virtuoso://example.com:1111/UID=dba;PWD=xyz;

Dialog that contains a JDBC URL that includes UID and PWD connection attributes generated by the URL Builder

Various options are available, to increase compatibility with various ODBC applications and JDBC drivers that don’t fully comply with their respective standards.

Now that you have successfully configured an ODBC Data Source Name for your target JDBC Data Source, proceed to testing connections from your preferred ODBC compliant applications.

Connecting To Data Sources Using ODBC Compliant Applications

Here are the steps to following if using the simple ODBC demo app (iODBC Demo) that we bundle with all our Windows Driver installations (UDA and/or Virtuoso):

Start the C++ Demo App go to the “Environment” (an ODBC environment handle) menu which presents an option to “Open Connection” (an ODBC connection handle).

Click on Environment -> Open Connection to open the ODBC DSN selection interface.

Initializing ODBC Data Source Selector UI (part of ODBC Administrator)

[10] Select your newly created ODBC DSN for accessing your SQL Table and click OK. Use the same credentials as you tested when creating the DSN, and click connect.

Data Source Name Selection

[11] The selection interface will disappear upon a successful ODBC connection. Navigate to SQL -> Execute SQL, and enter a query just as you would in a JDBC application such as SQL Workbench J, and Click OK.

SQL Query Entry in C++ Demo

[12] Results will be populated in a table within the C++ demo interface.

Query Results
  • You can repeat the same exercise with any other ODBC compliant application, such as Excel, Tableau, QlikView, etc.

Troubleshooting

License File Location Issues

If you failed to point the installer program to your JDBC-ODBC Driver’s license file, or are upgrading from a Free Evaluation License to a Perpetual License, you will encounter the following error dialog:

[OpenLink][ODBC] License not found

This problem is resolved by copying your license from the folder into which it was downloaded over to folder named: \Program Files\OpenLink Software\UDA\bin\.

Once the license is in place you will need to restart the license manager by performing the following steps:

  1. Opening the Windows service manager
  2. Locate and double click on OpenLink License Manager
  3. Stop and Restart the OpenLink License Manager Service

Note: You can manually ensure the License Manager is directed to directory where your License Files are situated by adding: +directory <directory-name> to the License Manager Service properties.

32-bit and 64-bit issues

The driving factor in most cases is the ODBC client application.

  • 32-bit ODBC client applications require a 32-bit ODBC driver.
  • 64-bit ODBC client applications require a 64-bit ODBC driver.

If you can install a Java environment (JVM/JRE/JDK) which matches the bitness of your ODBC client application, you can use our Lite Edition (Single-Tier) ODBC-JDBC Bridge Driver, again matching the same bitness. In this scenario, the ODBC client application, the Lite Edition ODBC-JDBC Bridge Driver, and the Java environment would either all be 32-bit, or all be 64-bit.

If you cannot install a Java environment (JVM/JRE/JDK) which matches the bitness of your ODBC client application — i.e., if you have a 32-bit ODBC client application and must have 64-bit Java, or a 64-bit ODBC client application and must have 32-bit Java — you can use our Enterprise Edition (Multi-Tier) Data Access solution. This solution also lets ODBC client applications on one machine connect to JDBC data sources on another machine. In this scenario, you would install the Enterprise Edition Generic Client ODBC driver which matches the bitness of your ODBC application, and the Enterprise Edition Request Broker and Bridge Agent that match the bitness of your Java environment.

Related