How to configure the 64-bit OpenEdge ODBC driver to work with PHP on CentOS/RedHat/Oracle Linux versions 5 or 6, via Source builds.

How to configure PHP and the OpenEdge 64-bit ODBC driver on Centos/RedHat/Oracle Linux 5 or 6, using Source installations, to retrieve data from an OpenEdge database.

Jul 20, 2018
Knowledge

Title

URL Name

000035694

Article Number

000134284

Environment

Product: OpenEdge Version: 10.2B06 and later, 11.x to 11.6 inclusive OS: CentOS RedHat Oracle Linux 5.x 6.x 7.x 64 bit

Question/Problem Description

How to configure PHP and the OpenEdge 64-bit ODBC driver on Centos/RedHat/Oracle Linux 5, 6 or 7 using Source installations, to retrieve data from an OpenEdge database.

Steps to Reproduce

Clarifying Information

A 64 bit ODBC driver for Linux is only available from OpenEdge 10.2B06 and 11.0 onward.
Prior to OpenEdge 10.2B06 and 11.0, both 32 bit and 64 bit OpenEdge releases for Linux contain only 32-bit ODBC drivers and therefore only allow 32-bit client connections.
The supported OpenEdge ODBC driver configuration technique does not work with the unixODBC package.

Error Message

Defect Number

Enhancement Number

Cause

Resolution

The supported method to get the OpenEdge ODBC driver to work with PHP requires PHP to be configured with the “–with-custom-odbc” and “--with-apxs2=/usr/local/apache2/bin/apxs” flags. However, standard rpm packages don’t allow you to run ./configure and the default httpd installation doesn’t contain the apxs directory. Therefore both Apache and PHP need to be built from source.

The steps below provide the necessary steps to create a 64 bit ODBC connection from a PHP webpage to an OpenEdge database on a newly installed 64 bit CentOS/RedHat 5.4 or 6.1, using Source builds of Apache and PHP.

The intention of this article is not to provide an extensive configuration document and the user’s requirements for each installation or configuration may vary.

BEFORE PROCEEDING:

The machine should be backed up prior to performing any steps mentioned in this article. The steps in this article involve removing and installing various pieces of software and have the potential to adversely impact your system.
If Apache and/or PHP have already been used, you may wish to back up relevant files in case they are removed or overwritten.
Source code installations can cause issues if overlaid on top of RPM installs, as the two most likely won’t share the same directories. Care must be taken to ensure that rpm/yum is not used to install the same components once the source install is used. It is not possible to use rpm/yum to upgrade a source based installation.
It is recommended that the Apache and PHP installation guides be reviewed. Progress provides no guarantee that the details and instructions within this article are or will remain accurate as they involve configuration outside of the Progress products.
It is expected that commands issued in this article are being executed by ‘root’ user.

The example below was created using:
Centos 6.1
PHP 5.4.7
OpenEdge 11.1

(the same procedure was also tested with CentOS 7, PHP 7.2.7, apr-util-1.6.1, apr-1.6.3, pcre2-10.31, httpd-2.4.32 and OpenEdge 11.6.4)

Operating System configuration:

1. Disable the Linux Firewall.

Configuring appropriate firewall settings is outside the scope of this article. Disabling the firewall is mentioned for simplicity of this guide to avoid port issues and is not recommended for a server that is actively in-use.

OpenEdge configuration:

1. If using OpenEdge 11.1 onward it is not necessary to download a JDK as it is now bundled and installed within OpenEdge. For OpenEdge 11.0, download and install the 64 bit JAVA 1.6.0_26 JDK from Oracle as per the requirements listed within the OpenEdge documentation:

OpenEdge Getting Started:Installation and Configuration, Chapter “Installation” -> “UNIX Systems Installation Requirements”.

2. Install an OpenEdge product that contains the ODBC drivers such as SQL Client Access, Client Networking, Database licenses. These example instructions assume that a database license is being installed. During installation, when prompted to copy scripts in the path (/usr/bin) do not do so.

3. Create an OpenEdge demo database called “testdb” and start it in multi-user mode. This will be the database that the ODBC driver will connect to:

/OpenEdgeInstallDir/bin/proenv

prodb testdb demo

proserve testdb –S 5555

4. Create a user within the OpenEdge database that will be used for ODBC access. This will be a userid “sysprogress” with password “sysprogress”:

a) Start the Data Administration Tool.

mpro testdb –p _dict.p

b) Within the data administration tool, use F3 to select the menu and choose Admin -> Security -> Edit User List... -> Add and add a user called 'sysprogress' with password ‘sysprogress’. The sysprogress user is an administrator account that has access to all tables. In a production database a different user should be created / used and appropriate access rights granted to them. Once complete, exit the Data Administration Tool.

ODBC Configuration:

1. Create a file /OpenEdgeInstallDir/odbc/include/odbc.h with the following contents:

#include <sql.h>

#include <sqlext.h>

#include <odbcinst.h>

