PDO 简介

为什么使用 PDO？

在哪里可以获得 PDO？

Unix/Linux 安装

% ./configure --prefix=/usr/local/php5 --with-zlib [此处指定其他选项]
% make install

% PATH="/usr/local/php5/bin:$PATH"
% pear install PDO-alpha

防火墙碍事了？

% wget http://pecl.php.net/get/PDO
% pear install PDO-0.1.1.tgz

% wget http://pecl.php.net/get/PDO_OCI
% pear install PDO_OCI-0.1.tgz

Windows 安装

连接 PDO

<?php
try {
$dbh = new PDO("OCI:", "scott", "tiger");
} catch (PDOException $e) {
echo "Failed to obtain database handle " .$e->getMessage();
    }
?>

<?php
try {
$dbh = new PDO("OCI:dbname=accounts;charset=UTF-8", "scott", "tiger");
} catch (PDOException $e) {
echo "Failed to obtain database handle " .$e->getMessage();
    }
?>

连接管理

<?php
try {
$dbh = new PDO("OCI:dbname=accounts;charset=UTF-8", "scott", "tiger");
} catch (PDOException $e) {
echo "Failed to obtain database handle " .$e->getMessage();
exit;
    }
// 在此处对数据库执行一些操作
    // ...
    
// 现在完成，释放该连接
$dbh = null;
?>

使用 PDO

<?php

// Create a PDO database handle object
// the 'oci:' string specifies that the OCI driver should be used
// you could use 'oci:dbname=name' to specify the database name.
// The second and third parameters are the username and password respectively
$dbh = new PDO('oci:', 'scott', 'tiger');

// Create a test table to hold the data from credits.csv
$dbh->exec("
CREATE TABLE CREDITS (
	extension varchar(255),
	name varchar(255)
)");

// start a transaction
$dbh->beginTransaction();

// prepare to insert a large quantitiy of data
$stmt = $dbh->prepare("INSERT INTO CREDITS (extension, name) VALUES (:extension, :name)");

// bind the inputs to php variables; specify that the data will be strings
// with a maximum length of 64 characters
$stmt->bindParam(':extension',	$extension,	PDO_PARAM_STR, 64);
$stmt->bindParam(':name', 		$name,		PDO_PARAM_STR, 64);

// Open the .csv file for import
$fp = fopen('credits.csv', 'r');
while (!feof($fp)) {
	list($extension, $name) = fgetcsv($fp, 1024);
	$stmt->execute();
}
fclose($fp);

// Commit the changes
$dbh->commit();

?>

PDO 中的事务处理

预处理语句、存储过程

$stmt = $dbh->prepare("INSERT INTO CREDITS (extension, name) VALUES (:extension, :name)");

$stmt->bindParam(':extension', $extension, PDO_PARAM_STR, 64);
$stmt->bindParam(':name',      $name,      PDO_PARAM_STR, 64);

$stmt = $dbh->prepare("INSERT INTO CREDITS (extension, name) VALUES (:extension, :name)");
$stmt->execute(array(':extension' => $extension, ':name' => $name));

$stmt = $dbh->prepare("begin sp_add_item(:item_name, :error_code); end");
$stmt->bindParam(':item_name', $item_name, PDO_PARAM_STR,  12);
$stmt->bindParam(':error_code', $error_code, PDO_PARAM_STR, 12);
$stmt->execute();

抓取数据

$stmt = $dbh->prepare("SELECT extension, name from CREDITS");
if ($stmt->execute()) {
while ($row = stmt->fetch()) {
print_r($row);
    }
}

while ($row = $stmt->fetch(PDO_FETCH_NUM)) {
	printf("Extension %s, by %s<br>", $row[0], $row[1]);
}

while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
	echo "Extension $row[EXTENSION] by $row[NAME]<br>";
}

while ($row = $stmt->fetch(PDO_FETCH_ASSOC)) {
	echo "Extension {$row->EXTENSION} by {$row->NAME}<br>";
}

$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, Author:$name\n";
    }
}

可移植性

$dbh = new PDO('OCI:', 'scott', 'tiger');
$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, Author:$name\n";
    }
}

if (!$dbh->exec($sql)) {
	echo $dbh->errorCode() ."<BR>";
	$info = $dbh->errorInfo();
	// $info[0] == $dbh->errorCode() 统一的错误代码
	// $info[1] 是驱动程序特定的错误代码
	// $info[2] 是驱动程序特定的错误字符串
}

try {
	$dbh->exec($sql);
} catch (PDOException $e) {
	// 显示警告消息
	print $e->getMessage();
	$info = $e->errorInfo;
	// $info[0] == $e->code; unified error code
	// $info[1] 是驱动程序特定的错误代码
	// $info[2] 是驱动程序特定的错误字符串
}

$dbh = new PDO('OCI:', 'scott', 'tiger');
$dbh->setAttribute(PDO_ATTR_ORACLE_NULLS, true);
// 现在从此 $dbh 打开的任何语句中的
// 空字符串都将被转换为 NULL

POD 的现状和未来