Large Objects (LOBs)

At some point in your application, you might find that you need to store "large" data in your database. Large typically means "around 4kb or more", although some databases can happily handle up to 32kb before data becomes "large". Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

Example #1 Displaying an image from a database

This example binds the LOB into the variable named $lob and then sends it to the browser using fpassthru(). Since the LOB is represented as 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);
?>

Example #2 Inserting an image into a database

This example opens up a file and passes the file handle to PDO to insert it as a LOB. PDO will do its best to get the contents of the file up to the database in the most efficient manner possible.


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

// assume that we are running as part of a file upload form
// You can find more information 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();
?>

Example #3 Inserting an image into a database: Oracle

Oracle requires a slightly different syntax for inserting a lob from a file. It's also essential that you perform the insert under a transaction, otherwise your newly inserted LOB will be committed with a zero-length as part of the implicit commit that happens 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(); // some function to allocate a new ID

// assume that we are running as part of a file upload form
// You can find more information 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();
?>

PDO

Errors and error handling

Last updated: Fri, 27 Nov 2009

add a note User Contributed Notes
Large Objects (LOBs)

http://matts.org/
28-Aug-2009 08:30


A big gotcha exists for Oracle users.



You have to save CLOB objects using PDO::PARAM_STR, not PDO::PARAM_LOB.



But you MUST send the 4th argument, usually strlen($subject) or you get a LONG error.

diogoko at gmail dot com
01-Apr-2009 05:33


PDOStatement's methods bindParam and bindValue also work with strings, as in:





<?php


  $data = file_get_contents($filename);


  $stmt->bindValue(1, $data, PDO::PARAM_LOB);


  //...


?>





This was the only way I could make it work with PostgreSQL.

knl at bitflop dot com
23-Sep-2008 07:07


I spend a lot of time trying to get this to work, but no matter what I did PDO corrupted my data.



I finally discovered that I had been using:



$pdo->exec('SET CHARACTER SET utf8');



in the TRY part of my connection script.



This off course doesn't work when you feed binary input to PDO using the parameter lob.

add a note

PDO

Errors and error handling

Last updated: Fri, 27 Nov 2009