2. Edit the file /etc/odbc.ini to include the following lines, create the file if it doesn't already exist. Adjust the highlighted path and filenames according to the OpenEdge version being used:

[ODBC Data Sources]

Progress=Progress_SQL92_Driver

[Progress]

Driver=/OpenEdgeInstallDir/odbc/lib/pgoe26.so

DatabaseName=testdb

PortNumber=5555

HostName=localhost

LogonID=sysprogress

Password=sysprogress

;APILevel=1

;ConnectFunctions=YYN

;CPTimeout=60

;DriverODBCVer=03.60

;FileUsage=0

;SQLLevel=0

;UsageCount=1

;ArraySize=50

;DefaultLongDataBuffLen=2048

;DefaultIsolationLevel=REPEATABLE READ

;StaticCursorLongColBuffLen=4096

;TRACE=1

;TRACEFILE=/tmp/odbctrace.out

[ODBC]

InstallDir=/OpenEdgeInstallDir/odbc

Trace=1

TraceFile=/tmp/odbctrace.out

TraceDll=/OpenEdgeInstallDir/odbc/lib/pgtrc26.so

UseCursorLib=0

3. Edit the file /etc/odbcinst.ini to include the following lines. Create the file if it doesn't already exist. Adjust the highlighted path and filename according to the OpenEdge version being used:

[ODBC Drivers]

Description = Progress driver

[Progress OpenEdge 11 Driver]

Driver=/OpenEdgeInstallDir/odbc/lib/pgoe26.so

FileUsage = 1

Download Required Source Files:

Apache needs to be built from source code. As part of the build process there are some dependencies on other components and these must also be installed. There might also be other dependencies depending on how your Centos was built, but the various installations will make you aware of any additional requirements during the build process.

1. Create a directory /usr/local/src if it doesn’t already exist. This directory will be used to contain the source so that it’s stored somewhere central.

mkdir /usr/local/src

2. Download the following tar.gz files and place them into /usr/local/src:

apr-1.4.6.tar.gz and apr-util-1.4.1.tar.gz files (required by Apache Http) from:
http://apr.apache.org/
pcre-8.31.tar.gz file (required by Apache Http) from:
http://pcre.org/
Apache Http Unix Source httpd-2.4.3.tar.gz file from:
http://httpd.apache.org/
php-5.4.7.tar.gz file from:
http://www.php.net/downloads.php

Removal of Pre-Existing Apache and PHP Installations:

Unless there is already a source installation of Apache & PHP installed, pre-existing installations may need to be uninstalled. Note that out-of-the-box Linux installations may automatically have Apache and/or PHP installed via RPM files.

1. Stop Apache Httpd if it is running:

service httpd stop

2. First make a list of any pre-existing Apache, httpd, PHP installations you wish to remove:

yum list installed apache*
yum list installed httpd
yum list installed php*

3. To remove a package:

yum remove package

Apache Installation:

1. Ensure that a compiler is installed:

yum install gcc gcc-c++ expat-devel

2. Extract the downloaded files.

cd /usr/local/src
tar zxf httpd-2.4.3.tar.gz
tar zxf pcre-8.31.tar.gz

3. The apr and apr-util need to reside within a subdirectory of Apache httpd. They also need to have their version numbers removed from their directory names:

cd /usr/local/src/httpd-2.4.3/srclib
tar zxf /usr/local/src/apr-1.4.6.tar.gz
tar zxf /usr/local/src/apr-util-1.4.1.tar.gz
mv apr-1.4.6 apr
mv apr-util-1.4.1 apr-util

4. Configure and install the Apache components. Note that this example only uses minimal httpd ./configure parameters and the Apache documentation should be reviewed for additional parameters that you may wish to add:

cd /usr/local/src/pcre-8.31
./configure
make
make install

cd /usr/local/src/httpd-2.4.3/srclib/apr
./configure
make
make install

cd /usr/local/src/httpd-2.4.3/srclib/apr-util
./configure --with-apr=/usr/local/src/httpd-2.4.3/srclib/apr
make
make install

cd /usr/local/src/httpd-2.4.3
./configure --enable-so --with-included-apr --prefix=/usr/local/apache2
make clean
make
make install

5. Create a file /usr/local/apache2/htdocs/db.php :

<?PHP
$dsn = "Progress";
putenv("ODBCINI=/etc/odbc.ini");
putenv("ODBCINST=/etc/odbcinst.ini");
Print "Test ODBC Progress <br>";
$sql = "SELECT * FROM PUB.Customer";
if ($conn_id=odbc_connect("Progress","sysprogress","sysprogress", SQL_CUR_USE_ODBC)){
echo "connected to DSN: $dsn <br>";
if($result=odbc_do($conn_id, $sql)) {
echo "executing '$sql'";
echo " Results: ";
odbc_result_all($result, "BGCOLOR='#AAFFAA' border=3 width=30% bordercolordark='#FF0000'");
echo "freeing result";
odbc_free_result($result);
}else{
echo "cannot execute '$sql' ";
}
echo " closing connection $conn_id";
odbc_close($conn_id);
}else{
echo " cannot connect to DSN: $dsn ";
}
/* phpinfo(); */
?>

