Edit Report a Bug

    大对象 (LOB)

    应用程序在某一时刻，可能需要在数据库中存储“大”数据。“大”通常意味着“大约 4kb 或以上”，尽管某些数据库在数据达到“大”之前可以轻松地处理多达 32kb 的数据。大对象本质上可能是文本或二进制。在 PDOStatement::bindParam() 或 PDOStatement::bindColumn()) 调用中使用 PDO::PARAM_LOB 类型码可以让 PDO 使用大数据类型。PDO::PARAM_LOB 告诉 PDO 作为流来映射数据，以便能使用 PHP Streams API 来操作。

    示例 #1 从数据库中显示一张图片

    下面例子绑定一个 LOB 到 $lob 变量，然后用 fpassthru() 将其发送到浏览器。因为 LOB 代表一个流，所以类似 fgets()、fread() 以及 stream_get_contents() 这样的函数都可以用在它上面。

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

    示例 #2 插入一张图片到数据库

    下面例子打开一个文件并将文件句柄传给 PDO 来做为一个 LOB 插入。PDO尽可能地让数据库以最有效的方式获取文件内容。

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

    示例 #3 插入一张图片到数据库：Oracle

    对于从文件插入一个 lob，Oracle略有不同。必须在事务之后进行插入，否则当执行查询时导致新近插入 LOB 将以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);

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

    add a note add a note

    User Contributed Notes 7 notes

    up
    11
    diogoko at gmail dot com
    15 years ago
    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.
    up
    10
    http://matts.org/
    15 years ago
    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.
    up
    11
    Jeremy Cook
    14 years ago
    There seems to be a bug that affects example 1 above. PDO::PARAM_LOB when used with pdo::bindColumn() is supposed to return a stream but it returns a string. Passing this string to fpassthru() then triggers an error with the message 'supplied argument is not a valid stream resource'. This has been reported in bug #40913. The work around is to do the following:

    <?php
    $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");
    echo(    $lob);
    ?>

    Since the browser is expecting an image after the call to header() writing the string representation of the binary output with echo() has the same affect as calling fpassthru().
    up
    4
    ben dot leiting at gmail dot com
    8 years ago
    For selecting data out of Postgres, the data type of the column in the table determined if the parameter bound with PARAM_LOB returned a string or returned a resource.

    <?php

    // create table log ( data text ) ;
    $geth = $dbh->prepare('select data from log ');
    $geth->execute();
    $geth->bindColumn(1, $dataString, PDO::PARAM_LOB);
    $geth->fetch(PDO::FETCH_BOUND);

    echo (    $dataString); // $dataString is a string

    // create table log ( data bytea ) ;
    $geth = $dbh->prepare('select data from log');
    $geth->execute();
    $geth->bindColumn(1, $dataFH, PDO::PARAM_LOB);
    $geth->fetch(PDO::FETCH_BOUND);

    fpassthru($dataFH); // $dataFH is a resource
    up
    2
    knl at bitflop dot com
    16 years ago
    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.
    up
    0
    phpcoder at gmail dot com
    6 years ago
    The DBMSs that are listed above have these (default) limits on the maximum size of a char string. The maximum is given in bytes so the number of characters storable can be smaller if a multibyte encoding is used.

    CUBRID:    16kB
    SQL Server: 2GB
    Firebird:  32kB
    IBM Db2:   32kB
    Informix:  32kB
    MySQL:     16kB
    Oracle:     2kB
    PostgreSQL: 1GB
    SQLite:     1 billion bytes
    4D: Unknown, but LOBs are limited to 2GB.
    up
    -12
    xorinox at gmx dot ch
    13 years ago
    I find it easier to use stream_get_contens to fetch the data of a lob using the file handle.

    <?php
    $stmt     = $pdo->con->prepare( 'select * from filetable' );
    $stmt->execute();
    $res = $stmt->fetchAll( PDO::FETCH_ASSOC );

    for(     $i=0; $i<count($res); $i++ ){
          $filename = "C:/tmp/".$res[$i]['FILE_ID'].'.xml';
          $content = stream_get_contents( $res[$i]['DATA_FILE'] );
          file_put_contents( $filename, $content );
    }
    ?>
    add a note add a note
    To Top