DB-2-MDB2

Intro

Recently I had to move an existing project from using PEAR::DB to PEAR::MDB2 - the new database abstraction layer. I took notes on the parts of the code I needed to change, I hope they can benefit someone who's doing the same. Many thanks go to Lukas Smith, the lead developer, he was always responding very fast to my reports and questions in the PEAR mailing list.

One thing to notice in MDB2 is that it tries not to do any unnecessary work and does many things only on demand. For example when you create an object, that doesn't mean that a connection is established. It is established only when you make the first real database access, a SELECT for example.

I assume you have an idea of PEAR::DB, since this posting illustrates a DB-to-MDB2 endeavour, but even if you don't, I hope the posting will still be useful as an intro to DB and MDB2.

Including the libs

So first off, including the libs (I assume you have PEAR on your machine).

require_once 'DB.php';
require_once 'MDB2.php';

One thing to note here is that installing MDB2 doesn't install any of the database wrappers. So if you use MySQL for example, you'd need to install it separately:
pear install MDB2_Driver_mysql-beta
in addition to
pear install MDB2-beta

MDB2 is now a stable release! So you can now remove the "-beta" monkier when installing the packages.

DSN

Next - the DSN string. It's the same for MDB2 as for DB.

$dsn = 'mysql://root@localhost/db2mdb2';

BTW, MDB2 can also accept an array of all the connection details, as opposed to a DSN string. And so does DB (Thanks for the clarification, Justin!)

Creating instances

$db =& DB::connect($dsn);
$mdb2 =& MDB2::factory($dsn);

MDB2 provides a factory method to create an instance. At this time no database connection is yet established. MDB2 also provides a singleton() method to create an instance.

Fetchmode

It is the same in both DB and MDB2, just note the prefix of the constant.

// set fetchmode
$db->setFetchMode(DB_FETCHMODE_ASSOC);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);

Simple SELECTs

There are the methods to select one row, one column, one cell and a bunch of records. DB prefixes them with get, while MDB2 uses query.

// select several records and shove them into an array
$all = $db->getAll('SELECT * FROM people');
$all = $mdb2->queryAll('SELECT * FROM people');


// select one cell
$one = $db->getOne('SELECT name FROM people WHERE id = 1');
$one = $mdb2->queryOne('SELECT name FROM people WHERE id = 1');


// one row
$row = $db->getRow('SELECT * FROM people WHERE id = 1');
$row = $mdb2->queryRow('SELECT * FROM people WHERE id = 1');


// a column
$col = $db->getCol('SELECT name FROM people');
$col = $mdb2->queryCol('SELECT name FROM people');

Quoting values

In DB, the suggested method to quote is quoteSmart(). In MDB2 it's quote() and it accepts a second parameter, which tells the type of the value to be quoted. If the second parameter is omitted, MDB2 will try to guess the type.

$one = $db->getOne(
         'SELECT name FROM people WHERE id = ' 
         . $db->quoteSmart(1)
       );


$one = $mdb2->queryOne(
         'SELECT name FROM people WHERE id = ' 
         . $db->quote(1, 'integer')
       );

Sequence tables

If you use sequence tables, both libs will provide you with a nextId() method:

echo $db->nextId('people_db');
echo $mdb2->nextId('people_mdb2');

The only difference is that when DB creates a sequence table (with one field and one value), the name of the field is id, where MDB2 will use sequence. If you're translating an existing project to MDB2 like me, and the sequence tables are already created by DB, you have the option of renaming this field in the database for all sequence tables, or you can set an MDB2 option and you're good to go.

$mdb2->setOption('seqcol_name','id');

Auto execute

Say you have the data:

$data = array('id' => 5, 'name' => 'Cameron');

To auto-insert it using DB, you'd do:

$db->autoExecute('people', $data, DB_AUTOQUERY_INSERT);

For MDB2, the auto execution is probably not considered an often-used feature, so it's not in the base instance. You need to load an additional module to have access to it:

$mdb2->loadModule('Extended');

Now you can

$mdb2->autoExecute('people', $data, MDB2_AUTOQUERY_INSERT);

The above will work in PHP5 only. In PHP4, due to the limited support of object overloading (Thanks again to Lukas for clarifying this!), you'd need to do:

$mdb2->extended->autoExecute('people', $data, MDB2_AUTOQUERY_INSERT);

Note that the second way will also work in PHP5.

Prepared statements

In DB:

$statement = $db->prepare('INSERT INTO people VALUES (?, ?)');
$data = array(6, 'Chris');
$db->execute($statement, $data);
$db->freePrepared($statement);

In MDB it's almost the same, only that the statement becomes an object and you call its (as opposed to MDB2 main object's) methods to execute and to release memory:

$statement = $mdb2->prepare('INSERT INTO people VALUES (?, ?)');
$data = array(7, 'Dave');
$statement->execute($data);
$statement->free();

Execute multiple

The same applies to executing a statement with with multiple "rows" of data from an array. executeMultiple() is in the Extended MDB2 module, so you need to load it:

DB:

$statement = $db->prepare('INSERT INTO people VALUES (?, ?)');
$data = array(
    array(8, 'James'),
    array(9, 'Cliff')
);

$db->executeMultiple($statement, $data);
$db->freePrepared($statement);

MDB2:

$statement = $mdb2->prepare('INSERT INTO people VALUES (?, ?)');
$data = array(
    array(10, 'Kirk'),
    array(11, 'Lars')
);

$mdb2->loadModule('Extended');
$mdb2->extended->executeMultiple($statement, $data);

$statement->free();

Transactions

In DB:

$db->autoCommit();
$result = $db->query('DELETE people'); // will cause an error

if (PEAR::isError($result)) {
    $db->rollback();     //echo 'rollback';
} else {
    $db->commit();     //echo 'commit';

}

In MDB2 you have to check if transactions are supported in your RDBMS. Then during the transaction, you can always check "Am I in transaction?"

if ($mdb2->supports('transactions')) {
    $mdb2->beginTransaction();

}
$result = $mdb2->query('DELETE people');
if (PEAR::isError($result)) {
    if ($mdb2->in_transaction) {
        $mdb2->rollback();         // echo 'rollback';
    }
} else {
    if ($mdb2->in_transaction) {
        $mdb2->commit();         // echo 'commit';
    }
}

Example script

You can download a script that has the examples above and play with it. Here's also the sql file to recreate the database:

• db2mdb2.phps
• db2mdb2.sql

Any questions or comments are welcome 😉 Thanks for reading!

Tell your friends about this post on Facebook and Twitter