Purpose
This tutorial shows you how to use PHP with Oracle Database 11g.
Time to Complete
Approximately 2 hours
Overview
PHP is a popular web scripting language, and is often used to create database-driven web sites. This tutorial helps you get started with PHP and Oracle Database by showing how to build a web application and by giving techniques for using PHP with Oracle. If you are new to PHP, review the Appendix: PHP Primer to gain an understanding of the PHP language.
Prerequisites
Before starting this Oracle By Example, please have the following prerequisites completed:
Install Oracle Database 11.2 |
Start DRCP connection pooling: sqlplus / as sysdba execute dbms_connection_pool.start_pool[]; execute dbms_connection_pool.restore_defaults[]; |
Create a user named PHPHOL with password of 'welcome'. Install Oracle's sample create sequence emp_id_seq start with 400;
create trigger my_emp_id_trigger
before insert on employees for each row
begin
select emp_id_seq.nextval into :new.employee_id from dual;
end;
/
--
-- Also to simplify the example we remove this trigger otherwise
-- records can only be updated once without violating the
-- PYTHONHOL.JHIST_EMP_ID_ST_DATE_PK constraint
--
drop trigger update_job_history;
--
-- Allow employees to be changed when testing the lab after hours.
--
drop trigger secure_employees;
|
Install Apache and enable UserDir module for public_html |
Install PHP 5.3.3 with the OCI8 1.4 extension. In php.ini set: oci8.connection_class = MYPHPAPP |
Extract these files to your $HOME location. |
Using PHP OCI8 with Oracle Database 11g
This section of the tutorial shows how to use the PHP OCI8 extension directly with Oracle Database. Using the OCI8 extension directly gives programmers maximum control over application performance.
Creating a Standard Connection
To create a connection to Oracle that can be used for the lifetime of the PHP script, perform the following steps.
Review the code in $HOME/public_html/connect.php The oci_connect[] function contains the username, the password and the connection string. In this case, Oracle's Easy Connect connection string syntax is used. It consists of the hostname and the DB service name. The oci_close[] function closes the connection. Any standard connections not explicitly closed will be automatically released when the script ends. |
Open a Web browser and enter the following URL to display the output: //localhost/~phphol/connect.php "Connected to Oracle!" is displayed if the connection succeeds. |
Review the SQL in $HOME/public_html/usersess.sql column username format a30 column logon_time format a18 set pagesize 1000 feedback off echo on select username, to_char[logon_time, 'DD-MON-YY HH:MI:SS'] logon_time from v$session where username is not null; exit This is a SQL script file that you run in SQL*Plus [Oracle's command-line SQL scripting tool]. This SQL*Plus script shows the current database sessions, and what time they logged into the database. |
Open a terminal window and enter the following commands to run the SQL script. Note that you could also execute the script in SQL Developer. cd $HOME/public_html sqlplus -l phphol/welcome @usersess.sql The SQL*Plus script lists the current database sessions. The only session shown is for SQL*Plus. The PHP connections from the oci_connect[] function has been closed. |
Edit $HOME/public_html/connect.php and change oci_connect[] to use a persistent connection oci_pconnect[]. $conn = oci_pconnect["phphol", "welcome", "//localhost/orcl"]; Reload the connect.php script in the browser. Now rerurn usersess.sql in SQL*Plus. cd $HOME/public_html sqlplus -l phphol/welcome @usersess.sql There should be two connected users. You may see more than two if you reloaded the page several times and Apache allocated different processes to handle the PHP script. By default, persistent connections stay open until the Apache process terminates. Subsequent PHP scripts can reuse the already opened connection, making them run faster. |
Using Database Resident Connection Pooling
Database Resident Connection Pooling is a new feature of Oracle Database 11g. It is useful for short lived scripts such as typically used by web applications. It allows the number of connections to be scaled as web site usage grows. It allows multiple Apache processes on multiple machines to share a small pool of database server processes. Without DRCP, a non-persistent PHP connection must start and terminate a server process, and a persistent PHP connection keeps hold of database resources even when PHP is idle.
Below left is diagram of nonpooling. Every script has its own database server proces. Scripts not doing any database work still hold onto a connection until the connection is closed and the server is terminated. Below right is a diagram with DRCP. Scripts can use database servers from a pool of servers and return them when no longer needed.
Batch scripts doing long running jobs should generally use non-pooled connections.
This section of the tutorial shows how DRCP can be used by new or existing applications without writing or changing any application logic. Perform the following steps:
Check that php has oci8.connection_class set. Open a terminal window and execute the following command: The connection class tells the database server pool that connections are related. Session information [such as the default date format] might be retained between connection calls, giving performance benefits. Session information will be discarded if a pooled server is later reused by a different application with its own connection class name. |
Review the code in $HOME/public_html/query_pooled.php Compare this code to the code in $HOME/public_html/query_nonpooled.php The only difference is the ":pooled" in the Easy Connect connection string in query_pooled.php. |
To run the scripts, the Apache Benchmark tool is used. This command repeatedly loads a web page, measuring its performance. From a terminal window, execute the following: ab -c 150 -t 30 //localhost/~phphol/query_pooled.php The above command sends Apache 150 concurrent requests for the script, repeatedly for 30 seconds. |
Now look at the number of database connections open. Open another terminal window, execute the following: sqlplus phphol/welcome select username, program from v$session where username = 'PHPHOL'; The default DRCP pool MAXSIZE is 40. You see up to 40 connections with PHPHOL username, depending on how many Apache processes handled the 'ab' requests. You may also need to execute the query while 'ab' is running to see the pooled servers working. Oracle manages the DRCP pool, shrinking it after a specified timeout. |
Now, you will run the same command except run the non-pooled script to compare the difference. From a terminal window, execute the following: ab -c 150 -t 30 //localhost/~phphol/query_nonpooled.php |
Now look at the number of database connections open. Open another terminal window, execute the following: sqlplus phphol/welcome select username, program from v$session where username = 'PHPHOL'; Many more rows than previously are returned. The rows with [TNS V1-V3] correspond to a running Apache process holding a database connection open. For PHP, Apache runs in a multi-process mode, spawning child processes each of which can handle one PHP script. Depending how Apache allocated these processes to handle the "ab" requests, you may see a varying number of rows in V$SESSION. Compare the number of requests completed in each run. You might want to run each script a few times to warm up the caches. Performance of the scripts is roughly similar. For the small works loads used in these two files, the tiny overhead of the handoff of pooled servers might make query_pooled.php a little slower than query_nonpooled.php. But the non-pooled script causes every single Apache process to open a separate connection to the database. For larger sites, or where memory is limited, the overall benefits of DRCP are significant. |
Fetching Data
A common task when developing Web applications is to query a database and display the results in a Web browser. There are a number of functions you can use to query an Oracle database, but the basics of querying are always the same:
| 1. | Parse the statement for execution. |
| 2. | Bind data values [optional]. |
| 3. | Execute the statement. |
| 4. | Fetch the results from the database. |
To create a simple query, and display the results in an HTML table, perform the following steps.
Review the code in $HOME/public_html/query.php The oci_parse[] function parses the statement. The oci_execute[] function executes the parsed statement. The oci_fetch_array[] function retrieves a row of results of the query as an associative array, and includes nulls. The htmlentities[] function escapes any text resembling HTML tags so it displays correctly in the browser. |
From your Web browser, enter the following URL to display the output: //localhost/~phphol/query.php The results of the query are displayed in the Web browser. The OCI_ASSOC parameter fetches the row as an associative arrary of column names and column data. Alternatively, the OCI_NUM parameter can be passed to oci_fetch_array[] to fetch the row as a numeric array. |
Using Bind Variables
Bind variables enable you to re-execute statements with new values, without the overhead of reparsing the statement. Bind variables improve code reusability, and can reduce the risk of SQL Injection attacks.
To use bind variables in this example, perform the following steps.
Review the code in $HOME/public_html/bind.php
|