ODBC-JDBC Bridge: Quick and Easy ODBC Wrapper for a JDBC Data Source

Introduction

ODBC and JDBC are the most popular SQL standards adopted by developers to access data and run queries. Using these standards allows applications to send SQL statements to different data sources without having to code multiple times. While JDBC was written for the Java environment, ODBC was designed to be language-agnostic. That's one of the major reasons, you see a lot more applications support ODBC than JDBC.

Developers often get into a dilemma when an application has been designed to consume ODBC drivers, whereas the database supports only JDBC. Recreating an ODBC layer at the DB level or a JDBC support at the application may not be in your control or even cost-effective to build and maintain in the long run. In such cases, we recommend that enterprises deploy an ODBC-JDBC bridge (a.k.a an ODBC wrapper) to make the JDBC compliant database look like an ODBC data source.

OpenAccess SDK is our SQL engine that makes it very easy to build an ODBC wrapper on top of any API. With almost 95% of the coding already taken care of, wrapping a JDBC driver (or for that matter any API) with an ODBC layer can be done in days if not hours. And the best part of OpenAccess is that queries could be passed through as long as the underlying API can handle a particular query. In other cases, OpenAccess can deconstruct the query and reconstruct the results to support additional ODBC features. In this tutorial, we have wrapped a Snowflake JDBC driver with ODBC and accessed it from OBIEE. OpenAccess can be your ODBC-REST bridge, ODBC-SOAP bridge or an ODBC-ODBC wrapper (when the existing ODBC driver is not robust enough) based on your enterprises need.

Pre-requisites

Have a Snowflake Instance running
Download the Snowflake JDBC driver from Snowflake website

Install Progress OpenAccess SDK and ODBC client

Download Progress DataDirect OpenAccess Server SDK 8.1 and ODBC client for OpenAccess from the website
Install the OpenAccess Server SDK 8.1 using the installer. Keep the options or values in installer to default and complete the installation.
Once you are done with installing the OpenAccess Server, you need to install ODBC client for OpenAccess – for you to be able to get ODBC access using your JDBC driver.

Configure Pass JDBC Service

Download Pass JDBC sample from Progress GitHub account.
Copy the passjdbc folder in the above repository to the path

C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava
Now set the environment variable CLASSPATH to include oasql.jar library from OpenAccess install folder. It will be present at the below path

C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\oasql.jar
Alternatively, you can go to System Properties -> Environment Variables and set the CLASSPATH as shown below
Run Command Prompt as Administrator and navigate to

C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\passjdbc
In the passjdbc folder, run the below command to compile the passjdbc ip code.

javac *.java
Now, open Management Console from the start menu. You can find this under Progress DataDirect OpenAccess folder in start menu.
In the Management Console, connect to OpenAccess SDK Agent and under the services, create a new Java Service. To do that Right click on Services folder -> New -> Service.
Click Next and now enter the service name and any TCP port that you would want the service to run on.
Click on Next and this will create the new service. Now open the Snowflake service you have just created, go to Configuration -> Service Settings -> IP Parameters and add the below attributes

ServiceIPModule: oadamipjava.dll

ServiceJVMLocation: C:\Program Files\Java\jdk1.8.0_XXX\jre\bin\server

ServiceJVMClassPath: C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\oasql.jar;C:\Program Files\Progress\DataDirect\oaserver81\ip; C:\<your-path-to> \snowflake-jdbc.jar

Note: Replace the JVM path and path to Snowflake JDBC driver appropriate to your local environment.
Go to Datasource settings -> Default -> IP Parameters and add the below attributes.

DataSourceIPType: DAMIP
DataSourceIPClass: oajava/passjdbc/dbpassjdbc
DataSourceIPCustomProperties: JDBC_DRIVER=net.snowflake.client.jdbc.SnowflakeDriver;JDBC_URL=jdbc:snowflake://<snow-flake-endpoint>

Note: Remove the attribute DataSourceIPSchemaPath
Go to DataSource settings -> Default -> User Security and change the DataSourceLogonMethod to DBMSLogon(UID,PWD)
Now right click on Snowflake service and Start “Snowflake”.

Configure Snowflake ODBC DSN

Open ODBC administrator, go to System DSN tab and click on Add to add a new configuration.
Choose Progress DataDirect OpenAccess SDK 8.1 as your driver and click on finish.
Fill out the details as below. If you have used a different port when creating the service, use that port.
Click on Test Connect to check the configuration. This will prompt username and password, provide your credentials for your snowflake account and you should be able to connect to it.

Use Snowflake driver with OBIEE, Informatica and others

In the recent past, we have seen several customers having trouble to connect their Snowflake JDBC and ODBC drivers with OBIEE, Informatica, SAS and other popular applications. This is due to the DataDirect Driver Manager embedded by these applications that expect a supported ODBC driver. In this case, wrapping their JDBC or ODBC drivers with DataDirect's OpenAccess driver will make them compliant with these applications once again. Please follow the steps below to use your ODBC wrapped Snowflake JDBC (or ODBC) driver along with OBIEE:

Now you are all set up to use the snowflake driver with your favorite BI and ETL tools like OBIEEE, Informatica, SAS etc.,
For example in OBIEEE, you should now be able to connect to Snowflake as shown below