William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Database Abstraction with MDB2 from PEAR – Basic Usage

After installing XAMPP, the pear library is auto installed, we can check from xampp control panel -> pearinfo(), where all the PEAR information are listed. Inside, there are 20 installed packages, channel pear.php.net. We focus on MDB2.

PEAR MDB2 is a merge of the PEAR DB and Metaabase php database abstraction layers.
It provides a common API for all supported RDBMS. The main difference to most other DB abstraction packages is that MDB2 goes much further to ensure portability.

To use MDB2, We need to establish a database connection. Connections are created with a Data Source Name, or DSN. This is a simple URL-like string that defines your connection. If you’ve used DBI with Perl or ODBC, this will look pretty familiar. Many of the pieces are optional, or will use defaults if not specified.

The basic format:
[DRIVER]://[USER]:[PASSWORD]@[PROTOCOL]+[HOST]/[DATABASE]?[OPTIONS]

Standard MySQL DSN:
mysql://user:password@localhost/mydata

Postgres on an unusual port
pgsql://user:pass@tcp(localhost:4179)/database

Once you’ve created your DSN, you’ll connect to the database and instantiate an MDB2 object. This will act as your database handle, and be used for all future interactions with the database in your code. Three methods are provided for creating this connection:

  • MDB2::factory() – Creates a new MDB2 object and returns it. Until you actually make queries, the actual connection is not established. Makes efficient use of your database resources.
  • MDB2::connect() – Creates a new MDB2 object, and establishes a connection.
  • MDB2::singleton() – Returns an MDB2 connection for the provided DSN. If the same DSN is requested again, the connection will be reused and returned. This is preferable to creating a global database variable.

$dsn = “mysql://user:password@localhost/mydata”;
$db = MDB2::factory($dsn);

Operations with MDB2 can create an error. These are dealt with in the PEAR manner of returning a PEAR_Error class, and should be checked with the PEAR::isError() method. The getMessage() method on the error will provide a string detailing the particular error that occurred.

$db = MDB2::factory($dsn);

if (PEAR::isError($db)) {
die(“There was a connecting to the database: ” .
$db->getMessage());
}

Now that you have your connection, and have verified that the connection was successful, you’ll want to execute some queries. Simple queries can be run with the query() method on your connection. Even if you don’t expect a result set, you should still capture the return value and check for errors:
$result = $db->query(“DELETE FROM users WHERE username=’cdmoyer'”);

if (PEAR::isError($result)) {
printf(“There was an error deleting cdmoyer: %s”,
$result->getMessage());
}

For SELECT statements, the result will allow you to both check for errors and retrieve the generated rows. The result includes a fetchRow() method which will return rows until the last row has been retrieved, and then will return false. fetchAll() is also provided, which will return an array of every row in the dataset. Other more specialized methods are provided for fetching individual columns from the data set.

      $db->setLimit(10);
      $result = $db->query("SELECT * FROM news WHERE category='3'");
      
      if (PEAR::isError($result)) {
        print "Sorry, there is no news at this time.";
      }
      else {
        while ($news = $result->fetchRow(MDB2_FETCHMODE_ASSOC)) {
          printf('%s: %s', $news['author'], $news['title']);
        }
      }
      

In this example, we see two important things, limits and the “fetch mode.” setLimit() on the MDB2 connection provides an abstract way to limit the size of your dataset. In MySQL you’d simply write “LIMIT 10”, but that syntax does not work with all databases. Using the abstract method ensures your code will work in all the environments where MDB2 will work. The “fetch mode” is passed to fetchRow() and determines the type of array returned.

MDB2_FETCHMODE_ASSOC returns an associative array with the column names as keys, whereas MDB2_FETCHMODE_ORDERED returns an ordered array with the columns in the order returned from the database. This mode is often slightly more efficient, but requires care in specifying the rows you wish to retrieve, lest you later change the database structure and wonder why your code is suddenly broken.

Finally, if you finish with the database you should disconnect. Normally PHP will clean up any open handles when the page finishes, but it is good practice to clean up.
$db->disconnect();

If you know anything about web application security, you know that SQL injection is one of the most common vulnerabilities. These security holes are created when the program accepts data input from an outside source and places it into the raw text of a query. PHP is particularly notorious for these vulnerabilities because the standard MySQL library does not provide a prepared statement facility. It is left to the programmer to sanitize and escape all data.

MDB2 provides a prepared statement layer which works on top of all supported libraries. If you’re not familiar with the concept, you can think of prepared statements as a query template. Rather than building the query out of a series of concatenated strings, the programmer specifies what query they want to run, and the library takes care of replacing the data into the query string in a properly escaped manner.

      $db = MDB2::connect($dsn);
      if (PEAR::isError($db)) {
        die('Database connection error: ' . $db->getMessage());
      }
      
      $query = $db->prepare('INSERT INTO news (title, author) VALUES (?, ?)',
                             MDB2_PREPARE_MANIP);
      $result = $query->execute(array("New PEAR Article", "Chris Moyer"));
      if (PEAR::isError($result)) {
        die('Could not insert row');
      }
      
      $query = $db->prepare('SELECT * FROM news WHER author=?',
                             MDB2_PREPARE_RESULT);
      
      $result = $db->execute(array('Chris Moyer'));
      

In this example, you’ll notice several things. A question mark is used to denote the replaceable data passed in to your query. This data is then passed to the execute statement as an array of replacement values. Execute will take care of converting those to the proper string format for your database. Additionally, you must tell prepare() whether you are preparing a manipulation statement (INSERT, UPDATE, DELETE) or a result (SELECT) as some databases require that parameters to these statements be formatted differently.

Conclusion
The features outlined in this article simply scratch the surface, as MDB2 strives to abstract nearly every feature you’d want from modern database library. MDB2 may not always be the answer. But, if the question is, “How can I ensure my application runs on three different database platforms with minimal code modification?” MDB2 is a good answer.

Advertisements

One response to “Database Abstraction with MDB2 from PEAR – Basic Usage

  1. bet365 12/15/2010 at 4:20 am

    how are you I was luck to discover your website in digg
    your Topics is outstanding
    I obtain a lot in your blog really thanks very much
    btw the theme of you blog is really magnificentsuper
    where can find it

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: