2 Getting Started

Unlocking the HR User

The PHP application connects to the database as the HR user. You may need to unlock the HR account before it can be used. Use SQL*Plus or SQL Developer to unlock the HR user.

This section contains the following topics:

• Unlocking the HR User Using a Command Line

• Unlocking the HR User Using SQL Developer:

SQL> source /u01/app/oracle/product/12.1/bin/oracle_env.sh
SQL> sqlplus system/system_password
SQL> alter user hr identified by welcome account unlock;

Database Resident Connection Pooling

The AnyCo sample application will use Database Resident Connection Pooling (DRCP) to show how a PHP application can scale to support many users.

PHP cannot be assumed to be thread safe and is typically run in a multi-process mode, for example with the Apache web server's pre-fork model or with FastCGI. Sharing Oracle connections between active and idle PHP processes is not possible in the mid-tier because there is no interprocess communication. DRCP works because the sharing is handled by the database host computer. This also allows connection resources created by multiple mid-tier hosts to be shared.

For best performance it is common for PHP OCI8 applications to use "persistent" database connections. When PHP has completed an application script and sent its output to the web user's browser, the script's underlying DB connection is not closed. The connection remains cached in the still-running, now idle PHP/Apache process. It can be reused by this PHP process in any subsequent PHP script connecting with the same database credentials. This has great performance benefits. However without DRCP, when there are large numbers of PHP processes the open database connections may use a large amount of database host memory. This is despite many connections being idle due to the user's "think-time" between making web page requests, or while the PHP script runs non-database operations.

DRCP allows PHP applications to efficiently use database host memory to support large numbers of web users. DRCP allows database resources to be used by only those web users currently doing database operations. Benchmarks have shown DRCP can support tens of thousands of web users on small, commodity Linux database hosts.

Figure 2-1 Without DRCP, Idle Persistent Connections from PHP still Consume Database Resources.

DRCP overcomes the database host memory pressure by maintaining a small pool of database server processes on the host. These can be shared by all the PHP database connections across all PHP processes and mid-tier servers when they are needed.

Figure 2-2 DRCP Architecture

If a PHP script connects to the database but there is no pooled server process available, then it will wait until one is free. This prevents the database from being overloaded and allows applications to continue running.

Once the DRCP pool is started, applications can choose at run time whether to use it or not. This is indicated in the PHP OCI8 connection string. Typically only short lived, similar kinds of tasks should use DRCP. Batch processes should not use the pool.

The DRCP pool can be used in two variants. The basic method is that only the processes are reused. The second method increases performance by also reusing the "session" memory associated with each process. In PHP, only "persistent" connections use the latter method. For web applications like PHP where each script is part of a single application, this session memory sharing is generally perfectly acceptable. However care must be taken that any retained session settings such as the date format do not occur unexpectedly and that they do not constitute an information security leak. DRCP allows the pool to be virtually sub-partitioned to reduce any issues like this.

PHP OCI8 applications use the oci_pconnect() call to create a persistent database connection. Applications can also connect to Oracle using oci_connect() or oci_new_connect(), which create non-persistent connections. The oci_new_connect() function always returns a new, transactionally independent connection resource each time it is called. The oci_connect() and oci_pconnect() functions will return their respective same PHP resource if a running script calls them multiple times with the same connection credentials. For each connection method, a rollback occurs after each script, if necessary.

There are also differences in behavior between the three functions depending on whether DRCP is being used.

Without DRCP, a persistent connection remains open even when the PHP script has completed. A subsequent script connecting with the same credentials can immediately reuse that connection. This is fast but the database host must have enough memory to maintain the connections from each PHP process even when the processes are idle. The oci_connect() and oci_new_connect() functions do not retain the underlying connection to the database after the PHP script completes. This makes connection slower to establish but memory use on the database host is capped by the number of active web users.

