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. |
| Connecting to the Database | DB::Connect( ) | ADOConnection::Connect( ) 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( ) |
| 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 |
| Create a sequence | DB::createSequence( ) | Automatically created for you. However in ADOdb 2.60 you can
create it manually using: |
| Drop a sequence | DB::dropSequence( ) | Available in ADOdb 2.60 |
| 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. |
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:
|
| 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. |