Set up ODBC connection to OpenEdge

Files to set up

Setting up of ODBC connection to OpenEdge requires /etc/odbcinst.ini to be update with the right drivers. Once that is done, OpenEdge can be accessed by either using a DSN or DSN-less connection string.

It is assumed that you have set up and tested your SQL ODBC connection drivers. If not, Installing OpenEdge SQL Client Access ODBC drivers on Ubuntu 16.04 topic will be of help.

Drivers in /etc/odbcinst.ini

Edit /etc/odbcinst.ini file with sudo rights and enter the following information in that file.

This makes Progress OpenEdge ODBC drivers available.

Set up DSN for OpenEdge

Edit /etc/odbc.ini with sudo rights and enter the following information in that file.

Some parameters are worth noting.

  1. ConnectFunctions – It has 3 characters. First one indicates whether driver supports SQLConnect. The second one indicates whether driver supports SQLDriverConnect and the last one indicates whether driver supports SQLBrowseConnect
  2. DefaultIsolationLevel – READ COMMITTED (equivalent to 1) setting prohibits dirty reads; allows non-repeatable reads and phantoms. See Transaction isolation level: Proper syntax and definition for more details
  3. IANAAppCodePage – if this setting is not included and your strings are not read correctly with your modern programming language, be sure to set it to ISO_8859_1 (or LATIN1) to correctly output English letters
  4. DefaultSchema – if this setting is not included, SYSPROGRESS becomes the default schema. Otherwise, the assigned schema is the default

See more details regarding DSN settings on Explanation of the ODBC data source parameters.

Extract data from OpenEdge using PHP, SQL and ODBC DSN

Let’s create a file called dsn.php and type in the following code. Assuming that you successfully tested your installation already, the following code snippet will work just fine.

Execute this file using php dsn.php

Notice that we are setting up 3 environment variables before accessing the database. Those environment variables are important for the driver. You can set them up as system environment variables and then remove from this script.

Notice that we are using PHP Data Objects (PDO) that nicely abstracts many DB functionalities. Just using odbc:Sports (Sports being our DSN name in /etc/odbc.ini) is sufficient for PDO to establish connection with our database.

Extract data from OpenEdge using PHP, SQL and DSN-less ODBC

Create a file called dsn-less.php and type in the following code.

Execute this file using php dsn-less.php

Make a note that we are creating an associated array with all the parameters and their values. We, then, build a connection string using http_build_query function and pass it to PDO identical to a DSN name.

DSN-less connection provides the benefit of changing information on the fly without disturbing a system file that requires privileged access.

Connect to OpenEdge using Ruby, ODBC and SQL

Ruby has a gem called Sequel that provides excellent database abstraction and many ORM features. Although we aren’t using ORM features, we are using Ruby to extract data from OpenEdge.

You will need some pre-requisites installed. Let’s install them.

Create a file called dsn.rb and type the following code

Execute this file using ruby dsn.rb

Similar to PHP, we create environment variables and then have Sequel and Ruby connect to DSN called Sports.

Connect to OpenEdge using Python3, ODBC and SQL

Python uses pypyodbc to connect to databases. We will use that module. Let’s install it.

Create a file called dsn.py and type the following code

Execute this file using python3 dsn.py

Connect to OpenEdge using Java, JDBC and SQL

Assumption is made that you have Oracle’s Java JDK installed. Pre-requisite for OpenEdge JDBC drivers are already fulfilled by SQL Client Access ODBC driver installation. The JAR of interest is found here: /usr/dlc/java/openedge.jar.

Let’s write dsnless.java like so:

Java does not use ODBC. It uses JDBC type 4 drivers provided by OpenEdge.

Compile this file using javac dsnless.java and then execute the class using java -classpath .:/usr/dlc/java/openedge.jar dsnless and observe the results. You should get the same results as with the other programming languages.

Hope these examples help you get started with accessing OpenEdge via ODBC or JDBC and SQL with one of the languages presented here or a language of your choice.

See Modern web applications and API with OpenEdge, SQL, ODBC and PHP for more topics of interest.