Introducing PHP Data Objects Wez Furlong wezomniti com

Introducing PHP 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 V 3, IBM DB 2 [PDO_ODBC] • My. SQL 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/php 5 • pear download PDO-alpha • tar xzf PDO-*. tgz • cd PDO-* • PATH=/usr/local/php 5/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 [win 32] • Grab the DLLs from the snaps site http: //snaps. php. net/win 32/PECL_5_0/ • You need: – php_pdo. dll – php_pdo_XXX. dll • Put them in C: php 5ext

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->get. Message(); }

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=char set; 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->get. Message(); } // 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->bind. Column(‘extension', $extension); $stmt->bind. Column(‘name', $name); while ($stmt->fetch(PDO_FETCH_BOUND)) { echo “Extension: $extensionn”; echo “Author: $namen”; } }

Portability Aids • PDO aims to make it easier to write db independent apps • Number of hacks^Wtweaks for this purpose $dbh->set. Attribute( 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->set. Attribute(PDO_ATTR_CASE, PDO_CASE_UPPER); $stmt = $dbh->prepare( "SELECT extension, name from CREDITS"); if ($stmt->execute()) { $stmt->bind. Column(‘EXTENSION', $extension); $stmt->bind. Column(‘NAME', $name); while ($stmt->fetch(PDO_FETCH_BOUND)) { echo “Extension: $extensionn”; echo “Author: $namen”; } }

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

Error handling • PDO offers 3 different error modes $dbh->set. Attribute(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->error. Code(). " "; $info = $dbh->error. Info(); // $info[0] == $dbh->error. Code() // 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->get. Message(); $info = $e->error. Info; // $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->begin. Transaction(); $dbh->query(‘UPDATE …’); $dbh->commit(); } catch (PDOException $e) { $dbh->roll. Back(); }

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 article http: //www. oracle. com/technology/pub/articles/php_expert s/otn_pdo_oracle 5. html • These slides and other PDO news bytes http: //netevil. org • Bugs? http: //pecl. php. net/bugs/report. php? package=PDO_XXX