Using ADOdb with PHP and Oracle: an advanced tutorial

1. Introduction

Oracle is the most popular commercial database used with PHP. There are many ways of accessing Oracle databases in PHP. These include:

The oracle extension

The oci8 extension

PEAR DB library

ADOdb library

The wide range of choices is confusing to someone just starting with Oracle and PHP. I will briefly summarize the differences, and show you the advantages of using ADOdb.

First we have the C extensions which provide low-level access to Oracle functionality. These C extensions are precompiled into PHP, or linked in dynamically when the web server starts up. Just in case you need it, here's a guide to installing Oracle and PHP on Linux.

Oracle extension

Designed for Oracle 7 or earlier. This is obsolete.

Oci8 extension

Despite it's name, which implies it is only for Oracle 8i, this is the standard method for accessing databases running Oracle 8i, 9i or 10g (and later).

Here is an example of using the oci8 extension to query the emp table of the scott schema with bind parameters:

Yes. You can install the optional ADOdb extension, which reimplements the most frequently used parts of ADOdb as fast C code. Note that the source code version of ADOdb runs just fine without this extension, and only makes use of the extension if detected.

PEAR DB is good enough for simple web apps. But if you need more power, you can see ADOdb offers more sophisticated functionality. The rest of this article will concentrate on using ADOdb with Oracle. You can find out more about connecting to Oracle later in this guide.

ADOdb Example

In ADOdb, the above oci8 example querying the emp table could be written as:

2. ADOdb Query Semantics

You can also query the database using the standard Microsoft ADO MoveNext( ) metaphor. The data array for the current row is stored in the fields property of the recordset object, $rs.
MoveNext( ) offers the highest performance among all the techniques for iterating through a recordset:

Array Fetch Mode

When data is being returned in an array, you can choose the type of array the data is returned in.

Numeric indexes - use $connection->SetFetchMode(ADODB_FETCH_NUM).

Associative indexes - the keys of the array are the names of the fields (in upper-case). Use $connection->SetFetchMode(ADODB_FETCH_ASSOC).

Both numeric and associative indexes - use $connection->SetFetchMode(ADODB_FETCH_BOTH).

The default is ADODB_FETCH_BOTH for Oracle.

Caching

You can define a database cache directory using $ADODB_CACHE_DIR, and cache the results of frequently used queries that rarely change. This is particularly useful for SQL with complex where clauses and group-by's and order-by's. It is also good for relieving heavily-loaded database servers.

This example will cache the following select statement for 3600 seconds (1 hour):

There are analogous CacheGetArray(
), CacheGetRow( ), CacheGetOne( ) and CacheSelectLimit( ) functions. The first parameter is the number of seconds to cache. You can also pass a bind array as a 3rd parameter (not shown above).

There is an alternative syntax for the caching functions. The first parameter is omitted, and you set the cacheSecs
property of the connection object:

3. Using Prepare( ) For Frequently Used Statements

Prepare( ) is for compiling frequently used SQL statement for reuse. For example, suppose we have a large array which needs to be inserted into an Oracle database. The following will result in a massive speedup in query execution (at least 20-40%), as the SQL statement only needs to be compiled once:

4. Working With LOBs

Oracle treats data which is more than 4000 bytes in length specially. These are called Large Objects, or LOBs for short. Binary LOBs are BLOBs, and character LOBs are CLOBs. In most Oracle libraries, you need to do a lot of work to process LOBs, probably because Oracle designed it to work in systems with little memory. ADOdb tries to make things easy by assuming the LOB can fit into main memory.

ADOdb will transparently handle LOBs in select statements. The LOBs are automatically converted to PHP variables without any special coding.

For updating records with LOBs, the functions UpdateBlob( ) and UpdateClob( ) are provided. Here's a BLOB example. The parameters should be self-explanatory:

Note that LogError( ) is a user-defined function, and not part of ADOdb.

Inserting LOBs is more complicated. Since ADOdb 4.55, we allow you to do this
(assuming that the photo field is a BLOB, and we want to store $blob_data into
this field, and the primary key is the id field):

5. REF CURSORs

Oracle recordsets can be passed around as variables called REF Cursors. For example, in PL/SQL, we could define a function open_tab that returns a REF CURSOR in the first parameter:

TYPE TabType IS REF CURSOR RETURN TAB%ROWTYPE;
PROCEDURE open_tab (tabcursor IN OUT TabType,tablenames IN VARCHAR) IS
BEGIN
OPEN tabcursor FOR SELECT * FROM TAB WHERE tname LIKE tablenames;
END open_tab;