When PHP connections uses DRCP, all three OCI8 connection functions benefit from using established DRCP server processes from the DRCP pool. When each script finishes (if not earlier), its database pooled server is returned to the DRCP pool for reuse. A lightweight connection to the DRCP broker is retained, which aids re-connection performance. An oci_pconnect() function will reuse the process session memory, provide more efficiency and higher scalability. Each oci_connect() and oci_new_connect() call will recreate the Oracle session memory in the reused DRCP pooled process.

More information on DRCP and PHP is in the whitepaper:

http://www.oracle.com/technetwork/topics/php/whatsnew/php-scalability-ha-twp-128842.pdf

Starting the DRCP Pool

The DRCP pool can be controlled in SQL*Plus with the pre-supplied PL/SQL DBMS_CONNECTION_POOL package.

To start the pool on Oracle Linux, open a terminal window and connect as the root user:

# su -

Now, su to the Oracle account:

# su - oracle
$ source /u01/app/oracle/product/12.1/bin/oracle_env.sh

Run SQL*Plus with the SYSDBA system privilege and invoke the DBMS_CONNECTION_POOL.START_POOL() procedure:

$ sqlplus / as sysdba
SQL> execute dbms_connection_pool.start_pool()

The pool will now run with its default parameters.

To stop the pool, run:

SQL> execute dbms_connection_pool.stop_pool()

If DRCP is running when the database is restarted, then the pool will automatically restart.

Pool parameters can be changed with DBMS_CONNECTION_POOL.ALTER_PARAM(), for example:

SQL> execute dbms_connection_pool.alter_param(null, 'MAXSIZE', '10');

The pool should be restarted after changing parameters.

The current pool settings can be viewed by querying Oracle's data dictionary:

select * from DBA_CPOOL_INFO;

The overall DRCP Pool statistics can be seen in

select * from V$CPOOL_STATS;

By observing the statistics over time you can decide how to tune the pool parameters.

Each DRCP database connection can specify an arbitrary "connection class". In PHP the connection class can be configured in PHP's php.ini initialization file or can be set at run time. See "Post PHP Installation Tasks on Windows and Linux". The connection class helps partition the DRCP pool for different use cases.

The statistics for each connection class can be seen using:

# select * from V$CPOOL_CC_STATS;

Not setting a connection class results in reduced sharing of the DRCP pool resources. For general application, if V$CPOOL_CC_STATS shows a large number of system generated connection class names, then check that your PHP configuration files on each mid tier server is correctly setting the connection class.

The DRCP pool is sharable across all enabled applications, including those written in PHP, Perl, and Python. Some tools like SQL*Plus are not DRCP enabled. If you use a DRCP connection with SQL*Plus you will see entries in V$CPOOL_CC_STATS with the class name SHARED. SQL*Plus will reuse the DRCP pool processes but will have to recreate each process's session memory.

When you have built the AnyCo application and run it, you can examine the monitoring views to see DRCP in action.

Installing Apache on Windows XP

PHP 5.3.6 is installed using the FastCGI model in Windows. Perform the following steps to obtain Apache HTTP Server for Windows:

1. Enter the following URL in your Web browser:

   http://httpd.apache.org/download.cgi
   

2. Click the httpd-2.2.17-win32-x86-no_ssl.msi.

3. Save the downloaded file in a temporary directory, such as c:\tmp and double click to install it.

   The software will install to a directory like: C:\Program Files\Apache Software Foundation\Apache2.2.

   The file name and extraction directory are based on the current version. Throughout this procedure, ensure you use the directory name for the version you are installing.

4. Download the Apache mod_fcgid FastCGI component from the following URL:

   http://httpd.apache.org/download.cgi#mod_fcgid

5. Unzip it to the installed Apache 2.2 directory.

6. Edit C:\Program Files\Apache Software Foundation\Apache2.2\conf\httpd.conf and add:

   LoadModule fcgid_module modules/mod_fcgid.so
   

   In httpd.conf locate the <Directory> section for htdocs and add ExecCGI to the Options:

   <Directory "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs">
   ...
   Options Indexes FollowSymLinks ExecCGI
   ...
   </Directory>
   

