1 / 29

Introducing PHP Data Objects

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

kamala
Download Presentation

Introducing PHP Data Objects

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. IntroducingPHP Data Objects Wez Furlong wez@omniti.com

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

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

  4. 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

  5. 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]

  6. 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

  7. 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

  8. 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

  9. 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

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

  11. 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

  12. 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!)

  13. 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;

  14. 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

  15. 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

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

  17. 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

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

  19. 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’ ));

  20. 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”; } }

  21. 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

  22. 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”; } }

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

  24. 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

  25. 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 }

  26. 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 }

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

  28. 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

  29. 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

More Related