Transcript Introducing PHP Data Objects

Introducing
PHP Data Objects
Wez Furlong
[email protected]
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 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
• 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=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->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 article
http://www.oracle.com/technology/pub/articles/php_expert
s/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