Accessing Oracle from Apache+PHP in Win32
Satheesh Babu, vsbabu-removethis@vsbabu.org
v1.0, 26 September 1999
A brief step-by-step guide to setup Apache with PHP3 in Windows(95/98/NT) to access Oracle databases.
1. Introduction
This document describes how you can do Oracle web development with Windows9x systems using PHP. For the time being, this concentrates on Apache web server, but it should be possible with any other web server as well (especially Windows PWS, which comes with all Win98 systems).
Note that PHP is now at version 4 and that the installation instructions for Windows have changed. This document is not updated to reflect that.
If you have PHP in other environments - and I'm sure you do! - you can skip to sample programs. I found the CGI version of PHP a much better alternative to Pro*C! It is much easier to code in PHP and you don't have to worry about compiling, getting makefiles to work properly etc. Also, PHP does come with a much richer set of other functions than C.
2. Apache
2.1 Getting it
You must download the latest Apache binary for windows. You can download it
from
Apache distribution site
.
The current version of Apache as of this writing is
apache_1_3_6_win32.exe. Download that file and run it. Let us assume
that we choose the installation directory as C:\Apache. Next, go
here
and download a
zip file which has a utility which will sit in the system tray, to control
Apache. Extract the executable apmgr.exe from that zip file to
C:\Apache. Now create an alias for this file to your Startup
menu, if you want Apache to start automatically - note that you must call
this program with -r option to start Apache immediately.
Installation
Let us assume that we will be installing PHP to C:\php. So, edit C:\Apache\conf\httpd.conf and add the following lines - search for php3 first and add these lines where you see similar lines commented.
ScriptAlias /php3/ "c:/php/"
Action application/x-httpd-php3 "/php3/php.exe"
AddType application/x-httpd-php3 .php3
Now, search for DirectoryIndex. By default, only index.html will be specified. To that, add index.php3 as well. This will help you to have index.php3 files automatically taken from a directory.
3. PHP3
3.1 Getting it
Go to
PHP Site
and download the Win32
binary. Extract the files to C:\php.
3.2 Installation
Copy the file php3.ini-dist to your Windows directory and rename it as php3.ini. Open this file, search for extensions=. You will see a list of extensions. Uncomment the ones you want. Also search for php3_*.dll in C:\php. All these dlls are valid php modules. If you want some of it to be used, just add it as an extension line. For example, to connect to Oracle 7 databases, you need
extension=php3_oci7.dll
- gd - graphics generator.
- pdf - pdf generator.
- odbc - odbc.
- dbase - dbase files.
Dr. Jesus del Valle proposes the following for PHP4 installation -
I would recommend you writing it clear:
Thanks! He also corrected two bugs in the sample scripts.
and change the \"extension_dir\" () to
<your_php_path>\\extensions\\
extension = php_oci8.dll
extension = php_oracle.dll
4. Oracle
Look elsewhere to get Oracle libraries and SQL*Net installed in your system. If you are able to connect to database using SQL*Plus from Windows, you should not face any problems with PHP3.
5. Testing it all
Create a document ora.php3 as follows in C:\Apache\htdocs.
<?
if ($conn=Ora_Logon("user@TNSNAME","password")) {
echo "<B>SUCCESS ! Connected to database<B>\n";
} else {
echo "<B>Failed :-( Could not connect to database<B>\n";
}
Ora_Logoff($conn);
phpinfo();
?>
Start Apache by running C:\Apache\apmgr.exe -r. Open the location
http://localhost/ora.php3
in your
browser. You should see the status of your connection to Oracle and then
info on PHP installation on your server.
6. Sample Scripts
6.1 Connect to database and get username and sysdate
Here is another sample program which will connect to a database and fetch username and sysdate. Comments are in the form of echo statements.
<?
/*
* connect to database and execute a query
*/
function printoraerr($in_cur){
// function to check whether an oracle error occured
// if it did, print the error
// call this after every oracle call when a cursor is active
if(ora_errorcode($in_cur))
echo "Oracle code - ".ora_error($in_cur)."<br>\n";
return;
}
/** main */
if (!($conn=ora_logon("user@TNSNAME","password"))) {
echo "Connection to database failed\n";
exit;
}
echo "Connected as connection - <b>$conn</b><br>\n";
echo "Opening cursor ...<br>\n";
$cursor=ora_open($conn); printoraerr($cursor);
echo "Opened cursor - <b>$cursor</b><br>\n";
$qry="select user,sysdate from dual";
echo "Parsing the query <b>$qry</b> ...<br>\n";
ora_parse($cursor,$qry,0); printoraerr($cursor);
echo "Query parsed <br>\n";
echo "Executing cursor ...<br>\n";
ora_exec($cursor); printoraerr($cursor);
echo "Executed cursor<br>\n";
echo "Fetching cursor ...<br>\n";
while(ora_fetch($cursor)){
$user=ora_getcolumn($cursor,0); printoraerr($cursor);
$sysdate=ora_getcolumn($cursor,1); printoraerr($cursor);
echo " row = <B>$user, $sysdate </B><br>\n";
}
echo "Fetched all records<br>\n";
echo "Closing cursor ...<br>\n";
ora_close($cursor);
echo "Closed cursor<br>\n";
echo "Logging off from oracle... <br>\n";
ora_logoff($conn);
echo "Logged off from oracle <br>\n";
?>
6.2 Generic query
You can use this as a template. Just change the query and you are done. This can be used to easily web-enable your SQL queries in quick time.
<?
function printoraerr($in_cur, $conn){
// function to check whether an oracle error occured
// if it did, print the error
// call this after every oracle call when a cursor is active
// If it encountered an error, we exit immediately
if(ora_errorcode($in_cur)) {
echo "Oracle code - ".ora_error($in_cur)."<br>\n";
ora_logoff($conn);
exit;
}
return;
}
function exequery($w_qry,$conn) {
$cursor=ora_open($conn); printoraerr($cursor,$conn);
ora_parse($cursor,$w_qry,0); printoraerr($cursor,$conn);
ora_exec($cursor); printoraerr($cursor,$conn);
$numrows=0;
$w_numcols=ora_numcols($cursor);
// print headers
echo "
<TABLE WIDTH=\"100%\" BORDER=\"0\"
CELLSPACING=\"1\" CELLPADDING=\"2\">
<TR>\n";
for ($i=0;$i<$w_numcols;$i++) {
$align=(ora_columntype($cursor,$i)=="NUMBER")?"RIGHT"
:"LEFT";
echo "\t<TH VALIGN=TOP ALIGN=$align>".
ora_columnname($cursor,$i)."</TH>\n";
}
echo "</TR>\n";
while(ora_fetch($cursor)){
echo "<TR>\n";
for ($i=0;$i<$w_numcols;$i++) {
$align=(ora_columntype($cursor,$i)=="NUMBER")?
"RIGHT":"LEFT";
if(ora_columntype($cursor,$i)=="LONG")
echo "<TD VALIGN=TOP ALIGN=$align><PRE>".
ora_getcolumn($cursor,$i)."</PRE></TD>\n";
else
echo "<TD VALIGN=TOP ALIGN=$align>".
ora_getcolumn($cursor,$i)."</TD>\n";
printoraerr($cursor,$conn);
}
$numrows++;
echo "</TR>\n";
}
if ($numrows==0)
echo "<TR><TD COLSPAN=\"$w_numcols\">
<B>Query returned no records</B></TD></TR>\n";
else {
echo "<TR>\n";
echo "<TH COLSPAN=\"".($w_numcols-1)."\"
ALIGN=RIGHT>Count</TH>\n";
echo "<TH ALIGN=RIGHT>$numrows</TH>\n";
echo "</TR>\n";
}
echo "</TABLE>\n";
ora_close($cursor);
return;
}
// main
if(!($conn=ora_logon("user@SID","password"))) {
echo "Error: Cannot connect to database\n";
exit;
}
$qry="SELECT
deptno \"Dept\"
,empno \"Emp\"
,empnm \"Name\"
,salary \"Salary\"
FROM
employee
ORDER BY 1,2";
exequery($qry,$conn);
ora_logoff($conn);
?>
6.3 Authentication
Add this to the beginning of the code to validate oracle login. You must specify $SID proparly.
<?
if(!isset($PHP_AUTH_USER)) {
Header("WWW-authenticate: basic realm=\"$SID\"");
Header("HTTP/1.0 401 Unauthorized");
$title="Login Instructions";
echo "<blockquote>
You are not authorised to enter the site
</blockquote> \n";
exit;
} else {
if (!($conn=ora_logon("$PHP_AUTH_USER@$SID",$PHP_AUTH_PW))) {
Header("WWW-authenticate: basic realm=\"$SID\"");
Header("HTTP/1.0 401 Unauthorized");
$title="Login Instructions";
echo "<blockquote>
You are not authorised to enter the site
</blockquote> \n";
exit;
}
}
?>
Dr. Jesus del Valle, a reader, adds: People may test your scripts using the default Oracle database (in my case, ODBC:POLITE with user=system, password=system) and modifying your last lines as follows:
$qry=\"SELECT empno \\\"Nr.\\\",
ename \\\"Name\\\",
job \\\"Job\\\",
mgr \\\"Manager\\\"
FROM emp
ORDER BY 1,2\";
Sally Greenaway adds:
Hi,Sally is right. When I wrote this article, PHP was at version 3.0, and Oracle at 7.3.x. Now it is at 4.x and Oracle has gone up to 9i. php_oracle extension is for Oracle 7 and will probably make matters worse for Oracle 8+ clients.
I found your step by step guide for configuring Oracle/Apache and PHP VERY useful.
I'd just like to mention one thing that maybe you could amend so other people don't have the same problem. I followed the steps and changed the php.ini file exactly as you described, however I found that I was having real problems connecting to Oracle without getting application error messages.
After some investigation and time spent trawling through forums I discovered the reason. I only needed to use one oracle extension not both. I re-commented the php_oracle extension in the ini file and now I have no problems.
I'm running Oracle 8i and am using the php_oci8.dll, I don't know if it's different for different versions.
Hope this is useful
Sally
Thanks Sally!
If you are using IIS or PWS, you might want to try PHP Everywhere
easy installer.