You can use the Start menu option to start Apache. This opens a console window showing any error messages. Error messages may also be written to C:\Program Files\Apache Software Foundation\Apache2.2\logs\error.log.

You can also use the ApacheMonitor utility to start Apache. If you chose to install Apache as a service for all users, it will appear as an icon in your System Tray.

If you have errors, double check your httpd.conf file

Installing Apache on Linux

This section describes how to install Apache HTTP Server on Linux.

The file name and extraction directory are based on the current version. Throughout this procedure, ensure you use the directory name for the version you are installing.

Apache is typically already installed on Linux or directly available in package repositories.

This section contains the following topics:

• Using the Default HTTPD Package on Oracle Linux

• Manually Installing Apache on Linux

• Setting the Oracle Environment for Apache on Linux

• Setting up a User Directory for the Example Project on Linux

# /usr/local/apache/bin/apachectl start

# /usr/local/apache/bin/apachectl stop

export ORACLE_HOME=/u01/app/oracle/product/12.1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Installing PHP on Windows

This section describes how to install PHP on Windows.

The file name and extraction directory are based on the current version. Throughout this procedure, ensure you use the directory name for the version you are installing.

You must be the administrator user to install PHP. To install PHP, perform the following steps:

1. Download the PHP 5.3.6 zip file from the following Web site:

   http://windows.php.net/download/

   Use the non-thread safe bundle because it will be installed in FastCGI mode.

2. In Windows Explorer, go to the directory where you downloaded the PHP 5.3.6 zip file.

3. Unzip the PHP package to a directory called C:\php-5.3.6

4. Copy php.ini-development to C:\php-5.3.6\php.ini

5. Edit php.ini to make the following changes:

   • Add the line extension_dir = "C:\php-5.3.6\ext".

     This is the directory containing the PHP extensions.

   • Remove the semicolon from the beginning of the line

     extension=php_oci8_11g.dll

6. Edit C:\Program Files\Apache Software Foundation\Apache2.2\conf\httpd.conf and add the following lines. Make sure you use forward slashes '/' and not back slashes '\':

   FcgidInitialEnv PHPRC "c:/php-5.3.6"
   AddHandler fcgid-script .php
   FcgidWrapper "c:/php-5.3.6/php-cgi.exe" .php
   

   Make sure mod_fcgid.so is loaded and the ExecCGI option set as described previously in the section Installing Apache on Windows XP.

7. Restart the Apache Server so that you can test your PHP installation.

   If you have errors, double check your httpd.conf and php.ini files. Make sure you rebooted the computer after installing Oracle Database so that the PATH environment variable includes the Oracle libraries.

Installing PHP on Linux

If your Linux distribution has PHP 5.3 packages it is easiest to use them. Alternatively you can build PHP from source code.

This section contains the following topics:

• Installing PHP and OCI8 on Oracle Linux

• Adding the OCI8 Extension to an Existing PHP Installation on Linux

• Manually Building PHP and OCI8 Together on Linux

# yum install php53 php53-gd

# up2date php53 php53-gd

# yum install php53-oci8-11gR2

# up2date php53-oci8-11gR2

extension=oci8.so

Post PHP Installation Tasks on Windows and Linux

Once PHP is installed, make sure the php.ini configuration file has the following settings.

1. Setting the time zone is a new requirement of PHP 5.3.

   date.timezone = America/Los_Angeles
   

2. For testing it is helpful to set PHP's display_errors to show messages in the output text, instead of hiding them in Apache log files. In php.ini locate the display_errors setting and change it to On if necessary. If the directive does not exist, add the line:

   display_errors = On
   

3. The DRCP connection class should be configured in php.ini. The name is a user-chosen string to distinguish this application from any others that also use DRCP. If the class is not set, Oracle will not be able to share the DRCP pool effectively.

   Add a class name entry, such as:

   oci8.connection_class = ACXE
   

   Save the configuration file.

   If you are on Linux, make sure you have also done the steps in the earlier section Setting the Oracle Environment for Apache on Linux.