In ADOdb, we could access this REF Cursor using the ExecuteCursor() function. The following will find
all table names that begin with 'A' in the current schema:

This reduces Oracle performance because Oracle will reuse compiled SQL which is identical to previously compiled SQL. The above example with the values inside the SQL
is unlikely to be reused. As an optimization, from Oracle 8.1 onwards, you can set the following session parameter after you login:

ALTER SESSION SET CURSOR_SHARING=FORCE

This will force Oracle to convert all such variables (eg. the 7900 value) into constant bind parameters, improving SQL reuse.

7. Dates and Datetime in ADOdb

There are two things you need to know about dates in ADOdb.

First, to ensure cross-database compability, ADOdb assumes that dates are returned in ISO format (YYYY-MM-DD H24:MI:SS).

Secondly, since Oracle treats dates and datetime as the same data type, we decided not to display the time in the default date format. So on login, ADOdb will set the NLS_DATE_FORMAT to 'YYYY-MM-DD'. If you prefer to show the date and time by default, do this:

Returns a string that is the equivalent of MySQL IFNULL or Oracle NVL.

Param($name)

Generates bind placeholders, using ? or named conventions as appropriate.

$db->sysDate

Property that holds the SQL function that returns today's date

$db->sysTimeStamp

Property that holds the SQL function that returns the current
timestamp (date+time).

$db->concat_operator

Property that holds the concatenation operator

$db->length

Property that holds the name of the SQL strlen function.

$db->upperCase

Property that holds the name of the SQL strtoupper function.

$db->random

Property that holds the SQL to generate a random number between 0.00 and 1.00.

$db->substr

Property that holds the name of the SQL substring function.

ADOdb also provides multiple oracle oci8 drivers for different scenarios:

Driver Name

Description

oci805

Specifically for Oracle 8.0.5. This driver has a slower SelectLimit( ).

oci8

The default high performance driver. The keys of associative arrays returned in a recordset are upper-case.

oci8po

The portable Oracle driver. Slightly slower than oci8. This driver uses ? instead of :bindvar for binding variables, which is the standard for other databases. Also the keys of associative arrays are in lower-case like other databases.

Here's an example of calling the oci8po driver. Note that the bind variables use question-mark:

9. Connecting to Oracle

Before you can use ADOdb, you need to have the Oracle client installed and setup the oci8 extension. This extension comes pre-compiled for Windows (but you still need to enable it in the php.ini file). For information on compiling the oci8 extension for PHP and Apache on Unix, there is an excellent guide at oracle.com.

Should You Use Persistent Connections

One question that is frequently asked is should you use persistent connections to Oracle. Persistent connections allow PHP to recycle existing connections, reusing them after the previous web pages have completed. Non-persistent connections close automatically after the web page has completed. Persistent connections are faster because the cost of reconnecting is expensive, but there is additional resource overhead. As an alternative, Oracle allows you to pool and reuse server processes; this is called Shared Server (also known as MTS).

The author's benchmarks suggest that using non-persistent connections and the Shared Server configuration offer the best performance. If Shared Server is not an option, only then consider using persistent connections.

Connection Examples

Just in case you are having problems connecting to Oracle, here are some examples:

a. PHP and Oracle reside on the same machine, use default SID, with non-persistent connections:

With ADOdb data source names,
you don't have to call Connect( ) or PConnect( ).

10. Error Checking

The examples in this article are easy to read but a bit simplistic because we ignore error-handling. Execute( ) and Connect( ) will return false on error. So a more realistic way to call Connect( ) and Execute( ) is:

You can retrieve the error message and error number of the last SQL statement executed from ErrorMsg( ) and ErrorNo( ). You can also define a custom error handler function.
ADOdb also supports throwing exceptions in PHP5.

Handling Large Recordsets (added 27 May 2005)

The oci8 driver does not support counting the number of records returned in a SELECT statement, so the function RecordCount()
is emulated when the global variable $ADODB_COUNTRECS is set to true, which is the default.
We emulate this by buffering all the records. This can take up large amounts of memory for big recordsets.
Set $ADODB_COUNTRECS to false for the best performance.

This variable is checked every time a query is executed, so you can selectively choose which recordsets to count.

11. Other ADOdb Features

Schema generation. This allows you to define a schema using XML and import it into different RDBMS systems portably.

Performance monitoring and tracing. Highlights of performance monitoring include identification of poor and suspicious SQL, with explain plan support, and identifying which web pages the SQL ran on.

12. Download

You can download ADOdb from sourceforge. ADOdb uses a BSD style license. That means that it is free for commercial use, and redistribution without source code is allowed.