Skip navigation.

ADOdb and PEAR DB compared

PEAR DB is the default database abstraction library for PEAR. ADOdb is a high end database abstraction library modelled on Microsoft's ADO that is also very popular.

1. Feature Comparison
Where we try to put you to sleep by showing you the similarities between PEAR DB and ADOdb

2. Features Missing from PEAR DB
Now we try to wake you up with some teasers

3. Criticisms of ADOdb
Read what other people are moaning and complaining about

Methodology: I went to the PEAR Manual on 16 Nov 2002 and enumerated all the functions and then compared it with ADOdb. Then I list all functionality in ADOdb that is not available in PEAR DB. This implies that undocumented features of PEAR DB will not be covered here. Unfortunately I don't have the time to dig further into PEAR DB currently. Post a reply if I missed out anything.

1. Feature Comparison based on PEAR DB docs

Feature PEAR DB ADOdb
DSN support Yes Yes if PEAR compatibility mode is enabled
Error Handling PEAR

Extensible error-handling module. Built-in support for 3 error handlers.
- PEAR style
- Using error_log
- Display on screen

Connecting to the Database DB::Connect( )

ADOConnection::Connect( )
ADOConnection::PConnect( )
ADOConnection::NConnect( )

Rather than reinvent the wheel, it is modelled on standard PHP connect conventions.

Disconnecting from the Database

DB::Disconnect( )

ADOConnection::Close( )
Sending SQL Statements

DB::Query( )

I have often wondered why the PEAR developers choose this name, because you can also send DML and Insert and Update statements too.

ADOConnection::Execute( )
Identify Warnings DB::isWarning( ) Warnings are sent to the ADOConnection::outp( ) function, but not sent to the Error Handler. Default behaviour of outp( ) is output to screen. This can be overridden.
Identify Errors DB::isError( ) Errors are sent to the error handler.
Quote strings DB::quote() ADOConnection::qstr( )
Determine whether a backend supports a particular feature. DB::provide( )

We provide an extensive list of properties that are true or false in the ADOConnection object. They typically begin with $has*.

Auto-insert or update record DB::autoPrepare( )

ADOConnection::GetInsertSQL( )
ADOConnection::GetUpdateSQL( )

Execute prepared query DB::execute( ) ADOConnection::Execute( )
Execute prepared query multiple times DB::executeMultiple( ) Call ADOConnection::Execute( ) in a for loop.
Generate a limited query (eg. SELECT ... LIMIT ...) DB::limitQuery( ) ADOConnection::SelectLimit( )
Get one first column, first row of query DB::getOne( ) ADOConnection::GetOne( )
Get first row of query DB::getRow( ) ADOConnection::GetRow( )
Get a single column of query in array DB::getCol( ) ADOConnection::GetCol( )
Return result-set as an associative array using 1st col as key DB::getAssoc( ) ADOConnection::GetAssoc( )
Return recordset as a 2-D array DB::getAll( ) ADOConnection::getAll( ) or ADOConnection::GetArray( )
Affected rows from insert/delete/update DB::affectedRows( ) ADOConnection::Affected_Rows( )
Determine whether rows are fetched as indexed or associative arrays, or as objects DB::setFetchMode( )

Set the $ADODB_FETCH_MODE variable for indexed or associative arrays. For fetching rows as objects, use ADOResultSet::FetchObject( ).

Get the next number in a sequence DB::nextID( )

ADOConnection::GenID( ) or
ADOConnection::NextID( )

Create a sequence DB::createSequence( )

Automatically created for you. However in ADOdb 2.60 you can create it manually using:
ADOConnection::CreateSequence( )

Drop a sequence DB::dropSequence( )

Available in ADOdb 2.60
ADOConnection::DropSequence( )

Get internal DB info DB::getListOf( ) Available as ADOConnection properties
Get the next row DB_Result::fetchRow( ) ADORecordSet::FetchRow( ) or
ADORecordSet::MoveNext( )
Fetch the row into existing variable

DB_Result::fetchInto( )

Personally I think that this is an impractical function because it returns a PEAR Error object on EOF. Obviously many people agree because later they implemented fetchRow which just return false on EOF.

ADORecordSet::FetchRow( )