4. Restart Apache.

Checking PHP Configuration with phpinfo()

First, review the Apache error file error_log (in /var/log/httpd, /usr/local/apache/logs or C:\Program Files\Apache Software Foundation\Apache2.2\logs) to confirm there are no startup errors from Apache or PHP.

Decide where you want to create the PHP project files. This directory should be Apache accessible. For example on Linux you could use the Apache document root (/var/www/html or /usr/local/apache/htdocs) or $HOME/public_html if you configured a user directory. On Windows use the Apache document root C:\Program Files\Apache Software Foundation\Apache2.2\htdocs

In an editor create a new PHP file pi.php containing:

<?php
    phpinfo();
?>

Load this file in a browser:

http://localhost/pi.php

Check the following:

• If you use a non default port such as 8888 change the URL to include it, for example http://localhost:8888/pi.php

• If your file is in $HOME/public_html then change the URL to include your user name, for example http://localhost/~chris/pi.php

• Or use both, for example http://localhost:8888/~chris/pi.php

You should see a page like:

If you see the text of the file echoed back it means you didn't configure Apache to send PHP files to PHP. Apache's http.conf file needs a line AddType application/x-httpd-php .php on Linux or AddHandler fcgid-script .php on Windows.

Correct phpinfo() output shows the php.ini location and if it was loaded. If it shows no php.ini loaded, then revisit earlier steps, copy a sample php.ini file to the correct location and follow the steps in the section "Post PHP Installation Tasks on Windows and Linux".

Scroll down to the OCI8 section. You should see

If there is no OCI8 section, check that you installed OCI8. If you installed it as a shared library, check php.ini has extension=oci8.so or extension=php_oci8_11g.dll on Windows, and that the phpinfo() output shows extension_dir set to the directory where the OCI8 library was installed.

Testing PHP Connections to Oracle

To check that the OCI8 extension works, create a new PHP file testoci8.php containing:

<?php
 
$c = oci_connect('hr', 'welcome', 'localhost');
if (!$c) {
    $m = oci_error();
    trigger_error('Could not connect to database: '. $m['message'], E_USER_ERROR);
}
$s = oci_parse($c, "SELECT * FROM employees");
if (!$s) {
    $m = oci_error($c);
    trigger_error('Could not parse statement: '. $m['message'], E_USER_ERROR);
}
$r = oci_execute($s);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not execute statement: '. $m['message'], E_USER_ERROR);
}
$r = oci_fetch_all($s, $res);
if (!$r) {
    $m = oci_error($s);
    trigger_error('Could not fetch rows: '. $m['message'], E_USER_ERROR);
}
echo "<table border='1'>\n";
foreach ($res as $row) {
    echo "<tr>\n";
    foreach ($row as $item) {
        echo "  <td>".($item!==null?htmlentities($item, 
                       ENT_QUOTES):"&nbsp;")."</td>\n";
    }
    echo "</tr>\n";
}
echo "</table>\n";
 
?>

Everything between the <?php ?> tags will be processed by PHP and its output sent to the user's browser. Text outside of the tags will be sent to the user's browser verbatim. This includes leading and trailing white space. Files can have multiple sets of tags. Some applications use this to embed snippets of PHP inside HTML content. However a PHP-centric application will commonly use PHP echo or print statements to print any needed HTML tags.

How the OCI8 function calls work is described in the section General Example of Running SQL in PHP OCI8 and throughout the remainder of this manual.

Load the following file in a browser:

http://localhost/testoci8.php

Depending how you installed Apache, you may need to use one of these alternatives:

• http://localhost:8888/testoci8.php

• http://localhost/~chris/testoci8.php

• http://localhost:8888/~chris/testoci8.php

You should see:

If you get a blank screen, check php.ini has display_errors = On. Reload the page and see if there was an error.

If you get an error ORA-28000: the account is locked, then unlock the HR account using the steps given previously in section Unlocking the HR User.

