PHP PDO Large Objects (LOBs)
PHP PDO Reference ManualAt some point, applications may need to store "large" data in the database.
"Large" usually means "approximately 4kb or more", although some databases can easily handle up to 32kb of data before it becomes "large". Large objects are essentially text or binary.
Using the PDO::PARAM_LOB type code in PDOStatement::bindParam() or PDOStatement::bindColumn() calls allows PDO to use the large data type.
PDO::PARAM_LOB tells PDO to map the data as a stream, so that it can be manipulated using the PHP Streams API.
Display an image from the database
The following example binds a LOB to the $lob variable, then uses fpassthru() to send it to the browser. Because a LOB represents a stream, functions such as fgets(), fread(), and stream_get_contents() can be used on it.
<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
header("Content-Type: $type");
fpassthru($lob);
?>
Insert an image into the database
The following example opens a file and passes the file handle to PDO to insert it as a LOB. PDO makes the database fetch the file contents in the most efficient way possible.
<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // 调用某个函数来分配一个新 ID
// 假设处理一个文件上传
// 可以在 PHP 文档中找到更多的信息
$fp = fopen($_FILES['file']['tmp_name'], 'rb');
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);
$db->beginTransaction();
$stmt->execute();
$db->commit();
?>
Insert an image into the database: Oracle
For inserting a LOB from a file, Oracle is slightly different. The insertion must be performed after a transaction; otherwise, when the query is executed, the newly inserted LOB will be implicitly committed with a length of 0:
<?php
$db = new PDO('oci:', 'scott', 'tiger');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) " .
"VALUES (?, ?, EMPTY_BLOB()) RETURNING imagedata INTO ?");
$id = get_new_id(); // 调用某个函数来分配一个新 ID
// 假设处理一个文件上传
// 可以在 PHP 文档中找到更多的信息
$fp = fopen($_FILES['file']['tmp_name'], 'rb');
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $_FILES['file']['type']);
$stmt->bindParam(3, $fp, PDO::PARAM_LOB);
$stmt->beginTransaction();
$stmt->execute();
$stmt->commit();
?>
Other Extensions