ODBC TUTORIAL
ODBC Connectivity to the Infor Data Lake Using the Compass JDBC Driver and OpenAccess SDK
Updated: 02 Jun 2021
Introduction
Prerequisites
- Contact Infor to obtain the Compass JDBC driver (dated Sept/Oct 2020). Please note the location of the infor-compass-jdbc-xxx.jar and *.ionapi security file. The JDBC drivers uses ION API authentication. The *.ionapi file should be in the same directory as the .jar file.
- Download the 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. It is recommended that you select ‘Run As Administrator’ when you launch the installer.
- 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. It is recommended that you select ‘Run As Administrator’ when you launch the installer.
Setup Pass JDBC Sample and OA Service for the Infor DataLake
- Download Pass JDBC sample from Progress
- Extract the contents of the .zip to the following directory:
C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava
This will create:
C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\passjdbc
- Start a Command Prompt as Administrator, set the environment variable CLASSPATH to include oasql.jar library from the OpenAccess installation folder.
- Alternatively, you can go to System Properties -> Environment Variables and set the CLASSPATH as shown below
-
Run a Command Prompt as Administrator and navigate to
C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\passjdbc
- From the passjdbc directory, 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 Server 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 Infor_Datalake 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\jre1.8.0_XXX\bin\server
ServiceJVMClassPath: C:\Infor\ION\infor-compass-jdbc-2020-09.jar;C:\Infor\ION;C:\Program Files\Progress\DataDirect\oaserver81\ip\oajava\oasql.jar;C:\Program Files\Progress\DataDirect\oaserver81\ip;
Note: Replace the JVM ClassPath to the Infor JDBC driver.
- Go to Data Source settings -> Right Click on Data Source Setting -> Select New Data Source. Name it Infor_OA.
- Expand Infor_OA and select IP Parameters. Add the below attributes.
DataSourceIPType: DAMIP
DataSourceIPClass: oajava/passjdbc/dbpassjdbc
DataSourceIPCustomProperties: JDBC_DRIVER=com.infor.idl.jdbc.Driver;JDBC_URL=jdbc:infordatalake://SERVERNAME_TST
Note: Remove the attribute DataSourceIPSchemaPath
- Go to DataSource settings -> Infor_OA -> User Security and change the DataSourceLogonMethod to Anonymous.The .ionapi file should bein the same directory as the .jar file listed above in the ServiceJVMClassPath
- Now right click on the Infor_DataLake service and Start “Infor_DataLake”.
Configure OpenAccess 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. Use the same port used in Step #9.Use the same Service Data Source used in Step #11
- Click on Test Connect to check the configuration. The connection will attempt to authenticate with your .ionapi file and you should be able to make a successful connection.
With Progress OpenAccess SDK, you can now connect to your Infor Data Lake. Feel free to contact us if you have any questions or issues, we will be happy to assist you.