PHP Installation:

1. Set the following ODBC environment variables:

ODBC_HOME=/OpenEdgeInstallDir/odbc; export ODBC_HOME
ODBCINST=/etc/odbcinst.ini; export ODBCINST
CPPFLAGS="-I$ODBC_HOME/include"; export CPPFLAGS
CUSTOM_ODBC_LIBS="-L$ODBC_HOME/lib -lodbc -lodbcinst"; export CUSTOM_ODBC_LIBS

2. Extract the php-5.4.7.tar.gz file:

cd /usr/local/src
tar zxf php-5.4.7.tar.gz

3. Configure and Install PHP. The below command will install PHP into the /usr/local/php5.4 directory. Note that the ./configure command is being provided minimal parameters and the PHP documentation should be reviewed for additional parameters that you may wish to add:

cd /usr/local/src/php-5.4.7
./configure --with-apxs2=/usr/local/apache2/bin/apxs --prefix=/usr/local/php5.4 --with-custom-odbc=$ODBC_HOME
make
make install

If you decide to change your configure options after installation, you'll need to re-run the configure, make, and make install steps. You only need to restart apache for the new module to take effect. A recompile of Apache is not needed.

4. Edit /usr/local/apache2/conf/httpd.conf to tell Apache to parse .php extensions, otherwise when viewing the db.php page it will display as text rather than running the code. At the bottom of httpd.conf file add the following lines:

<FilesMatch \.php$>
SetHandler application/x-httpd-php
</FilesMatch>

5. If the webserver is not started then launch it with the command:

/usr/local/apache2/bin/apachectl start

Test the ODBC connection from Apache/PHP:

1. On the Linux console open a web browser. A working ODBC connection should now be seen at address:

http://localhost/db.php

And should produce output like this:

Test ODBC Progress

connected to DSN: Progress

executing 'SELECT * FROM PUB.Customer' Results:

...

freeing result closing connection Resource id #2

2. If the ODBC connection is not working, do the following:

a) Within the /etc/php.ini configuration file, search for “display_errors” and set the parameter to On. Note that there may be two locations where this parameter is mentioned, ensure that only one is uncommented. E.g:

display_errors = On

Once set, re-try http://localhost/db.php to review errors.

b) To test the ODBC connection outside of PHP the following commands can be used. Be sure to adjust the LD_LIBRARY_PATH:

export LD_LIBRARY_PATH=/OpenEdgeInstallDir/odbc/lib:/OpenEdgeInstallDir/lib

export ODBCINI=/etc/odbc.ini
/OpenEdgeInstallDir/odbc/samples/example/example

The interactive output should show something like:

/example DataDirect Technologies, Inc. ODBC Example Application.
Enter the data source name : Progress
Enter the user name : sysprogress
Enter the password : sysprogress
Enter SQL statements (Press ENTER to QUIT)
SQL> select * from PUB.customer

if the isql or iusql unixODBC utilities are installed on the Linux machine then the following commands also be used:

export LD_LIBRARY_PATH=/OpenEdgeInstallDir/odbc/lib:/OpenEdgeInstallDir/lib

export ODBCINI=/etc/odbc.ini

and then
isql -v Progress sysprogress sysprogress
or
isql -v -k DSN=Progress
or
isql -v -k "DRIVER={Progress OpenEdge 11 Driver};HostName=localhost;Database=testdb;PortNumber=5555;LogonID=sysprogress;Password=sysprogress"

The output should show:

+--------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+--------------------+

SQL>

It is then possible to execute an SQL statement like select * from PUB.customer which will return output similar to:

...

SQLRowCount returns -1

33 rows fetched

SQL>

Workaround

Notes

References to other documentation:

OpenEdge Data Management:SQL Development, Pages 59 to 65, "ODBC Client" -> "Configuring UNIX clients"

Progress Articles:

How to know if ODBC is installed in a Unix Environment?
How to set up and test the ODBC driver on Unix?

How to create an OpenEdge 10.2B 32 bit ODBC connection under CentOS 5.6 32 bit?
Sample PHP Programs to Access a Progress DB through ODBC
How to configure the 64-bit OpenEdge ODBC driver to work with PHP on CentOS/RedHat/Oracle Linux versions 5, 6 or 7 via Yum (embedded video)

Keyword Phrase

Last Modified Date

11/20/2020 7:08 AM

How to configure the 64-bit OpenEdge ODBC driver to work with PHP on CentOS/RedHat/Oracle Linux versions 5 or 6, via Source builds.

Files(0)

Was this article helpful?

Was this article helpful?

How to configure the 64-bit OpenEdge ODBC driver to work with PHP on CentOS/RedHat/Oracle Linux versions 5 or 6, via Source builds.

Information

Related Articles