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)

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

Aug 27, 2014
Knowledge

Title

URL Name

000027930

Article Number

000149038

Environment

Product: OpenEdge Version: 10.2B06, 11.x OS: CentOS/RedHat Linux 5.x 6.x 7.x 64 bit, Other: ODBC, PHP

Question/Problem Description

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

Steps to Reproduce

Clarifying Information

64 bit ODBC drivers for Linux are 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 ODBC client connections.

Error Message

Defect Number

Enhancement Number

Cause

Resolution

Workaround

Please note that the fully supported technique involves building PHP from source and using specific parameters. For the fully supported technique, please view article:

000035694: "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?"

The method described here however allow:

- to install, use and maintain the software components needed to configure an ODBC configuration (with PHP) using the standard Linux packages that are supported by the Operating System vendor (Red Hat, Oracle Linux) or the community that support that Linux distribution (CentOS).

- to install security or bug fixes for those software components automatically without recompiling those software components (easier to maintain).

- to create a Linux ODBC connection in a quicker way (no source files to download, no components to compile)

- avoid the risk of installing conflicting packages by accident (for example by installing the Operating System provided PHP or Appache webserver packages while those software components were already installed by compilling those software components from source).

- to use the Linux security feature SELinux that is only defined by default for the software packages provided by the Operating System.

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.x, 6.x or 7.x.

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. It is expected that commands issued in this article are being executed by ‘root’ user.

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.

2. Disable (temporarily or permanently) the standard SELinux enforcing mode.

This can be done temporarily with the command '/usr/sbin/setenforce 0' or permanently by editing the file /etc/selinux/config so that the line containing ‘SELINUX=’ is not set to 'SELINUX=enforcing' but to either ‘SELINUX= permissive’ or ‘SELINUX= disabled’.

(please not that changing to SELINUX= disabled will make enabling SELinux again take a lot of time as a full re-labelling of the file system will be necessary )

3. Reboot the server. Only required if a change was made to the SELinux mode in the file /etc/selinux/config as mentioned above.

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”.

Please note that OpenEdge ODBC drivers higher than version 11.0 are not compatible with CentOS5 / Red Hat 5 and will produce an error similar to this one:

Warning: odbc_do() [function.odbc-do]: SQL error: [unixODBC][Driver Manager]Driver does not support this function, SQL state IM001 in SQLSetStmtOption in /var/www/html/db.php on line 9

cannot execute 'SELECT * FROM PUB.Customer' closing connection Resource id #2

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.

PHP configuration:

1. Ensure the 64 bit unixODBC is installed on the machine by running:

yum install unixODBC.x86_64

2. If PHP is not installed on the machine then execute the commands:

yum install php-odbc.x86_64
yum install php

3. If using Centos 5, after the release of Centos 5 minor release 6 the php53.x86_64 and php53-odbc.x86_64 Centos packages became available. Those packages allow the installation of PHP 5.3 (instead of version 5.1) directly from the Centos repositories. To upgrade or install PHP version 5.3 on Centos 5 use the following commands:

sudo /sbin/service httpd stop

Run the following command to keep track of which extensions will need to be reinstalled afterward:
yum list installed php php-* > php_list.txt

sudo yum remove php
sudo yum remove php-odbc.x86_64
sudo yum remove php-common
sudo yum update
sudo yum install php53.x86_64
sudo yum install php53-odbc.x86_64

Run the following command for each extension needing to be reinstalled. The names are the same as mentioned within the file php_list.txt except that instead of the prefix “php” they now need the prefix “php53”:
yum install php53-ExtensionName

sudo /sbin/service httpd start

4. Create a file /var/www/html/db.php containing the following. Adjust the highlighted pathnames for OpenEdge version being used:

