Progress OpenEdge SQL ODBC drivers
You can obtain and install OpenEdge SQL Client Access drivers ODBC drivers (aka SQL ODBC drivers) from Progress’s Electronic Software Delivery (ESD) website. Contact Progress support or your account manager for details. SQL ODBC drivers for OpenEdge are available for Windows and Linux. This article focuses on installing SQL driver on Ubuntu 16.04 GNU/Linux Server and is a continuation of Modern web applications and API with OpenEdge, SQL, ODBC and PHP
Pre-requisites
Install the following software prior to installing SQL ODBC drivers
- unixodbc – to support ODBC access to OpenEdge
- unixodbc-dev
- xterm – to support character based installer (ncurses/curses)
- php 7.1 and php 7.1 odbc – to test connectivity
- OpenEdge SQL Drivers (e.g. PROGRESS_OE_11.7_LNX_64_SQLCLIENTACCESS.tar.gz)
Installing pre-requisites
Let’s install all the necessary pre-requisites before installing Progress OpenEdge SQL drivers for ODBC.
# install unix odbc sudo apt-get -y install unixodbc unixodbc-dev # install xterm sudo apt-get -y install xterm # install php7, odbc and pdo sudo apt-get -y install php7.0 php7.0-odbc php7.0-pdo
Begin Installation of OpenEdge SQL ODBC drivers
We will create a directory called test and unzip contents of SQL Client Access zip file in it.
# create a test directory
mkdir test
# assumption is made that PROGRESS_OE_11.7_LNX_64_SQLCLIENTACCESS.tar.gz is in home directory
# and we are in home directory
mv PROGRESS_OE_11.7_LNX_64_SQLCLIENTACCESS.tar.gz test/
cd test
# extract zip file
tar xzf PROGRESS_OE_11.7_LNX_64_SQLCLIENTACCESS.tar.gz
cd ..
# set terminal to xterm
export TERM=xterm
# from home directory, execute this command
sudo ./test/proinst
# you will see this. Hit Enter
+-------------------------------------------------------------------+
| Welcome |
+-------------------------------------------------------------------+
| |
| WELCOME TO THE OPENEDGE INSTALLATION UTILITY |
| |
| Ensure that you have your completed "Preinstallation Checklist |
| for Unix" handy to consult. This information will facilitate your |
| installation and ensure your choices are accurately recorded. |
| |
| Copyright (c) 1984-2017 Progress Software Corporation |
| and/or one of its subsidiaries or affiliates. |
| All Rights Reserved. |
| |
| |
| [Enter=OK] |
+-------------------------------------------------------------------+At this point, you need to have your serial number and control number ready. You can obtain them from Progress’s ESD website or by asking your account manager.
Enter serial number and control number
+----------------------------------------------------------------------+
| Product Configuration Data |
+----------------------------------------------------------------------+
| [Enter=Additional] |
| Company Name: ______________________________ [Ctrl-E=Done] |
| Serial Number: _________ [CTRL-T=Quit] |
| Control Number: _____ _____ _____ [CTRL-N=Release Notes]|
| [CTRL-V=View] |
| [TAB=Next Field] |
| [CTRL-P=Help] |
| [CTRL-A=Addendum File]|
| |
+----------------------------------------------------------------------+After entering the numbers, hit Enter. If the numbers appear correct, you will see a message that control number has been confirmed and accepted.
Hit CTRL-E to go to the next screen and Hit Y when you see a screen like this:
+-----------------------------------------------------------------------+ | Done Configuration Data Confirmation | +-----------------------------------------------------------------------+ | | |Are you sure that you are done entering all the control numbers for the| |OpenEdge products that will be installed? | | | | [Y=YES] [N=NO] | +-----------------------------------------------------------------------+
You will notice that Continue with Installation will be highlighted by default. Hit Enter on the screen below to install the drivers.
+-------------------------------+
| Install Type and Destination |
+-------------------------------+
| Select Type of Installation |
| Select Destination Pathname |
| Select Management Pathname |
| Continue with Installation*** |
| View Release Notes |
| Cancel |
| Quit Installation |
| Help |
+-------------------------------+
+-----------------------------------------------------------------------------+
|Type: Complete Install |
|Destination pathname: /usr/dlc |
|Working Dir pathname: /usr/wrk |
|Management pathname: /usr/oemgmt |
|Management Working Dir pathname: /usr/wrk_oemgmt |
| |
+-----------------------------------------------------------------------------+Choosing default location for installation
Acknowledge the final step by hitting Y
+-----------------------------------------------------------+
| Complete Installation |
+-----------------------------------------------------------+
| |
|The following products will be installed: |
|'OE SQL Client Access (1 USERS)' |
| |
|Disk Space Required for Products: 357,720,064 bytes |
|Disk Space Required for Installation: 358,969,344 bytes |
|Disk Space Remaining After Installation: 988,233,728 bytes |
| |
|Selected Destination Path: /usr/dlc |
| |
|Do you want to install the above listed product(s)? |
| |
| [Y=YES] [N=NO] [H=Help] |
+-----------------------------------------------------------+Use the default location to install scripts
+-----------------------------+
| Copy Scripts? |
+-----------------------------+
| |
|Copy the scripts to /usr/bin?|
| |
| [Y=YES] [N=NO] [H=Help] |
+-----------------------------+After installation, End the OpenEdge installation will be highlighted by default. Hit Enter.
+---------------------------------------------------------+
|Installation of selected OpenEdge products is complete. |
|Refer to the installation notes for more information. |
+---------------------------------------------------------+
| End the OpenEdge Installation |
| View Release Notes |
| Help |
+---------------------------------------------------------+You have now installed OpenEdge SQL drivers for ODBC. Congratulations!
Confirm correctness of OpenEdge ODBC drivers
Let’s confirm that our drivers have been installed correctly.
cd /usr/dlc/odbc/lib/ ldd pgoe27.so linux-vdso.so.1 => (0x00007ffc4eebf000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f20f1c55000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f20f1a4d000) libpgicu27.so => not found libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f20f1848000) libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f20f14c6000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f20f11bc000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f20f0df2000) libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f20f0bdc000) /lib64/ld-linux-x86-64.so.2 (0x0000559df2bed000)
Notice that libpgicu27.so was not found. Let’s copy this file to the right destination.
sudo cp libpgicu27.so /usr/lib ldd pgoe27.so linux-vdso.so.1 => (0x00007ffe951c7000) libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f132a8d0000) librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f132a6c8000) libpgicu27.so => /usr/lib/libpgicu27.so (0x00007f13297cc000) libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f13295c8000) libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f1329246000) libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f1328f3c000) libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f1328b72000) libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f132895c000) /lib64/ld-linux-x86-64.so.2 (0x000055f3137c8000)
After libpgicu27.so was copied to /usr/lib, we are noticing that all shared object dependencies for pgoe27.so are met. Excellent!
Set up ODBCINST
Edit /etc/odbcinst.ini and enter the following information:
[Progress] Description = Progress Driver Driver = /usr/dlc/odbc/lib/pgoe27.so FileUsage = 1
This file makes our drivers available for working with OpenEdge.
Testing OpenEdge ODBC connection
Assumption is made that your OpenEdge installation is serving ODBC connectivity already. My OpenEdge database is based on sports2000 and SQL engine is listening on port 10000.
Test.php
<?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'
);
try {
// build connection string and SQL
$connectionString = http_build_query($parameters, '', ';');
$sql = 'select name from pub.customer fetch first 10 rows only';
// establish connection and prepare statement
$db = new PDO('odbc:' . $connectionString, 'sysprogress','mypassword');
$stmt = $db->prepare($sql);
// confirm statement has been prepared correctly. If so, execute
if (!$stmt) {
echo 'Error :' . $db->errorInfo();
}
$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";
}
}
catch (Exception $e) {
echo $e->getTraceAsString() . '<br>' . $e->getMessage() . '<br>....';
}
?>Results
php test.php Lift Tours Urpon Frisbee Hoops Go Fishing Ltd Match Point Tennis Fanatical Athletes Aerobics valine Ky Game Set Match Pihtiputaan Pyora Just Joggers Limited
Results were returned in less than 15 ms and the experience was outstanding. This code can be translated into a quick API prototype, don’t you think?
Ports to open for OpenEdge SQL Access
I have OpenEdge SQL serving from port 10000. My OpenEdge instance is running on one virtual machine. In order for me to access OpenEdge from another virtual machine, port 10000 should be unblocked. Additionally, port 1025-2000 on Linux and port 3000-5000 on Windows should also be open for clients to access OpenEdge database using SQL. See knowledgebase article from Progress about opening ports also.
This concludes our goal of installing OpenEdge SQL Client Access ODBC drivers on Ubuntu. Hope you can follow these methods to test with your own favorite modern language.
See more articles on Modern web applications and API with OpenEdge, SQL, ODBC and PHP