IntroducingPHP Data Objects: Introducing PHP Data Objects Wez Furlong wez@omniti.com


The Problem: 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: 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: 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: 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]: 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 andamp;andamp; ./configure andamp;andamp; make make install


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


Getting PDO [win32]: Getting PDO [win32] Grab the DLLs from the snaps site http://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: 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: Connecting via PDO try { $dbh = new PDO($dsn, $user, $password, $options); } catch (PDOException $e) { echo 'Failed to connect:' . $e-andgt;getMessage(); }


DSN format in PDO: 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: 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: Connection management try { $dbh = new PDO($dsn, $user, $pw); } catch (PDOException $e) { echo 'connect failed:' . $e-andgt;getMessage(); } // use the database here // … // done; release the connection $dbh = null;


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


Persistent PDO 2: 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: Let’s get data $dbh = new PDO($dsn); $stmt = $dbh-andgt;prepare( ‘SELECT * FROM FOO’); $stmt-andgt;execute(); while ($row = $stmt-andgt;fetch()) { print_r($row); }


Fetch types: Fetch types $stmt-andgt;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-andgt;name holds the ‘name’ column from the row PDO_FETCH_BOUND Just returns true until there are no more rows


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


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


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


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


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


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


Error handling: Error handling PDO offers 3 different error modes $dbh-andgt;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: PDO_ERRMODE_SILENT if (!$dbh-andgt;query($sql)) { echo $dbh-andgt;errorCode() . 'andlt;brandgt;'; $info = $dbh-andgt;errorInfo(); // $info[0] == $dbh-andgt;errorCode() // unified error code // $info[1] is the driver specific // error code // $info[2] is the driver specific // error string }


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


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


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


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