<?PHP
$dsn = "Progress";
putenv("ODBCINI=/etc/odbc.ini");
Print "Test ODBC Progress <br>";
$sql="SELECT * FROM PUB.Customer";
if ($conn_id=odbc_connect("Progress","sysprogress","sysprogress")){
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(); */
?>

ODBC configuration:

1. Copy some files from the OpenEdge directory to the Linux lib64 directory.

NOTE: Depending on the version of OpenEdge used, the filenames to copy may be slightly different. replace XX with the correct number.
NOTE: Do not copy libodbc.so otherwise the CentOS 5 Apache web server will crash soon after startup.
NOTE: The "echo y | " is used so that existing files are automatically overwritten.
NOTE: The DLC variable must be set in order for the code below to work, the proenv shell sets this variable automatically.

echo y | cp $DLC/odbc/lib/libodbcinst.so /usr/lib64/libodbcinst.so
echo y | cp $DLC/odbc/lib/libpgicuXX.so /usr/lib64/libpgicuXX.so
echo y | cp $DLC/odbc/lib/libpgmback.so /usr/lib64/libpgmback.so
echo y | cp $DLC/odbc/lib/libpgsslXX.so /usr/lib64/libpgsslXX.so
echo y | cp $DLC/odbc/lib/odbccurs.so /usr/lib64/odbccurs.so
echo y | cp $DLC/odbc/lib/pgoeXX.so /usr/lib64/pgoeXX.so
echo y | cp $DLC/odbc/lib/pgtrcXX.so /usr/lib64/pgtrcXX.so
echo y | cp $DLC/odbc/lib/vscnctdlg.so /usr/lib64/vscnctdlg.so

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

[ODBC Data Sources]
Progress=Progress_SQL92_Driver

[Progress]
Driver=/OpenEdgeInstallDir/odbc/lib/pgoeXX.so
DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={LD_LIBRARY_PATH=/OpenEdgeInstallDir/odbc/lib:/OpenEdgeInstallDir/lib}
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

Note: ("DMEnvAttr = SQL_ATTR_UNIXODBC_ENVATTR={LD_LIBRARY_PATH=/OpenEdgeInstallDir/odbc/lib:/OpenEdgeInstallDir/lib}" need to be on the same line inside the file /etc/odbc.ini)

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

[ODBC]
Trace = no
TraceFile = /tmp/odbctrace.out
TraceDll=/OpenEdgeInstallDir/odbc/lib/pgtrcXX.so

[ODBC Drivers]
Description = Progress driver
[Progress OpenEdge 11 Driver]
Driver=/OpenEdgeInstallDir/odbc/lib/pgoeXX.so
FileUsage = 1

4. If using CentOS 6, the following command must be executed to correct a bug in CentOS 6:

sudo ln /usr/lib64/libodbccr.so.2 /usr/lib64/libodbccr.so.1

Otherwise during testing later in this article the following error message will appear:
Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open cursor lib 'libodbccr' : file not found, SQL state 01000 in SQLConnect in /var/www/html/db.php on line 6 cannot connect to DSN: Progress

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

sudo /sbin/service httpd 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, stop and restart httpd and then re-try http://localhost/db.php to review errors.

If the following error message appears then the most likely cause is that either SELinux wasn’t disabled or the appropriate $DLC/odbc/lib files weren’t copied to /usr/lib64:

Warning: odbc_connect(): SQL error: [unixODBC][Driver Manager]Can't open lib '/usr/dlc/odbc/lib/pgoe26.so' : file not found, SQL state 01000 in SQLConnect in /var/www/html/db.php on line 7 cannot connect to DSN: Progress

b) To test the ODBC connection outside of PHP the following commands can be used:

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>

(optional) Setting the environment variable ODBCINI to /etc/odbc.ini for the CentOS Linux machine :

create a text file called for example /etc/profile.d/odbcini.sh containing the following 2 lines:
ODBCINI=/etc/odbc.ini

export ODBCINI
This will allow the command 'isql -v Progress sysprogress sysprogress' to run without first having to execute the command 'export ODBCINI=/etc/odbc.ini'

(optional) Re-enable SELinux enforcing mode:

(All the following commands must be executed as root)

Under CentOS 5 execute the command 'yum install policycoreutils'

Under CentOS 6 execute the command 'yum install policycoreutils-python'

In the web browser go to http://localhost/db.php

/usr/sbin/setenforce 1

/usr/sbin/setenforce 0 (to re-enable the selinux audit loging)

cat /dev/null > /var/log/audit/audit.log (to empty the file audit.log, this command does not work with 'sudo' only with the root user)

more /var/log/audit/audit.log (audit.log should now be empty)

Refresh the web browser page on http://localhost/db.php

more /var/log/audit/audit.log (audit.log should now contain text)

grep httpd /var/log/audit/audit.log | audit2allow -M openedgeodbcpol

more openedgeodbcpol.te (and check the content of this text file with the examples included in this article)

/usr/sbin/semodule -i openedgeodbcpol.pp (this command can take some time to execute)

/usr/sbin/setenforce 1

Refresh the web browser page on http://localhost/db.php, the ODBC connection to the database should now still work despite SELinux mode being set to enforcing mode.

Example of the content of the file openedgeodbcpol.te under CentOS 5:

module openedgeodbcpol 1.0;

require {

type httpd_t;

type default_t;

type port_t;

class tcp_socket name_connect;

class file { read getattr execute };

}

#============= httpd_t ==============

allow httpd_t default_t:file { read getattr execute };

allow httpd_t port_t:tcp_socket name_connect;

Example of the content of the file openedgeodbcpol.te under CentOS 6:

module openedgeodbcpol 1.0;

require {

type httpd_t;

type default_t;

type port_t;

class tcp_socket name_connect;

class file { read getattr open execute };

}

#============= httpd_t ==============

allow httpd_t default_t:file { read getattr open execute };

#!!!! This avc can be allowed using one of the these booleans:

# allow_ypbind, httpd_can_network_connect

allow httpd_t port_t:tcp_socket name_connect;

To enable ODBC tracing the option 'Trace = yes' can be set in the section [ODBC] of the file /etc/odbcinst.ini

When a PHP page using an ODBC connection (for example http://localhost/db.php) is then executed then the file /tmp/odbctrace.out will be created with the permissions -rw-r--r-- apache apache

To allow other programs such as the isql utility to write to this file /tmp/odbctrace.out when using an ODBC connection the following command will then need to be run:

sudo chmod 666 /tmp/odbctrace.out

(please note that ODBC tracing can produce quite large files)

A video that show the configuration of a Linux ODBC connection and PHP on CentOS 6 with the steps described above:

Notes

References to other documentation:

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

Progress Article(s):
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 set up and test the ODBC driver on Unix?

Two Sample PHP Programs to Access a Progress DB through ODBC

How to know if ODBC is installed in an Unix Environment?
How to create an OpenEdge 10.2B 32 bit ODBC connection under CentOS 5.6 32 bit?

Keyword Phrase

Last Modified Date

11/20/2020 7:32 AM

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)

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, 6 or 7 via Yum (embedded video)

Information

Related Articles