PHP PDO Large Objects (LOBs)

At some point, an application may need to store "large" data in the database.

"Large" typically means "around 4kb or more," although some databases can handle up to 32kb of data easily before it reaches "large." Large objects can essentially be text or binary.

Using the PDO::PARAM_LOB type code in PDOStatement::bindParam() or PDOStatement::bindColumn() calls allows PDO to handle large data types.

PDO::PARAM_LOB instructs PDO to map the data as a stream, enabling the use of the PHP Streams API for manipulation.

Displaying an Image from the Database

The following example binds a LOB to the $lob variable and then sends it to the browser using fpassthru(). Since the LOB represents a stream, functions like 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);
?>

Inserting an Image into the Database

The following example opens a file and passes the file handle to PDO for insertion as a LOB. PDO makes every effort to fetch the file contents in the most efficient way for the database.

<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
$id = get_new_id(); // Call some function to allocate a new ID

// Assume handling a file upload
// More information can be found in the PHP documentation

$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();
?>

Inserting an Image into the Database: Oracle

For inserting a LOB from a file, Oracle is slightly different. The insertion must be done after the transaction, otherwise, the newly inserted LOB will be implicitly committed with a length of 0 when the query is executed:

<?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(); // Call some function to allocate a new ID

// Assume handling a file upload
// More information can be found in the PHP documentation

$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();
?>

PHP PDO Reference Manual

❮ Func Date Offset Get Func Ftp Rmdir ❯