Get the number of columns returned DB_Result::numCols( ) ADORecordSet::FieldCount( )
Get the number of rows returned DB_Result::numRows( ) ADORecordSet::RecordCount( )
Get next result if batch of queries executed DB_Result::nextResult( ) ADORecordSet::NextRecordSet( )
Get info about recordset

DB_Result::tableInfo( )

Another strange name, as the recordset data could be from a view, or a join of multiple tables.

ADORecordSet::FetchField( )
Error object

DB_Error

Pear provides an meta-error mechanism where all errors are mapped into a virtual error list. The problem with this approach is that error messages are inevitably simplified so that debugging the actual cause of the error is significantly more difficult.

PEAR Error object supported if PEAR emulation enabled.

In general, debugging in ADOdb is easier thanks to the ADOConnection::debug property (set to true to debug). Set it to true and all SQL statements, debugging and error messages are outputted to standard output. PEAR DB has no equivalent - you have to build the debugging framework yourself.

Warning object DB_Warning No equivalent required.
Conclusion

PEAR DB is close to being just a subset of ADOdb. But wait, there's more...

 

2. Features Missing from PEAR DB

Feature PEAR DB ADOdb
Cached Recordsets Implemented as part of PEAR Cache, but not so powerful nor flexible nor documented so well as it is not integrated into PEAR DB. Yes, extensive support for caching.
HTML generation No Generation of html tables from a recordset with next, previous, first and last page links. Also able to generate <SELECT> popup menus based on a query.
Handling of Blobs No

Yes, using ADOConnection::UpdateBlob( ) and UpdateBlobFile( ). All blobs are auto-decoded on download without any special coding.

List databases No ADOConnection::MetaDatabases( )
List tables in database No ADOConnection::MetaTables( )
List fields in table Partial - hacked from DB_Result::tableInfo( ) ADOConnection::MetaColumns( )
List of primary keys of table No ADOConnection::MetaPrimaryKeys( )
Provides info on outer join syntax No Yes, ADOConnection has properties that store the left and right outer join operator syntax.
Generates SQL for cross-tabulations or pivot tables No Yes
Code to provide database backed session variables No Yes, two versions. One with encrypted data, one without.
Has code to support multiple database variations Limited Extensive range to support different database versions. We have 4 different oracle drivers, 4different microsoft sql server drivers, 2 mysql drivers, 2 postgresql drivers, 2 informix drivers, and 3 interbase drivers.
Support for multiple data types No

Yes, we special case the DATE data type and provide:

  • Conversion to unix integer timestamps with ADOConnection::UnixDate( ).
  • Conversion of unix timestamps back to database dates with ADOConnection::DBDate( ).
  • Date formating that is database independent
  • Date arithmetic support that is database independent
Type matching system between different databases No Yes, with ADORecordSet::MetaType( ), so that similar data type in different databases, such as TIMESTAMP and DATETIME are treated as equivalent types.
Return the last auto-increment number. No Yes. Use ADOConnection::InsertID( ).
Integrated Debugging Mode No When you set $connection->debug = true, an integrated debugging output mode is enabled, which sends all SQL statements, warnings, and errors to standard output. This can be overridden to send to a custom logging module.
Portable Stored Procedure Support No Yes. Provides Parameter( ) and SPPrepare( ) functions for preparing stored procedures.
Replace function available No Yes. When updating a record, we check if the record exists, and if it does not, we insert the record instead, using ADOConnection::Replace( ).

3. Criticisms of ADOdb

Criticism ADOdb
Does not generation of DML statements (create table, create index)

This is an obsolete criticism. We now offer automatic generation of database schemas using XML or text scripts.

HTML Generation should be outside a database abstraction library Most of the HTML generation is implemented as subsidiary include files, and will not be compiled in if not used.
ADOdb source code formating is terrible Yes, I've had people say that ADOdb is a bunch of crap because of this. Set tabs to 4 and see the difference. Sorry I don't follow your coding style, but as I get older, the closer my code get to K&R style.
ADOdb's Blob model is simplistic.

Yes, a design decision was made to simplify the Blob Management API to make it very easy to use. The limitations of this approach are that you might have to use database specific functions if your Blob does not fit into main memory.

ADOdb's data type system does not offer complete matching of database types

We already do this for the only data type that is unusual, dates (because PHP does not have a date data type). However some people advocate providing special type handling for integer, boolean, string, etc, but this is not needed as other data types are automatically mapped to the existing PHP type system correctly.