Source of: /manual/en/pdo.lobs.php
<?php
include_once $_SERVER['DOCUMENT_ROOT'] . '/include/shared-manual.inc';
$TOC = array();
$PARENTS = array();
include_once dirname(__FILE__) ."/toc/book.pdo.inc";
$setup = array (
'home' =>
array (
0 => 'index.php',
1 => 'PHP Manual',
),
'head' =>
array (
0 => 'UTF-8',
1 => 'en',
),
'this' =>
array (
0 => 'pdo.lobs.php',
1 => 'Large Objects (LOBs)',
),
'up' =>
array (
0 => 'book.pdo.php',
1 => 'PHP Data Objects',
),
'prev' =>
array (
0 => 'pdo.error-handling.php',
1 => 'Errors and error handling',
),
'next' =>
array (
0 => 'class.pdo.php',
1 => 'PDO',
),
);
$setup["toc"] = $TOC;
$setup["parents"] = $PARENTS;
manual_setup($setup);
manual_header();
?>
<div>
<h1>Large Objects (LOBs)</h1>
<p class="para">
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
<b><tt class="constant">PDO::PARAM_LOB</tt></b>
type code in your <a href="pdostatement.bindparam.php" class="function">PDOStatement::bindParam()</a> or
<a href="pdostatement.bindcolumn.php" class="function">PDOStatement::bindColumn()</a> calls.
<b><tt class="constant">PDO::PARAM_LOB</tt></b> tells
PDO to map the data as a stream, so that you can manipulate it using the
<a href="ref.stream.php" class="link">PHP Streams API</a>.
</p>
<p class="para">
</p><div class="example">
<p><b>Example #1 Displaying an image from a database</b></p>
<div class="example-contents para"><p>
This example binds the LOB into the variable named $lob and then sends
it to the browser using <a href="function.fpassthru.php" class="function">fpassthru()</a>. Since the LOB
is represented as a stream, functions such as
<a href="function.fgets.php" class="function">fgets()</a>, <a href="function.fread.php" class="function">fread()</a> and
<a href="function.stream-get-contents.php" class="function">stream_get_contents()</a> can be used on it.
</p></div>
<div class="example-contents programlisting">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$db </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #DD0000">'odbc:SAMPLE'</span><span style="color: #007700">, </span><span style="color: #DD0000">'db2inst1'</span><span style="color: #007700">, </span><span style="color: #DD0000">'ibmdb2'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$db</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"select contenttype, imagedata from images where id=?"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">(array(</span><span style="color: #0000BB">$_GET</span><span style="color: #007700">[</span><span style="color: #DD0000">'id'</span><span style="color: #007700">]));<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindColumn</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$type</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_STR</span><span style="color: #007700">, </span><span style="color: #0000BB">256</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindColumn</span><span style="color: #007700">(</span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #0000BB">$lob</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_LOB</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch</span><span style="color: #007700">(</span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">FETCH_BOUND</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">header</span><span style="color: #007700">(</span><span style="color: #DD0000">"Content-Type: </span><span style="color: #0000BB">$type</span><span style="color: #DD0000">"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">fpassthru</span><span style="color: #007700">(</span><span style="color: #0000BB">$lob</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div><p>
</p>
<p class="para">
</p><div class="example">
<p><b>Example #2 Inserting an image into a database</b></p>
<div class="example-contents para"><p>
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.
</p></div>
<div class="example-contents programlisting">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$db </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #DD0000">'odbc:SAMPLE'</span><span style="color: #007700">, </span><span style="color: #DD0000">'db2inst1'</span><span style="color: #007700">, </span><span style="color: #DD0000">'ibmdb2'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$db</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"insert into images (id, contenttype, imagedata) values (?, ?, ?)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$id </span><span style="color: #007700">= </span><span style="color: #0000BB">get_new_id</span><span style="color: #007700">(); </span><span style="color: #FF8000">// some function to allocate a new ID<br /><br />// assume that we are running as part of a file upload form<br />// You can find more information in the PHP documentation<br /><br /></span><span style="color: #0000BB">$fp </span><span style="color: #007700">= </span><span style="color: #0000BB">fopen</span><span style="color: #007700">(</span><span style="color: #0000BB">$_FILES</span><span style="color: #007700">[</span><span style="color: #DD0000">'file'</span><span style="color: #007700">][</span><span style="color: #DD0000">'tmp_name'</span><span style="color: #007700">], </span><span style="color: #DD0000">'rb'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #0000BB">$_FILES</span><span style="color: #007700">[</span><span style="color: #DD0000">'file'</span><span style="color: #007700">][</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">3</span><span style="color: #007700">, </span><span style="color: #0000BB">$fp</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_LOB</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$db</span><span style="color: #007700">-></span><span style="color: #0000BB">beginTransaction</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$db</span><span style="color: #007700">-></span><span style="color: #0000BB">commit</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div><p>
</p>
<p class="para">
</p><div class="example">
<p><b>Example #3 Inserting an image into a database: Oracle</b></p>
<div class="example-contents para"><p>
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:
</p></div>
<div class="example-contents programlisting">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$db </span><span style="color: #007700">= new </span><span style="color: #0000BB">PDO</span><span style="color: #007700">(</span><span style="color: #DD0000">'oci:'</span><span style="color: #007700">, </span><span style="color: #DD0000">'scott'</span><span style="color: #007700">, </span><span style="color: #DD0000">'tiger'</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt </span><span style="color: #007700">= </span><span style="color: #0000BB">$db</span><span style="color: #007700">-></span><span style="color: #0000BB">prepare</span><span style="color: #007700">(</span><span style="color: #DD0000">"insert into images (id, contenttype, imagedata) " </span><span style="color: #007700">.<br /></span><span style="color: #DD0000">"VALUES (?, ?, EMPTY_BLOB()) RETURNING imagedata INTO ?"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$id </span><span style="color: #007700">= </span><span style="color: #0000BB">get_new_id</span><span style="color: #007700">(); </span><span style="color: #FF8000">// some function to allocate a new ID<br /><br />// assume that we are running as part of a file upload form<br />// You can find more information in the PHP documentation<br /><br /></span><span style="color: #0000BB">$fp </span><span style="color: #007700">= </span><span style="color: #0000BB">fopen</span><span style="color: #007700">(</span><span style="color: #0000BB">$_FILES</span><span style="color: #007700">[</span><span style="color: #DD0000">'file'</span><span style="color: #007700">][</span><span style="color: #DD0000">'tmp_name'</span><span style="color: #007700">], </span><span style="color: #DD0000">'rb'</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">1</span><span style="color: #007700">, </span><span style="color: #0000BB">$id</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">2</span><span style="color: #007700">, </span><span style="color: #0000BB">$_FILES</span><span style="color: #007700">[</span><span style="color: #DD0000">'file'</span><span style="color: #007700">][</span><span style="color: #DD0000">'type'</span><span style="color: #007700">]);<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">bindParam</span><span style="color: #007700">(</span><span style="color: #0000BB">3</span><span style="color: #007700">, </span><span style="color: #0000BB">$fp</span><span style="color: #007700">, </span><span style="color: #0000BB">PDO</span><span style="color: #007700">::</span><span style="color: #0000BB">PARAM_LOB</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">beginTransaction</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">$stmt</span><span style="color: #007700">-></span><span style="color: #0000BB">commit</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div><p>
</p>
</div>
<?php manual_footer(); ?>
