PHP PDO Large Objects (LOBs)

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

"Large" usually means "about 4kb or more", although some databases can easily handle up to 32kb of data before the data reaches "large". Large objects may be text or binary in nature.

Use the PDO::PARAM_LOB type code in the PDOStatement::bindParam() or PDOStatement::bindColumn()) call to enable PDO to use large data types.

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 and then uses fpassthru() to send it to the browser. Because a 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);?>

Insert a picture into the database

The following example opens a file and passes the file handle to PDO for insertion as a LOB. PDO allows the database to obtain 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(); // Call a function to assign a new ID // Assume that a file upload is processed // This can be done in PHP More information found in the 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();?>

Insert a picture into the database: Oracle

Oracle is slightly different for inserting a lob from a file. Inserts must be made after a transaction, otherwise the newly inserted LOB will be implicitly committed with 0 length 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 a function to assign 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();?>