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:
Any questions or comments are welcome
Thanks for reading!

February 4th, 2006 at 11:28 am
Looks great. One thing to note about get*() versus query*(). The get*() methods support prepared statement placeholders. The query*() do not. If you need this behaviour you can also use the extended module again:
$mdb2->loadModule(’Extended’);
$mdb2->extended->get*();
February 4th, 2006 at 5:27 pm
DB also supports an array for a DSN, it’s not an MDB2 only thing. In fact, both packages use an array internally.
February 13th, 2006 at 8:18 am
Someone on the pear-general list recently asked why there is no affectedRows() method .. it should be noted that for DML statements the exec() method should be used. That method returns the number of affectedRows() instead of a result set object like the query() method.
March 7th, 2006 at 8:28 am
I guess, in ‘Quoting values’ the mdb2 example should read $mdb2->quote(1, ‘integer‘) instead of $db->quote(..)
March 9th, 2006 at 2:11 pm
Can I use MDB2 with DataObject ? It says so on the MDB2 PEAR page but I don’t know how to do it.
March 21st, 2006 at 11:38 am
I’ve found a little more complication migrating to mdb2… even if perhaps is due to my newbeness…
When I’ve ported my code from PEAR:DB to PEAR:MDB2 I’ve found that the associative array returned by queryAll, queryRow ecc… in PEAR:MDB2 are in lowercase; PEAR::DB was case sensitive… but in effect I prefer the choice used by PEAR::MDB2.
March 25th, 2006 at 12:56 pm
thanks a million. BOOKMARKED!!!
March 25th, 2006 at 1:11 pm
as for replacing fetchRow which is missing in MDB2
the following
$choiceRS = $db->query(”SELECT choiceid, choiceText FROM $ctablename WHERE qid = $qid”);
while ($c = $choiceRS->fetchRow(MDB2_FETCHMODE_ASSOC)) {
……
}
becomes now
$db->setFetchMode(MDB2_FETCHMODE_ASSOC);
$result1 = $db->queryAll(”SELECT qid FROM $tablename ORDER BY qid”);^M
for ($i = 0; $i “.$row1[qid].”";^M
}
March 25th, 2006 at 8:38 pm
sorry, the above should be:
$result1 = $db->queryAll(”select ….. “);
for ($i = 0; $i
March 26th, 2006 at 7:00 am
@Jesse: yes MDB2 works with DB_DataObject
@Paolo: this is a question of the portability mode you set. by default MDB2 goes portability all the way. you can control this by setting different portability mode.
Also .. anyone out there willing to lend a helping hand read this:
http://pooteeweet.org/blog/336
May 23rd, 2006 at 1:44 pm
I’m using MDB2 bindParamArray method. One of the array element is an empty string. The empty string is automatically converted into NULL. I do not want that to happen. How do I force MDB2 to preserve the empty string?
If not, how do I use NULL in bindParamArray to force a IS NULL in a prepared statement?
May 23rd, 2006 at 10:23 pm
Hi Sam, if you want the empty strings, exclude the MDB2_PORTABILITY_EMPTY_TO_NULL portability option.
For example if you want to have portability set to ALL but preserving the empty strings, you can do:
May 24th, 2006 at 11:05 am
Just a heads up:
The MDB2::extended::getXX methods are not the same as the DB::getXX methods. The new versions require an extra parameter before the query parameters array. I have no idea what it’s for though.
Also, the MDB2::query method does not allow query parameters.
May 28th, 2006 at 10:44 pm
Thanks, Sloat!
I believe the second parameter to MDB2->extended->get*() is the type. You know in MDB2 there is a set of portable types you can use accross different RDBMS. In this case, specifying the type will make any type conversions if you need them.
Consider this:
$data will be:
array(0, ’something else’, 3)
May 29th, 2006 at 8:48 am
Will ODBC connections work with MDB?
In DB I could use DSN like:
$dsn = odbc://$dbuname:$dbpass@$dbhost/$odbc_name;
May 29th, 2006 at 8:53 am
Hmm, I haven’t tried, but I don’t think so, because I don’t see an ODBC driver in the source tree:
http://cvs.php.net/viewcvs.cgi/pear/MDB2/MDB2/Driver/
June 4th, 2006 at 12:30 am
I’m using $mdb2->setFetchMode( MDB2_FETCHMODE_OBJECT, $objName )…This is not working for me. There’s nothing wrong with the query since when MDB2_FETCHMODE_ASSOC is used, data result is returned.
The $objName is a name of a class with defined properties mapping to fields in the db table. And there are get and set methods. What am I doing wrong. Need I do extra getters and setters in the class definition?
June 4th, 2006 at 1:03 am
Can you post more details, maybe just the skeleton of your class and how you use it?
When using object fetch mode, the default class is stdClass, so here’s an example how to do the same thing that stdClass does, but defining it as custom fetch class.
class My_Fetch_Class { function __construct($row) { foreach ($row as $field => $data) { $this->{$field} = $data; } } } $mdb2->setFetchMode(MDB2_FETCHMODE_OBJECT, 'My_Fetch_Class');The important thing is that the constructor of the custom class receives a $row array, the rest is up to you.
Also in your example $objName should be the name of the class, right? Not the actual object.
June 13th, 2006 at 2:22 pm
[...] Meanwhile my article on DB and MDB2 was published in the International PHP Magazine, the TOC is here. This article is an extended and improved version of the original DB-to-MDB2 blog posting you can find here, plus I’ve added an intro part in case you’ve never used DB or MDB2. [...]
June 18th, 2006 at 5:52 pm
Great article. We recently decided to switch over from DB to MDB2, and this was a valuable translation reference.
June 28th, 2006 at 6:02 am
Two more changes required if using MDB2 error handling mechanisms:
1. DB::isError() –> MDB2::isError()
2. DB_common::setErrorHandling() –> MDB2_Driver_Common::setErrorHandling()
July 12th, 2006 at 5:02 pm
Following up on Carlos’s comment, MDB2::factory does not return a PEAR error object, it returns its own. So doing the PEAR::isError check (as I do for DB) does not work.
July 23rd, 2006 at 10:31 pm
this page is very good. i am having a problem on working on mdb2, but now im converting all my pear:db to pear:mdb2.
thanks again!
July 24th, 2006 at 1:05 am
Cant see the benefits of this migration. Only troubles and a lot of work.
August 26th, 2006 at 6:39 am
Is it just me, or does the PEAR::isError() check not actually work? MDB2::factory() apparently returns false on error, but even a Boolean check doesn’t seem to work. How can I get this bloody MDB2 thing to error!
August 31st, 2006 at 10:38 am
Actually factory() will return an error object on error. I never returns false. Not sure whats going on there.
Also MDB2_Error’s can be checked via PEAR::isError(). Unless you specifically want to check if its an MDB2 error I recommend using PEAR::isError(). After all you usually do not care what PEAR package generated the error.
September 1st, 2006 at 2:54 pm
For those who used DB’s getAssoc and don’t want the overhead of the MDB2:Extended class, you can use the following:
$dbobj->queryAll(’SELECT * FROM X WHERE Y’,array(),NULL,TRUE);
The final parameter tells queryAll to arrange the array just like getAssoc.
October 21st, 2006 at 6:29 pm
Thank you very much for putting up this information.
October 30th, 2006 at 1:56 pm
What about update statements?
December 20th, 2006 at 6:40 pm
I’m currently setting up DB_DataObject on an existing website and will be using MDB2 as the db driver. The website has an existing custom database wrapper and so on every page view there is already a database connection. I would like to avoid making a second unnecessary database connection so I’m wondering if there is any way to tell MDB2 to use that existing connection or to pass that connection in?
December 21st, 2006 at 9:03 am
Hi Sam, I don’t know if this is supported, I’ll try to figure something out.
December 25th, 2006 at 2:44 pm
[...] Update: The new preferred database library is PEAR::MDB2 which is a merge of PEAR::DB and Metabase libraries. You will find migrating from DB to MDB2 fairly straight forward. There is even a migration guide. [...]
January 3rd, 2007 at 6:57 am
Migrando do PEAR::DB para o PEAR::MDB2…
Primeiro post de 2007
Há algum tempo estamos estudando a migração de nossos projetos no trabalho do PEAR::DB para o PEAR::MDB2. O MDB2 é uma abstração nova para bancos de dados (hoje, já não tão nova assim), e é um merge do PEAR::DB e d…
January 5th, 2007 at 12:07 pm
[...] This is a follow up to a question posted by Sam in my DB-2-MDB2 post. The question was if you can reuse an exisitng database connection you’ve already established and not have MDB2 creating a second connection. [...]
January 10th, 2007 at 1:02 pm
I have to agree with Eugene Panin…. Why? Why is this change necessary?
January 16th, 2007 at 11:05 pm
[...] Through a trackback I found out that Walter Cruz has translated my DB-2-MDB2 article in a language I was led to believe is Brazilian Portuguese. [...]
January 26th, 2007 at 12:18 pm
[...] DB-2-MDB2 (tags: mysql php development) [...]
January 29th, 2007 at 5:10 am
[...] О статье Автор заметки Stoyan Stefanov, он известен своими статьями о PEAR (скорей всего вы сталкивались с его статьей DB to MDB2), MySQL и AJAX. Речь пойдет о раскладке AJAX под MVC (казалось бы, вещь элементарная, но на самом деле легко допустить ошибку) и о мини-фреймворке, который предлагает вам автор. Критика по перевод приветствуется. [...]
February 22nd, 2007 at 4:05 am
I have lots of code written with DB. I have been planning to move most active projects to MDB2 or PDO. Your article will help me to do the conversions to MDB2. Thanks!
April 17th, 2007 at 8:59 pm
Regarding Paolo Sala’s comment:
I had the same problem. By default, MDB2 converts the column name keys of the associative arrays returned in MDB2_FETCHMODE_ASSOC to lower case. It turns out this is a portability feature that is enabled by default. The solution is to turn the case-fixing portability feature off, as described in the following link, under MDB2_PORTABILITY_FIX_CASE: http://pear.php.net/manual/en/package.database.mdb2.intro-portability.php
May 3rd, 2007 at 10:12 am
This is a helpful resource indeed! The note about the underdocumented necessity of turning off MDB2_PORTABILITY_EMPTY_TO_NULL put an end to several hours of torture for me.
Still not sure about the answer to the fundamental question: why? DB was getting the job done. The MDB2 package seems harder to understand and use. What’s the upside?
May 9th, 2007 at 7:45 am
Addon for Auto execute
I had some trouble using:
$mdb2->autoExecute(’people’, $data, MDB2_AUTOQUERY_INSERT);
message says: Native message: ERROR: column “Col_name” is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression.]
solution:
for each field in $data, give the type.
for example:
two varchar followed by 3 integers makes:
$types = array(’text’,'text’,'integer’,'integer’,'integer’);
$mdb2->autoExecute(’people’, $data, MDB2_AUTOQUERY_INSERT, $types);
For an explanation on $types:
http://pear.php.net/package/MDB2/docs/latest/MDB2/MDB2_Driver_Datatype_Common.html#varvalid_default_values
$valid_default_values = array(
‘text’ => ”,
‘boolean’ => true,
‘integer’ => 0,
‘decimal’ => 0.0,
‘float’ => 0.0,
‘timestamp’ => ‘1970-01-01 00:00:00′,
‘time’ => ‘00:00:00′,
‘date’ => ‘1970-01-01′,
‘clob’ => ”,
‘blob’ => ”,
)
May 11th, 2007 at 11:16 am
I’ve been using MDB2 for a couple of months now and am impressed with it’s general features. I’m thinking about extending it to handle ’shadow’ tables which I want to use to record an audit history of the changes made to a record.
The way I’d see this working is that immediately prior to an update query being executed, a copy of the affected row would be inserted into my shadow table. Ideally this would be handled automatically by the extended-MDB2 class based on desconstructing the update query passed in.
As an example with a table of company names, I’d end up with the following 3 tables (MySQL) defined:
CREATE TABLE tbl_companies (
id int(11) NOT NULL auto_increment,
name varchar(255) UNIQUE NOT NULL DEFAULT ”,
PRIMARY KEY (id)
) TYPE=InnoDB;
CREATE TABLE tbl_companies_seq (
sequence int(11) NOT NULL auto_increment,
PRIMARY KEY (sequence)
) TYPE=InnoDB;
CREATE TABLE tbl_companies_shadow (
uid int(11) NOT NULL auto_increment,
timestamp timestamp NOT NULL,
id int(11) NOT NULL DEFAULT 0,
name varchar(255) UNIQUE NOT NULL DEFAULT ”,
PRIMARY KEY (uid)
) TYPE=InnoDB;
Each company would have a single record in the tbl_companies table, and a record for each time it was updated in the tbl_companies_table.
Has anyone implemented anything like this?
Ian
August 14th, 2007 at 12:07 am
The MDB2::extended::getXX methods are not the same as the DB::getXX methods. The new versions require an extra parameter before the query parameters array
August 22nd, 2007 at 12:06 pm
Thanks for posting this guide! It was really useful.
You don’t mention errorNative()… I used this under DB to show more detailed error messages. I couldn’t find it under MDB2, so I just removed the calls and display the result of getMessage().
The symptom was a ‘Call to undefined function MDB2::errorNative’ error message which took a while to track down.
This code was useful to find the problem:
function showError($errno,$errstr,$filename,$linenum,$context) {
print “There has been an error in $filename at $linenum.”;
print $errstr.”;
print ”;
debug_print_backtrace();
print ”;
die();
}
set_error_handler(’showError’,E_ALL);
August 22nd, 2007 at 2:06 pm
Hey, I just found this:
$res->getDebugInfo()
which seems to do what
$db->errorNative()
used to do in DB
August 31st, 2007 at 4:20 pm
This is one of the most helpful sites I’ve seen in a long time! Thanks for doing this.
September 5th, 2007 at 8:41 am
thanks - very helpful article.
it’s good when somebody puts efforts into knowledge to share it afterwards
September 20th, 2007 at 12:43 am
Thanx a lot! This is very useful.
September 20th, 2007 at 11:45 pm
[...] According to one comment that I read, an additional option that might be good to set is MDB2_PORTABILITY_EMPTY_TO_NULL. At the moment though, I’m not 100% sure how to set both options at once. I found a workaround for the time being, but will post this as a bug in eventum so we can decide what to do about it. [...]
October 11th, 2007 at 5:37 am
Thanks for this article. I like your posts
November 23rd, 2007 at 5:38 am
Hi….
I am conpletely new to the PEAR thing and dont even know how to install the thing. I have uploaded all the files i got from the MDB2 file i downloaded to a shared hosting. But dont know what to do with it after that. OR even where to put the info for my database name and password etc….
Can anyone help
November 26th, 2007 at 5:44 am
Hi Joseph,
You could probably take a look at the PEAR manual for easy examples on how to get started with MDB2.
http://pear.php.net/manual/en/package.database.mdb2.php
You can also download this free chapter of the PEAR book, written by yours truly
http://www.packtpub.com/files/SampleChapter-PHP-Programming-with-PEAR.pdf
(you should jump to page 11 of the PDF)
December 14th, 2007 at 4:52 pm
Great writeup! I use it quite often! One area I would like to see expanded is the MDB2 error catching. Thanks!
January 7th, 2008 at 4:55 pm
Thanks, this is great, you saved me a couple of headaches.
April 16th, 2008 at 11:00 pm
Hi All!
I have a problem with this code. I use mdb2 with utf8 unicode.
require_once(’libs/PEAR/MDB2.php’);
$dsn = “mysql://root:123456@localhost/daianvn”;
$mdb2 = &MDB2::factory($dsn);
$mdb2->setCharset(’utf8′);
$smt = $mdb2->prepare(”select * from dasc_users where username=?”);
var_dump($smt);
$smt is error object instead statement_object. Please tell me why? When I remove $mdb->setCharset(’utf8′). It’s Ok.
May 26th, 2008 at 9:44 am
This wery nice documan thanks ( mdb + all subject)
May 26th, 2008 at 10:02 am
( I have a problem with this code. I use mdb2 with utf8 unicode )
This code error ?
June 10th, 2008 at 2:41 am
[...] Note: MDB2 is their current database package. It’s quite useful and easy to learn. See http://www.phpied.com/db-2-mdb2/ for a quick tutorial. [...]
July 29th, 2008 at 3:54 pm
Nice code. Will it work with all the encodings?
July 31st, 2008 at 2:47 am
Hi. You know what guys? I think this is very good idea, but I am affraid but not for me
I like to cook very at home. Greetings from Poland.