Introducing PHP Data Objects

IntroducingPHP Data Objects Wez Furlong wez@omniti.com

The Problem • No consistency of API between DB extensions • Sometimes no self-consistency within a given extension • Duplicated code (but not) • High maintenance

The PDO solution • Move PHP specific stuff into one extension • Database specific stuff (only) in their own extensions • Data access abstraction, not database abstraction

Features • Performance • Native C code beats a scripted solution • Takes advantage of latest PHP 5 internals • Power • Gives you common DB features as a base • Still be able to access specialist functions • Easy • Non-intrusive • Clear • Runtime extensible • Drivers can be loaded at runtime

Available Drivers • Oracle OCI [PDO_OCI] • ODBC V3, IBM DB2 [PDO_ODBC] • MySQL 3.x [PDO_MYSQL] • Postgres [PDO_PGSQL] • SQLite 3.x [PDO_SQLITE] • Firebird [PDO_FIREBIRD]

Getting PDO [unix] • Build PHP 5 --with-zlib --prefix=/usr/local/php5 • pear download PDO-alpha • tar xzf PDO-*.tgz • cd PDO-* • PATH=/usr/local/php5/bin:$PATH • phpize && ./configure && make • make install

Getting PDO [unix] 2 • Select the driver(s) you need • pear download PDO_XXX-alpha • tar xzf PDO_XXX*.tgz • cd PDO_XXX* • phpize && ./configure && make • make install

Getting PDO [win32] • Grab the DLLs from the snaps sitehttp://snaps.php.net/win32/PECL_5_0/ • You need: • php_pdo.dll • php_pdo_XXX.dll • Put them in C:\php5\ext

Switching it on • Need to enable PDO in your php.ini • MUST load PDO first • Unix: extension=pdo.so extension=pdo_XXX.so • Windows extension=php_pdo.dll extension=php_pdo_XXX.dll

Connecting via PDO try { $dbh = new PDO($dsn, $user, $password, $options); } catch (PDOException $e) { echo “Failed to connect:” . $e->getMessage(); }

DSN format in PDO • Driver:optional_driver_specific_stuff • sqlite:/path/to/db/file • sqlite::memory: • mysql:host=name;dbname=dbname • pgsql:native_pgsql_connection_string • oci:dbname=dbname;charset=charset • firebird:dbname=dbname;charset=charset;role=role • odbc:odbc_dsn

DSN Aliasing • uri:uri • Specify location of a file containing actual DSN on the first line • Works with streams interface, so remote URLs can work too • name (with no colon) • Maps to pdo.dsn.name in your php.ini • pdo.dsn.name=sqlite:/path/to/name.db • $dbh = new PDO(‘name’); • $dbh = new PDO(‘sqlite:/path/to/name.db’); • Neither of these allows for user/pass (yet!)

Connection management try { $dbh = new PDO($dsn, $user, $pw); } catch (PDOException $e) { echo “connect failed:” . $e->getMessage(); } // use the database here // … // done; release the connection $dbh = null;

Persistent PDO $dbh = new PDO($dsn, $user, $pass, array( PDO_ATTR_PERSISTENT => true ) ); • Can specify a string instead of true • Useful for keeping 2 connections open with similar credentials

Persistent PDO 2 • PDO_ODBC supports native connection pooling by default • Likely to be more resource efficient than PDO ‘pconnect’ • Can turn it off in php.ini: pdo_odbc.connection_pooling=off • Need to restart web server after changing it

Let’s get data $dbh = new PDO($dsn); $stmt = $dbh->prepare( ‘SELECT * FROM FOO’); $stmt->execute(); while ($row = $stmt->fetch()) { print_r($row); }

Fetch types • $stmt->fetch(PDO_FETCH_BOTH) • Array with numeric and string keys • default option • PDO_FETCH_NUM • Array with numeric keys • PDO_FETCH_ASSOC • Array with string keys • PDO_FETCH_OBJ • $obj->name holds the ‘name’ column from the row • PDO_FETCH_BOUND • Just returns true until there are no more rows

Let’s change data $deleted = $dbh->query( “DELETE FROM FOO WHERE 1”); $changes = $dbh->query( “UPDATE FOO SET active=1 ” . “WHERE NAME LIKE ‘%joe%’”);

Smarter Queries • Quoting is annoying, but essential • PDO offers a better way $stmt->prepare(‘INSERT INTO CREDITS (extension, name) VALUES (:extension, :name)’); $stmt->execute(array( ‘:extension’ => ‘xdebug’, ‘:name’ => ‘Derick Rethans’ ));

Binding for output $stmt = $dbh->prepare( "SELECT extension, name from CREDITS"); if ($stmt->execute()) { $stmt->bindColumn(‘extension', $extension); $stmt->bindColumn(‘name', $name); while ($stmt->fetch(PDO_FETCH_BOUND)) { echo “Extension: $extension\n”; echo “Author: $name\n”; } }

Portability Aids • PDO aims to make it easier to write db independent apps • Number of hacks^Wtweaks for this purpose $dbh->setAttribute( PDO_ATTR_ORACLE_NULLS, true); • Converts empty strings to NULL when fetched

PDO_ATTR_CASE • Some databases (notably, Oracle) insist on returning column names in uppercase $dbh->setAttribute(PDO_ATTR_CASE, PDO_CASE_UPPER); $stmt = $dbh->prepare( "SELECT extension, name from CREDITS"); if ($stmt->execute()) { $stmt->bindColumn(‘EXTENSION', $extension); $stmt->bindColumn(‘NAME', $name); while ($stmt->fetch(PDO_FETCH_BOUND)) { echo “Extension: $extension\n”; echo “Author: $name\n”; } }

Data typing • Very loose • uses strings for data • Gives you more control over data conversion

Error handling • PDO offers 3 different error modes $dbh->setAttribute(PDO_ATTR_ERRMODE, $mode); • PDO_ERRMODE_SILENT • PDO_ERRMODE_WARNING • PDO_ERRMODE_EXCEPTION • Attempts to map native codes to PDO generic codes • But still offers native info too

PDO_ERRMODE_SILENT if (!$dbh->query($sql)) { echo $dbh->errorCode() . "<br>"; $info = $dbh->errorInfo(); // $info[0] == $dbh->errorCode() // unified error code // $info[1] is the driver specific // error code // $info[2] is the driver specific // error string }

PDO_ERRMODE_EXCEPTION try { $dbh->exec($sql); } catch (PDOException $e) { // display warning message print $e->getMessage(); $info = $e->errorInfo; // $info[0] == $e->code; // unified error code // $info[1] is the driver specific error code // $info[2] is the driver specific error string }

Transactions try { $dbh->beginTransaction(); $dbh->query(‘UPDATE …’); $dbh->query(‘UPDATE …’); $dbh->commit(); } catch (PDOException $e) { $dbh->rollBack(); }

Cool stuff on the horizon • Iterators (coming real soon) foreach ($stmt->execute() as $row) • LOB support via streams • Bind the parameter • fwrite, fread(), fseek() on the LOB • Scrollable cursors

Resources • Oracle Technology Network articlehttp://www.oracle.com/technology/pub/articles/php_experts/otn_pdo_oracle5.html • These slides and other PDO news byteshttp://netevil.org • Bugs?http://pecl.php.net/bugs/report.php?package=PDO_XXX

Introducing PHP Data Objects