Installing & Configuring OpenLink’s Enterprise Edition (Multi-Tier) Data Access for JDBC Data Sources on Windows

Laying the foundation for connecting any ODBC-compliant application to JDBC-accessible data sources

Our Enterprise Edition (Multi-Tier architecture) Data Access solution builds on our basic Lite Edition (Single-Tier architecture) ODBC Drivers, and addresses the following additional challenges:

• Connecting 32-Bit ODBC compliant applications to JDBC compliant data sources in a 64-bit Java environment
• Connecting 64-Bit ODBC compliant applications to JDBC compliant data sources in a 32-bit Java environment
• Connecting ODBC, JDBC, and/or ADO.NET compliant applications to remote JDBC compliant data sources — that is, to JDBC drivers resident on a different machine/host than the data consuming application
• Enhancing security by providing Attribute-based Access Controls that may be used to constrain access to JDBC data sources
• Supporting varied deployment architectures including Application Server (where a single ODBC-compliant app instance serves many app users) and Client-Server (where many ODBC-compliant app instances, potentially on many different machines, independently connect to a single JDBC data source)

This step-by-step guide will walk you through installation and configuration of our Enterprise Edition ODBC-JDBC Bridge solution for Windows.

Specifically, this guide covers the Application Server usage scenario, i.e., all of the Enterprise Edition (Multi-Tier) components, both client- and server-side, are installed on the same machine, similar to the experience of using our Lite Edition (Single-Tier) drivers.

Once successfully installed, your JDBC Data Sources will be accessible to an ODBC compliant application running on Windows operating systems.

Prerequisites

• A valid license (evaluation or paid) for our Enterprise Edition Data Access for JDBC Data Sources, enabling local ODBC clients. (Licensing may also enable remote ODBC clients, as well as local and/or remote JDBC and/or ADO.NET clients). This may be in a single oplrqb.lic file, or be comprised of two files, oplrqb.lic and jdbc.lic.
• A working JDBC connection to your data source, for which you have both the JDBC driver classname and the JDBC URL. For this tutorial, we’ll connect to a local instance of our own Virtuoso Universal Server.

License Generation

[1] If you do not yet have a license file, visit our License Generator, and fill in the License Selection Form:

[2] Then click on the “Generate License” button which triggers the following:

• Creation of a page that includes links to the ODBC-JDBC Bridge installer archive and associated installation and configuration guides

• Generation and dispatch of an email that includes a link to the License File you just generated.

[3] 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

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

By default, the server component installers (the Request Broker, 64-bit wabrzzzz.msi or 32-bit ntbrzzzz.msi, and the Bridge Agent for JDBC Data Sources, 64-bit waajzzzz.msi or 32-bit ntajzzzz.msi) will set the Request Broker’s Listen port to 5000, and the web-based Admin Assistant’s port to 8000. You can change these ports if desired; if you do so, the steps that follow must also be adjusted to match.

Bridge Agent Configuration

This section is optional, for basic connections to most JDBC data sources. For purposes of this tutorial, you can skip ahead to step #14, Data Source Name (DSN) Configuration.

[5] Make sure to enable pop-ups in your browser; otherwise, the following setup wizard window will not appear.

[6] Access the web-based administrator at http://localhost:8000 and navigate to Server Components Administration → Database Agent Administration → Database Settings By Wizard. When asked to authenticate, use the default username and password (that is, admin and admin), or whatever you set during installation.

[7] Select the appropriate JDBC Bridge Agent for your Java environment. The numbers here refer to the common JDK/JRE versioning; for Java 8, a/k/a JDK/JRE 1.8, you should start with generic_jdbc18.

[8] Add a description/comment for this agent configuration, if desired.

[9] Authentication credentials can be left blank for this tutorial. If set here, these values will over-ride client-provided username and password.

[10] Various options are available in several panes that follow. These are used to improve compatibility with various ODBC applications and JDBC drivers that don’t fully comply with their respective standards. None of these are necessary for this tutorial.

[11] Navigate to the Client-Server Process Mapping & Control pane, and set a max number of Openlink Clients to be accepted by a single instance of the bridge agent. If an additional Client connection is requested and permitted by license and other settings, this will cause an additional instance of the Agent to be launched.

[12] Clicking on save will save the details you’ve set, and tell the Request Broker to reload those settings. Note: Some settings will not take effect until the Request Broker service is stopped and restarted, typically through the Services control panel.

[13] Exit the wizard, and an ODBC DSN can now be configured.

Data Source Name (DSN) Configuration

[14] Open the 32-bit or 64-bit ODBC Administrator corresponding to the bitness of your ODBC application, and click Add to create a new DSN.

[15] For best results, you should pick the non-Unicode driver (“OpenLink Generic ODBC Driver [7.0]”) if you’re working with any single-byte (8-bit) codepage or character set. You must (and should only) pick the Unicode driver (“OpenLink Generic ODBC Driver (Unicode) [7.0]”) if you’re working with a multi-byte (9-bit or larger) codepage or character set.

[16] Name your data source, and enter your Request Broker’s hostname and listen port in the Server text box (default is localhost:5000).

[17] For Domain, choose the Agent Name that corresponds to the Agent configuration made in step 7, above; Jdbc 1.8 for this example). Input your JDBC Driver’s classname in Name, and your JDBC URL where obvious. We’re using the Type 4 JDBC Driver for Virtuoso, classname virtuoso.jdbc4.Driver, and connecting to a local Virtuoso instance, via URL jdbc:virtuoso://localhost:1111/. You can tick the "Connect now to verify" box, and input username and password for the JDBC target now, to check the basic setup when you click the Next > button.

[17] Select any additional attributes required for your connection:

[18] Upon a successful test, the DSN can be used with your ODBC applications.

Connecting an ODBC compliant application to a JDBC data source

[19] Launch an ODBC compliant application, tell it to make an ODBC connection, and select your new DSN. The C++Demo app, cppdemo.exe, bundled with each of our ODBC Drivers is always a good test tool.

[20] Respond to the authentication challenge with credentials for the JDBC data source.

[21] Enter and execute an SQL query.

[22] Assuming no errors in syntax or data access permissions, the query result-set (or solution) will be presented. (Note: C++Demo is a fairly simple tool, and will not display LONG, BINARY, nor various other data types.)

Related

• Free Evaluation License Generation & Installer Archive Download Page
• JDBC-to-ODBC Bridge options — for the Latest Releases of Java, and Beyond
• What are Multi-Tier ODBC-JDBC Bridge Drivers?
• New 32- and 64-Bit ODBC SDK & Runtime for Mac OS X
• OpenLink Data Access Drivers — Secure & High-Performance ODBC, JDBC, ADO.NET, OLE DB Drivers for a wide range of database management systems
• OpenLink Virtuoso — Multi-Model RDBMS & Middleware Platform