Feedback
Did this article resolve your question/issue?

   

Article

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

 
TitleHow 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)
URL Name000027930
Article Number000149038
EnvironmentProduct: 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/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
Last Modified Date3/26/2020 11:03 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.