PHP

Oracle 7 and 8 Through the OCI8 Interface

Oracle is well-supported with PHP functions, and seven key functions are listed here. Example 4-14 shows these functions implemented in a modified version of Example 4-1. The functions require that Oracle 8 client libraries be installed and the functions use the Oracle 8 Call Interface (OCI8). Support for previous versions of Oracle is available through a separate function library we don't discuss here.

Oracle access is a six-step process. A connection is opened, and then a query is first prepared with OCIParse( ) and executed with OCIExecute( ). Then, each row is retrieved with OCIFetch( ) and individual attributes are retrieved from the row with OCIResult( ). Last, the connection is closed. Our treatment of Oracle functions is brief, and more detail can be found in the PHP manual.

The key functions are:

resource OCILogon(string username, string password, string database)

Establishes a connection to an Oracle DBMS. On success, the function returns a connection handle that can access databases through subsequent commands. Parameters are the same as those for mysql_connect( ).

resource OCIParse(resource connection, string SQL_command)

Returns a query resource handle that can subsequently be executed, or returns false on error. The connection resource created with OCILogon( ) is passed as a parameter, along with an SQL_command. The function doesn't execute the query-OCIExecute( ) does that-but this function is required to set up the query for execution.

int OCIExecute(resource query_handle)

Runs the query set up with OCIParse( ), taking the return value of OCIParse( ) as the only parameter. Results are subsequently fetched with OCIFetch( ). Returns true on success and false on failure.

int OCIFetch(resource query_handle)

Buffers a row from the last OCIExecute( ) call specified with the query_handle returned from OCIParse( ). Returns true if a row is retrieved and false when no more rows are available. Attributes are fetched from this buffer with OCIResult( ).

int OCINumCols(resource query_handle)

Returns the number of attributes associated with the query specified in OCIParse( ).

mixed OCIResult(resource query_handle, int attribute_number)

Fetches the value of attribute_number from the current row retrieved with OCIFetch( ). Takes the return result of OCIParse( ) as the first parameter.

int OCILogoff(resource connection)

Closes an Oracle connection opened with OCILogon( ).

Example 4-14. Connecting to an Oracle data source with PHP
<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wines</title>
</head>
<body><pre>
<?php
   // (1) Open the database connections
   $connection = OCILogon("fred","shhh", "winestore");
   // (2) Setup the query on the winestore through the
   // connection
   $query = OCIParse($connection, "SELECT * FROM
                              wine");
   // (3) Run the query
   OCIExecute($query);
   // (4) Output the results
   while (OCIFetch($query))
   {
     // (5) Print out the attributes in this row
     for($x=1;$x<=OCINumCols($query);$x++)
        echo OCIResult($query,$x);
     echo "\n";
    }
   // (6) Close the database connection
   OCILogoff($connection);
?>
</pre>
</body>
</html>