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.

[Progress]
Description = Progress Driver
Driver      = /usr/dlc/odbc/lib/pgoe27.so
FileUsage   = 1?

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.

[Sports]
Driver=Progress
DatabaseName=sports
DMEnvAttr=SQL_ATTR_UNIXODBC_ENVATTR={LD_LIBRARY_PATH=/usr/dlc/odbc/lib:/usr/dlc/lib}??
Hostname=192.168.86.161
PortNumber=10000
LogonID=sysprogress
Password=mypassword
APILevel=1
ConnectFunctions=YYN
CPTimeout=60
SQLLevel=0
FileUsage=0
UsageCount=1
ArraySize=50
DefaultLongDataBuffLen=2048
DefaultIsolationLevel=READ COMMITTED
StaticCursorLongColBuffLen=4096
IANAAppCodePage=ISO_8859_1
DefaultSchema=pub

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.

<?php

putenv('ODBCINI=/etc/odbc.ini');
putenv('ODBCINST=/etc/odbcinst.ini');
putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib');

$sql = 'select name from pub.customer fetch first 10 rows only';

$db = new PDO('odbc:Sports', 'sysprogress','mypassword');
$stmt = $db->prepare($sql);
$stmt->execute();

// retrieve results in a variable, loop through results and display
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
	echo "{$row['Name']}\n";
}

?>

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.

<?php

putenv('ODBCINI=/etc/odbc.ini');
putenv('ODBCINST=/etc/odbcinst.ini');
putenv('LD_LIBRARY_PATH=/usr/dlc/odbc:/usr/dlc/odbc/lib');

$parameters = array(
	'Driver' => 'Progress',
	'DatabaseName' => 'sports',
	'Hostname' => '192.168.86.161',
	'PortNumber' => 10000,
	'IANAAppCodePage' => 'ISO_8859_1',
);
$connectionString = http_build_query($parameters, '', ';');

$sql = 'select name from pub.customer fetch first 10 rows only';

$db = new PDO('odbc:' . $connectionString, 'sysprogress','mypassword');
$stmt = $db->prepare($sql);
$stmt->execute();

// retrieve results in a variable, loop through results and display
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($result as $row) {
	echo "{$row['Name']}\n";
}

?>

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.

# install ruby and gem dependencies
sudo apt-get -y install ruby ruby-dev make

# install gem related to DBI, which we may not use
sudo gem install dbi
sudo gem install dbd-odbc

# install gem related to sequel, which we WILL use
sudo gem install ruby-odbc
sudo gem install sequel

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

require 'sequel'

ENV['ODBCINST'] = '/etc/odbcinst.ini'
ENV['ODBCINI'] = '/etc/odbc.ini'
ENV['LD_LIBRARY_PATH'] = '/usr/dlc/odbc:/usr/dlc/odbc/lib'

db = Sequel.odbc('Sports', :user=>'sysprogress', :password=>'mypassword')
sql = 'select name from pub.customer fetch first 10 rows only'

db.fetch(sql) do |row|
    puts "#{row[:name]}"
end

db.disconnect

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.

# install ruby and gem dependencies
sudo apt-get -y install python3-pip

# install module
sudo -H pip3 install pypyodbc

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

import os
import pypyodbc as pyodbc

os.environ['ODBCINI'] = '/etc/odbc.ini'
os.environ['ODBCINST'] = '/etc/odbcinst.ini'
os.environ['LD_LIBRARY_PATH'] = '/usr/dlc/odbc'

sql = 'select name from pub.customer fetch first 10 rows only'
db = pyodbc.connect('DSN=Sports;UID=sysprogress;PWD=mypassword')
result = db.cursor()
result.execute(sql)

for row in result.fetchall():
	print(row[0])

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:

import java.sql.*;

public class dsnless
{
    public static void main (String args[])
    {
        try
        {
            Class.forName ("com.ddtek.jdbc.openedge.OpenEdgeDriver");
            Connection db = DriverManager.getConnection("jdbc:datadirect:openedge://192.168.86.161:10000;databaseName=sports;defaultSchema=pub", "sysprogress", "mypassword");
            
            String sql = "select name from pub.customer fetch first 10 rows only";

            Statement stmt = db.createStatement();
	    ResultSet result = stmt.executeQuery(sql);

	    while (result.next())
	    {
		System.out.println(result.getString("Name"));
	    }

            db.close();
        }
        catch (Exception x) 
        {
            x.printStackTrace();
        }
    }
}

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.