Introducing%20PHP%20Data%20Objects

1
IntroducingPHP Data Objects

• Wez Furlong
• wez_at_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/binPATH
• 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
• extensionpdo.so
• extensionpdo_XXX.so
• Windows
• extensionphp_pdo.dll
• extensionphp_pdo_XXX.dll

10
Connecting via PDO

• try
• dbh new PDO(dsn,
• user, password, options)
• catch (PDOException e)
• echo Failed to connect
• . e-gtgetMessage()

11
DSN format in PDO

• Driveroptional_driver_specific_stuff
• sqlite/path/to/db/file
• sqlitememory
• mysqlhostnamedbnamedbname
• pgsqlnative_pgsql_connection_string
• ocidbnamedbnamecharsetcharset
• firebirddbnamedbnamecharsetcharsetrolerole
• odbcodbc_dsn

12
DSN Aliasing

• uriuri
• 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.namesqlite/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-gtgetMessage()
• // use the database here
• //
• // done release the connection
• dbh null

14
Persistent PDO

• dbh new PDO(dsn, user, pass,
• array(
• PDO_ATTR_PERSISTENT gt 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_poolingoff
• Need to restart web server after changing it

16
Lets get data

• dbh new PDO(dsn)
• stmt dbh-gtprepare(
• SELECT FROM FOO)
• stmt-gtexecute()
• while (row stmt-gtfetch())
• print_r(row)

17
Fetch types

• stmt-gtfetch(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-gtname holds the name column from the row
• PDO_FETCH_BOUND
• Just returns true until there are no more rows

18
Lets change data

• deleted dbh-gtquery(
• DELETE FROM FOO WHERE 1)
• changes dbh-gtquery(
• UPDATE FOO SET active1
• . WHERE NAME LIKE joe)

19
Smarter Queries

• Quoting is annoying, but essential
• PDO offers a better way
• stmt-gtprepare(INSERT INTO CREDITS (extension,
  name) VALUES (extension, name))
• stmt-gtexecute(array(
• extension gt xdebug,
• name gt Derick Rethans
• ))

20
Binding for output

• stmt dbh-gtprepare(
• "SELECT extension, name from
  CREDITS")
• if (stmt-gtexecute())
• stmt-gtbindColumn(extension', extension)
• stmt-gtbindColumn(name', name)
• while (stmt-gtfetch(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 hacksWtweaks for this purpose
• dbh-gtsetAttribute(
• 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-gtsetAttribute(PDO_ATTR_CASE,
  PDO_CASE_UPPER)
• stmt dbh-gtprepare(
• "SELECT extension, name from
  CREDITS")
• if (stmt-gtexecute())
• stmt-gtbindColumn(EXTENSION', extension)
• stmt-gtbindColumn(NAME', name)
• while (stmt-gtfetch(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-gtsetAttribute(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-gtquery(sql))
• echo dbh-gterrorCode() . "ltbrgt"
• info dbh-gterrorInfo()
• // info0 dbh-gterrorCode()
• // unified error code
• // info1 is the driver specific
• // error code
• // info2 is the driver specific
• // error string

26
PDO_ERRMODE_EXCEPTION

• try
• dbh-gtexec(sql)
• catch (PDOException e)
• // display warning message print
• e-gtgetMessage()
• info e-gterrorInfo
• // info0 e-gtcode
• // unified error code
• // info1 is the driver specific error code
• // info2 is the driver specific error
  string

27
Transactions

• try
• dbh-gtbeginTransaction()
• dbh-gtquery(UPDATE )
• dbh-gtquery(UPDATE )
• dbh-gtcommit()
• catch (PDOException e)
• dbh-gtrollBack()

28
Cool stuff on the horizon

• Iterators (coming real soon)
• foreach (stmt-gtexecute() as row)
• LOB support via streams
• Bind the parameter
• fwrite, fread(), fseek() on the LOB
• Scrollable cursors

29
Resources

• Oracle Technology Network articlehttp//www.oracl
  e.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