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 it reaches "large". Large objects may be text or binary in nature. Using the PDO::PARAM_LOB type code in PDOStatement::bindParam() or PDOStatement::bindColumn() calls allows 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.

Example #1 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 LOB represents a stream, functions like fgets(), fread(), and stream_get_contents() can be used on it.

<?php
    $db = new PDO(&#39;odbc:SAMPLE&#39;, &#39;db2inst1&#39;, &#39;ibmdb2&#39;);
    $stmt = $db->prepare("select contenttype, imagedata from images where id=?");
    $stmt->execute(array($_GET[&#39;id&#39;]));
    $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 Insert a picture into the database

The following example opens a file and passes the file handle to PDO to insert as a LOB. PDO allows the database to obtain file contents in the most efficient way possible.

<?php
    $db = new PDO(&#39;odbc:SAMPLE&#39;, &#39;db2inst1&#39;, &#39;ibmdb2&#39;);
    $stmt = $db->prepare("insert into images (id, contenttype, imagedata) values (?, ?, ?)");
    $id = get_new_id(); // 调用某个函数来分配一个新 ID
    // 假设处理一个文件上传
    // 可以在 PHP 文档中找到更多的信息
    $fp = fopen($_FILES[&#39;file&#39;][&#39;tmp_name&#39;], &#39;rb&#39;);
    $stmt->bindParam(1, $id);
    $stmt->bindParam(2, $_FILES[&#39;file&#39;][&#39;type&#39;]);
    $stmt->bindParam(3, $fp, PDO::PARAM_LOB);
    $db->beginTransaction();
    $stmt->execute();
    $db->commit();
?>

Example #3 Inserting a picture into the database: Oracle

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

<?php
 $db = new PDO(&#39;oci:&#39;, &#39;scott&#39;, &#39;tiger&#39;);
   $stmt = $db->prepare("insert into images (id, contenttype, imagedata) " .
       "VALUES (?, ?, EMPTY_BLOB()) RETURNING imagedata INTO ?");
   $id = get_new_id(); // 调用某个函数来分配一个新 ID

   // 假设处理一个文件上传
   // 可以在 PHP 文档中找到更多的信息
   $fp = fopen($_FILES[&#39;file&#39;][&#39;tmp_name&#39;], &#39;rb&#39;);

   $stmt->bindParam(1, $id);
   $stmt->bindParam(2, $_FILES[&#39;file&#39;][&#39;type&#39;]);
   $stmt->bindParam(3, $fp, PDO::PARAM_LOB);

   $stmt->beginTransaction();
   $stmt->execute();
   $stmt->commit();
?>