If you get a startup error on Linux like

Warning: oci_connect():OciEnvNlsCreate() failed then check that ORACLE_HOME was set correctly before Apache was started.

If you get an error like ORA-12541: TNS:no listener review the Oracle Database installation log and find the port that Oracle Database listener was installed with. For example if you used 1522 then change the connect call in testoci8.php to:

$c = oci_connect('hr', 'welcome', 'localhost:1522');

If you get an error ORA-01017: invalid username/password, then change the oci_connect() call to use the password you assigned to HR in the section Unlocking the HR User.

Installing NetBeans IDE on Windows

To install NetBeans IDE on Windows, perform the following steps:

1. Download NetBeans 7.0 with the Java SE Development Kit from the following location:

   http://www.oracle.com/technetwork/java/javase/downloads/jdk-netbeans-jsp-142931.html

2. In Windows Explorer, locate the downloaded file jdk-6u25-nb-7_0-windows-ml.exe and run it by double clicking it.

3. After installation, start NetBeans by navigating to Start, then All Programs, then NetBeans, and then NetBeans IDE 7.0.

4. From Run Tools, select Plugins.

5. Go to the Available Plugins tab and select all the options in the PHP category.

6. Click Install and agree to the license and location.

7. Restart the IDE when prompted.

Installing NetBeans IDE on Linux

To install NetBeans IDE on Linux, perform the following steps:

1. Download the NetBeans 7.0 PHP bundle from the following Web site:

   http://netbeans.org/downloads/

2. On Linux open a terminal window and run the installer:

   # sh netbeans-7.0-ml-php-linux.sh
   

3. If your JDK is not installed in a default location, specify the path when installing, for example:

   # sh netbeans-6.9-ml-php-linux.sh --javahome $HOME/jdk1.6.0_24
   

4. Accept the license

5. Confirm the install directory.

6. Confirm the location of your JDK.

NetBeans will install.

If you do not already have a JDK on your computer, download a bundle of NetBeans 7.0 that includes the JDK from

http://www.oracle.com/technetwork/java/javase/downloads/jdk-netbeans-jsp-142931.html

This bundle does not have PHP enabled. To enable PHP, go to Tools and run Plugins. Go to the Available Plugins tab and select all the options in the "PHP" category. Click Install and agree to the license and location. Restart the IDE when prompted.

Configuring NetBeans on Linux and Windows

Perform the following steps to configure NetBeans on Linux and Windows.

1. Start NetBeans by using the desktop icon or menu entry. Navigate to Tools and then Options.

2. In the General options choose your preferred browser.

3. In the PHP options, go to the General tab.

4. Set the PHP 5 interpreter to your PHP command line executable, for example /usr/bin/php, /usr/local/bin/php, or C:\php-5.3.6\php.exe.

Using NetBeans

Create a new PHP Project as follows:

1. Navigating to File, then New Project, or clicking the New Project Icon, or using Ctrl + Shift + N.

2. Choose PHP and PHP Application.

3. Change the Project name to ACXE.

4. Choose your folder where the source files will be kept. For ease of install, use a directory that Apache can access such as /home/chris/public_html/ACXE.

5. Set the PHP Version to PHP 5.3

6. Set the project URL to map the directory where the sources are located. For example, http://localhost:8888/~chris/ACXE/, if you use a non default Apache port. Do not use a framework.

7. The project will be created and an index.php file will be set to the default NetBeans template.

   
   

8. Put some content in index.php to verify it can be run. Inside the <?php ?> tags, replace the comment line // put your code here with

   echo "hi";
   

9. Run the file by selecting Run Project from Run, or press F6. A browser window will open with the newly added message:

   
   

10. If the URL is not correct and does not map to the file on disk, right click the project name in the Project navigator and select Properties. Select Run Configuration and change the Project URL.

Throughout the rest of this manual you can add files to the ACXE project as needed by selecting the project name in the Project navigator and then going to File then New File, or using the New File icon, or Ctrl + N.