[update: corrected information about STRINGIFY_FETCHES]
It's been a looong time coming, but it's finally here. Here's how to insert a BLOB via PDO_OCI:
<?php
$db = new PDO("oci:", "scott", "tiger");
$db->beginTransaction(); $stmt = $db->prepare(
"INSERT INTO blobtest (id, contenttype, blob) ".
"VALUES (:id, :type, EMPTY_BLOB()) ".
"RETURNING blob INTO :blob");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':type', $type);
$stmt->bindParam(':blob', $blob, PDO::PARAM_LOB);
$type = 'image/gif';
$id = 1; $blob = fopen('/path/to/a/graphic.gif', 'rb');
$stmt->execute();
$stmt->commit();
?>
This will suck the contents of the graphic.gif up and store it into the newly inserted row. This syntax most closely matches the generic blob insert syntax that I talk about in my PDO presentation, there are two differences that are peculiar to Oracle. The first is the RETURNING blob INTO :blob that's tacked onto the end of the INSERT query. The reason for this is that Oracle stores "LOB Locators" rather than LOB contents in its table rows.
A LOB Locator tells Oracle where it stashed the actual LOB contents without making the table rows overly large, and allows some clever optimizations when manipulating LOBs. You can't just conjure up a LOB Locator though, so you need to insert a brand new empty LOB into a table and then fetch it's locator back out before you can start modifying it. (If you're coming from a mysql background, you can think of the locator as being something like a mysql auto-increment field; you need to insert a row before you find out what the value of the field is.)
Rather than issuing 2 queries just to make an insert, Oracle provides the RETURNING ... INTO syntax as a shortcut; it's equivalent to SELECTing the columns back out again, but it bundled up into a single query, saving the effort of parsing multiple queries and the overhead of multiple network round-trips to get everything where it needs to be.
This means that the :blob parameter is actually an output parameter, even though it smells like an input parameter. There's some intuitive magic at work here; if you bind a stream or a string to a PDO::PARAM_LOB parameter, the PDO_OCI driver will assume that you want to store the contents of that stream-or-string into the LOB that gets returned after the execute. So, that's what it does. Post-execute, all the LOB parameters are checked to see what PHP-space variables were bound, and data is written to the LOBs. This has an important implication; if you're doing this, you'd better have a transaction open, otherwise your new LOBs will be committed as part of the execute--before the PDO_OCI driver can write the data into the LOBs.
So, we can insert just fine. What about binding a LOB for output? Here's how:
<?php
$db = new PDO("oci:", "scott", "tiger");
$db->beginTransaction(); $stmt = $db->prepare(
"INSERT INTO blobtest (id, contenttype, blob) ".
"VALUES (:id, :type, EMPTY_BLOB()) ".
"RETURNING blob INTO :blob");
$stmt->bindParam(':id', $id);
$stmt->bindParam(':type', $type);
$stmt->bindParam(':blob', $blob, PDO::PARAM_LOB);
$type = 'image/gif';
$id = 1; $blob = null;
$stmt->execute();
fwrite($blob, "GIF89a");
...
fclose($blob);
$stmt->commit();
?>
OK, this sample is still inserting data into the LOB, but it's doing it by binding the LOB for output, and giving you access to the LOB stream so that you can manually do things with it. The same approach will work if you issue a query that returns an existing read-only LOB. This syntax is closer to the traditional oci8 extension LOB support, except that the LOB is mapped as a PHP stream, so that you can use all the usual PHP streams functions to work with it.
LOBs are also handled for simple SELECTs that return rows with LOB columns. The PDO_OCI driver returns each LOB column as a stream; no data is transferred from that column until you start to read from it:
<?php
$stmt = $db->prepare('select blob from blobtest where id = ?');
$stmt->execute(array($id));
$row = $stmt->fetch();
var_dump($row);
var_dump(stream_get_contents($row[0]));
?>
this will output something like:
array(2) {
["BLOB"]=>
resource(7) of type (stream)
[0]=>
resource(7) of type (stream)
}
string(886) ".....BLOBDATAHERE..."
Notice that I'm using stream_get_contents() to transform the LOB stream into a string. If you're writing a portable application (good luck!) you need to be prepared to handle columns coming back as a stream, even if you didn't explicitly bindColumn and ask for it to be delivered as a LOB. If you're not looking forward to handling that dynamically, you might be interested in setting the STRINGIFY_FETCHES database attribute:
<?php
$db->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
?>
This will convert all columns to strings, regardless of their original type, when fetches. This does not include NULL columns. This does NOT translate data being inserted. Use your brain before deploying this setting!