Using the PEAR Database Class


In addition to the API we have already discussed, PHP also allows access to SQLite databases via the PEAR Database class. We will show briefly how to use this with SQLite.

The PEAR class provides a unified API for accessing SQL-based databases. It supports SQLite as well as many other popular databases such as Microsoft SQL Server, MySQL, Oracle, and PostgreSQL, as well as ODBC connectivity.

The database-abstraction approach allows you to create scripts that can work on a wide range of database engines simply by changing the connection parameters. The same set of functions is made to work with whatever database type you happen to be connected to at the timeright down to a function that delimits quotes and other special characters the correct way for that database. If you expect your PHP application to need porting to a different database system in future, the PEAR class is well worth considering.

Note

Database-specific features must be worked around when using a database abstraction layer. For instance the INTEGER PRIMARY KEY feature specific to SQLite is similar to an AUTO INCREMENT field in MySQL, but the same functionality can be only achieved in Oracle using CREATE SEQUENCE. In fact the PEAR Database class forces you to use a database-level sequence object for all autoincrementing fields for maximum compatibility.


To download and install the PEAR DB class automatically if you do not already have it, use the pear utility:

 # pear install DB 

There are two ways to define a database connection. The first uses a string to indicate the Data Source Name (DSN) and usually looks like this:

 dbtype://username:password@hostname/dbname 

For SQLite, however, the username, password, and hostname fields are not required, so the following DSNs are sufficient to identify a database file in the current directory or in the given path respectively.

 sqlite:///dbfile sqlite:////home/chris/sqlite/dbfile 

The second way to define a DSN is by setting elements of an array of parameters, which is defined by the class as follows:

 $dsn = array(     'phptype'  => false,     'dbsyntax' => false,     'username' => false,     'password' => false,     'protocol' => false,     'hostspec' => false,     'port'     => false,     'socket'   => false,     'database' => false, ); 

Only the phptype and database elements are required to identify a SQLite database, so the following array is a valid DSN:

 $dsn = array(     'phptype'  => 'sqlite',     'database' => 'webdb' ); 

Connecting to the database with a DSN defined by either method is as simple as the following statement:

 $db = DB::connect($dsn); 

To execute an SQL command that does not return any rows, use the query() method:

 $sql = "DELETE FROM mytable WHERE myfield = 'somevalue'"; $res = $db->query($sql); 

To test if a query was successful, use the isError() function on the returned result:

 if (DB::isError($res)) {   echo "An error occurred"; } 

For a SELECT query that returns a single row, use the getrow() method to return that row as an arraysimilar to calling sqlite_fetch_single(). If the optional second parameter is passed the constant DB_FETCHMODE_ASSOC, an associative array using the column names as indexes will be created:

 $sql = "SELECT * FROM mytable"; $row = $db->getRow($sql, DB_FETCHMODE_ASSOC); 

To return all the rows in a query as an arraysimilar to sqlite_array_query()use the getAll() method.

 $sql = "SELECT * FROM mytable"; $row = $db->getAll($sql, DB_FETCHMODE_ASSOC); 

This chapter has only touched on a few of the many functions available in the PEAR Database class. There is comprehensive online documentation for the class at http://pear.php.net/manual/en/package.database.php.



    SQLite
    SQLite
    ISBN: 067232685X
    EAN: 2147483647
    Year: 2004
    Pages: 118
    Authors: Chris Newman

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net