Connect to Infor Compass using JDBC Driver in ODBC Apps (e.g. SQL Server, Power BI, Excel, Informatica)

Introduction

In this blog post you will learn how to use Infor Compass JDBC driver in ODBC Apps (i.e. non-JAVA apps) such as Power BI, Excel, Informatica and many more listed here using JDBC-ODBC Bridge Driver. In other words, if you like to use Infor Compass data in your App / Programming language and App doesnt support using JDBC Driver Infor Provides then this article is for you. ZappySys has created a Bridge Driver which can translate ODBC calls to JDBC making it possible to call any JDBC driver in non Java Apps (i.e. ODBC Apps) written in languages like C++ / C#  / Python etc.

So lets get started.

Download Infor Compass JDBC Driver and ionapi file

Check below links to download and configure Infor Compass JDBC.

1. Download Infor Compass JDBC Driver (*.jar file)
2. Extract Driver files to local folder
3. Download and Configure *.ionapi file – Infor Compass JDBC Driver.ionapi file (Placed in same directory where JDBC driver is saved)

Setup JRE (Java Runtime)

Before we start using ZappySys JDBC Bridge Driver, we need to install JAVA Runtime (JRE) or Java Development Kit (JDK).

We recommend installing JRE8 (other JRE version(s) might work too). There are several ways to get JRE8 installer, but we recommend using any of the following ways. Skip below steps if you have JAVA Runtime already installed.

1. JRE for OpenJDK (FREE – Recommended). Download JRE-8 for OpenJDK (Its maintained by Amazon AWS)
2. JRE for Oracle JAVA (For cost check Oracle Licensing) Download JRE8 for Oracle JAVA

Download Infor JDBC Driver / Generate ionapi Credentials File

To access infor Data using their official JDBC Driver you will need to do 2 things

1. Download Infor JDBC Driver locally
2. Create ionapi file locally

Read this link for more information

Here is the video Tutorial

Create ODBC DSN for Infor Compass JDBC Driver

In this section, you will learn how to Configure ODBC DSN. Later on, this DSN can be used to use ZappySys Driver. ODBC DSN can be stored at Machine Level (for all users) or the Current User Level. In this case, we are going to load data into Microsoft Excel from PostgreSQL Storage using ZappySys JDBC Bridge ODBC Driver.

1. Type odbcad32.exe in your search box and launch the DSN Config utility.
2. If you want access for yourself then stay on User DSN Tab. If you want to grant access to other users, then go to the System DSN tab. For SQL Server Integration go to the System Tab and add a new System DSN rather than User DSN. Click the New button.
   
3. From the Driver list Select ZappySys ODBC Driver. For this example, select [ZappySys JDBC Bridge Driver].
   

Configure ODBC DSN for Infor Compass Connectivity

Once you downloaded / extracted JDBC (*.jar) and *.ionapi files and saved them to the local disk you can configure ZappySys JDBC Bridge Driver like the below

1. Create a folder e.g. C:\Infor_Datalake
2. Extract jdbc driver files in that folder, keep ionapi file in the same folder too (v1.7 will look ionapi file under this folder)
3. Make sure do not rename ionapi file. It must be named as “Infor Compass JDBC Driver.ionapi”
   
   If you are getting this error (Infor Compass JDBC Driver.ionapi file is not found) then we suggest you copy ionapi file in the below locations too. If you are not planning to use Data Gateway / Linked Server then you can skip ionapi file copy to 3rd location (i.e. ZappySys.TdsServer.WindowsService folder).
   

   C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\JdbcBridgeDriverX64 C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\JdbcBridgeDriverX86 C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\ZappySys.TdsServer.WindowsService

   1 2 3

   C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\JdbcBridgeDriverX64 C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\JdbcBridgeDriverX86 C:\Program Files (x86)\ZappySys\ZappySys ODBC PowerPack\ZappySys.TdsServer.WindowsService

4. Open ODBC DataSource UI by double clicking the DSN (you created in the previous section).
5. Enter JDBC Connection String as per this format
   
   jdbc:infordatalake://TENANTNNAME

   1	jdbc:infordatalake://TENANTNNAME

6. Enter Driver class (Optional).
   
   com.infor.idl.jdbc.Driver

   1	com.infor.idl.jdbc.Driver

7. Enter User ID and Password is optional so keep it blank unless required by driver. Click Test Connection
   

   Using Infor Compass JDBC Driver in ODBC Apps via ZappySys JDBC Bridge Driver

8. Now you can go to Preview Tab and select Table from the dropdown and write query to preview data

Video Tutorial – ODBC-JDBC Bridge Driver

Connect Infor Compass Datalake in SQL Server (Linked Server)

So in our previous section, we saw how to connect using ODBC Driver but if you like to access Infor Datalake data in SQL Server then you have to use the Data gateway approach. Click here to learn more.

In Data Gateway Driver UI is pretty much the same as ODBC UI except few differences. You also need to configure the Linked server which requires extra steps. The above link is for a generic JDBC Driver but follow Infor Datalake JDBC-specific steps mentioned in the ODBC setup section in this article.

Troubleshooting Known Errors

Here in this section, we will discuss some known errors and its workaround.

The Infor Compass JDBC Driver.ionapi file is not found

If you get this error you can review this solution

Data Lake is not available /  Unable to verify trust for server certificate chain

If you get the following error then check this link for possible workarounds

Using Infor Compass ODBC-JDBC connection in various apps (e.g. Power BI, Excel, Informatica, SQL Server)

Once you create ODBC DSN you are ready to test that in any ODBC-compliant apps listed here

If your app is not listed in the above link, then no worries just find the Option to connect to ODBC in your App and use DSN we created in an earlier section.

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.  

BI / Reporting Tools Integration	ETL Tools Integration	Programming Languages Integration
Microsoft Power BI Tableau SSRS (SQL Reporting Services) QlikView /Qlik Sense MicroStrategy Google Sheet Microsoft Excel Microsoft Access Oracle OBIEE Many more (not in this list).....	Informatica PowerCenter (Windows) Informatica Cloud SSIS (SQL Integration Services) SQL Server Talend Data Studio Pentaho Kettle Oracle OBIEE Many more (not in this list).....	Visual Studio C# C++ JAVA Python PHP PowerShell T-SQL (Using Linked Server)

 

Conclusion

In this post we saw how easy it is to use the Infor Compass JDBC driver in any non-JAVA app in a few steps. Click here to download ZappySys JDBC Bridge Driver.