DB-2-MDB2

February 4th, 2006. Tagged: mdb2, mysql, PEAR

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!

Tell your friends about this post: Facebook, Twitter, Google+

74 Responses

  1. 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*();

  2. DB also supports an array for a DSN, it’s not an MDB2 only thing. In fact, both packages use an array internally.

  3. 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.

  4. I guess, in ‘Quoting values’ the mdb2 example should read $mdb2->quote(1, ‘integer‘) instead of $db->quote(..)

  5. Can I use MDB2 with DataObject ? It says so on the MDB2 PEAR page but I don’t know how to do it.

  6. 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.

  7. thanks a million. BOOKMARKED!!!

  8. 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
    }

  9. sorry, the above should be:
    $result1 = $db->queryAll(“select ….. “);
    for ($i = 0; $i

  10. @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

  11. 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?

  12. 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:

    $mdb2->setOption(
        'portability',
        MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL
    );
    
    
  13. 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.

  14. 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:

    $sql = 'SELECT "something", "something else", "3rd thing"';
    $data = $mdb2->extended->getRow($sql, array('integer', 'text', 'integer'));

    $data will be:
    array(0, ‘something else’, 3)

  15. Will ODBC connections work with MDB?
    In DB I could use DSN like:
    $dsn = odbc://$dbuname:$dbpass@$dbhost/$odbc_name;

  16. 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/

  17. 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?

  18. 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.

  19. [...] 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. [...]

  20. Great article. We recently decided to switch over from DB to MDB2, and this was a valuable translation reference.

  21. Two more changes required if using MDB2 error handling mechanisms:

    1. DB::isError() –> MDB2::isError()

    2. DB_common::setErrorHandling() –> MDB2_Driver_Common::setErrorHandling()

  22. 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.

  23. 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!

  24. Cant see the benefits of this migration. Only troubles and a lot of work.

  25. 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!

  26. 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.

  27. 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.

  28. Thank you very much for putting up this information.

  29. What about update statements?

  30. 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?

  31. Hi Sam, I don’t know if this is supported, I’ll try to figure something out.

  32. [...] 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. [...]

  33. 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…

  34. [...] 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. [...]

  35. I have to agree with Eugene Panin…. Why? Why is this change necessary?

  36. [...] 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. [...]

  37. [...] DB-2-MDB2 (tags: mysql php development) [...]

  38. [...] О статье Автор заметки Stoyan Stefanov, он известен своими статьями о PEAR (скорей всего вы сталкивались с его статьей DB to MDB2), MySQL и AJAX. Речь пойдет о раскладке AJAX под MVC (казалось бы, вещь элементарная, но на самом деле легко допустить ошибку) и о мини-фреймворке, который предлагает вам автор. Критика по перевод приветствуется. [...]

  39. 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!

  40. 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

  41. 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?

  42. 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#var$valid_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’ => ”,
    )

  43. 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

  44. 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

  45. 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);

  46. Hey, I just found this:
    $res->getDebugInfo()
    which seems to do what
    $db->errorNative()
    used to do in DB

  47. This is one of the most helpful sites I’ve seen in a long time! Thanks for doing this.

  48. thanks – very helpful article.
    it’s good when somebody puts efforts into knowledge to share it afterwards

  49. Thanx a lot! This is very useful.

  50. [...] 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. [...]

  51. Thanks for this article. I like your posts

  52. 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

  53. 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)

  54. Great writeup! I use it quite often! One area I would like to see expanded is the MDB2 error catching. Thanks!

  55. Thanks, this is great, you saved me a couple of headaches.

  56. 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.

  57. This wery nice documan thanks ( mdb + all subject)

  58. ( I have a problem with this code. I use mdb2 with utf8 unicode )

    This code error ?

  59. [...] 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. [...]

  60. Nice code. Will it work with all the encodings?

  61. 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.

  62. A dumb problem I had that hopefully will help someone:

    I would call $mdb2->supports(‘transactions’) and it would return false, despite having InnoDB tables.

    My code was the following:

    $db =& MDB2::factory($myDSN);
    echo $db->supports(‘transactions’); //returns false

    This was because, as described in this article, factory() doesn’t actually connect to the database.

    MDB2 also has a connect method. By using connect instead of factory, I was able to call the supports() method right after creating my mdb2 module and have it return the correct value.

  63. This is one of the most helpful sites I’ve seen in a long time! Thanks for doing this weryy thankss

  64. [...] DB-2-MDB2 / phpied.com [...]

  65. We recently decided to switch over from DB to MDB2, and this was a valuable translation reference.

  66. [...] | DB-2-MDB2) dp.SyntaxHighlighter.ClipboardSwf = “http://img.diimii.com/01/clipboard.swf”; [...]

  67. Hello
    I was wondering if anyone can tell me how to import a table or query into MYSQL on my website from my drive C on my computer using pear of other method?
    thanks
    Marty

  68. [...] 參考網址http://www.phpied.com/db-2-mdb2/ 本篇發表於 PHP & MySQL、未分類 由 stan。固定網址書籤。 [...]

  69. @khiem:

    Are you using BOTH
    ->setCharset(‘utf8′);
    AND
    utf8_encode()
    ?

    I just resolved some problems with unicode by using setCharset and abandoning utf8_encode(). Make sure they’re not both used together.

  70. [...] http://www.phpied.com/db-2-mdb2/ [...]

  71. is it possible to use mdb2 with php 5.3 ?
    i tried your included example and having some issue with declaration ( Strict Standards issue ) any possible fix for that ?

  72. Spot on with this write-up, I really feel this site needs far more attention.
    I’ll probably be returning to read through more, thanks for the info!

  73. Czytałem wpis na stronie poświęconej kuchni http://kuchniaodzaplecza.pl/w-sieci-przepisow/ i szczerze
    pytam, czy jest ktoś kto korzystał z publikowanych tu przepisów i myśli, że są one godne zaufania?
    Kocham przygotowywać jedzenie, lecz strasznie denerwuje mnie, gdy ktoś marnuje moje jedzenie.

  74. there’s also DB::escapeSimple(), should be changed to MDB2::escape()

Leave a Reply