©
本文档使用
php.cn手册 发布
大对象 (LOBs)
应用程序在某一时刻，可能需要在数据库中存储“大”数据。“大”通常意味着“大约 4kb 或以上”，尽管某些数据库在数据达到“大”之前可以轻松地处理多达 32kb 的数据。大对象本质上可能是文本或二进制。在 PDOStatement::bindParam() 或 PDOStatement::bindColumn() ) 调用中使用 PDO::PARAM_LOB 类型码可以让 PDO 使用大数据类型。 PDO::PARAM_LOB 告诉 PDO 作为流来映射数据，以便能使用 PHP Streams API 来操作。
Example #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 );
?>
Example #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 ();
?>
Example #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 );
$stmt -> beginTransaction ();
$stmt -> execute ();
$stmt -> commit ();
?> 用户评论:
[#1] xorinox at gmx dot ch [2011-01-13 11:11:56]
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 );
}
?>
[#2] Jeremy Cook [2010-02-19 13:31:17]
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().
[#3] http://matts.org/ [2009-08-28 13:30:57]
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.
[#4] diogoko at gmail dot com [2009-04-01 09:33:25]
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.
[#5] knl at bitflop dot com [2008-09-23 12:07:10]
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.