Finally, handle pdo_oci BLOB INSERT/UPDATE successfully...

Submitted by hswong3i on Tue, 2008/01/29 - 15:08

Oracle
PHP

Finally I give a hack at the end of Siren 1.x (Drupal 6.x) install.pdo_oci.inc, and able to complete the task for BLOB insertion with Oracle + PDO. Don't laugh at this minor research progress: since I just consider to give up the implementation of CLOB, but replace it with the use of BLOB, a successful insertion of BLOB in pdo_oci can ensure we will able to ship Drupal 7.x with PDO supporting, even including Oracle support ;-)

Technical references:
http://netevil.org/uuid/4365876a-cee9-3009-7726-365876a51802
http://www.php.net/manual/en/ref.pdo.php#pdo.lobs

Here are some code snippets, at the tail of install.pdo_oci.inc (I will soon remove them before commit it to Siren's CVS, so just keep in here as reference):

<?php
  $query = 'CREATE TABLE "drupal_install_test" ("id" NUMBER(38) NOT NULL, "blob" BLOB)';
  $stmt = $connection->prepare($query);
  $stmt->execute();

  $query = 'INSERT INTO "drupal_install_test" ("id", "blob") VALUES (:id, EMPTY_BLOB()) RETURNING "blob" INTO :blob';
  $stmt = $connection->prepare($query);
  $stmt->bindValue(':id', 1);
  $stmt->bindParam(':blob', $blob, PDO::PARAM_LOB);
  $blob = NULL;
  $connection->beginTransaction();
  $stmt->execute();
  _var_dump($blob);
  fwrite($blob, "This is a BLOB INSERT action.");
  fclose($blob);
  $connection->commit();

  $query = 'SELECT * FROM "drupal_install_test" WHERE "id" = 1';
  $stmt = $connection->prepare($query);
  $stmt->execute();
  $object = $stmt->fetch(PDO::FETCH_OBJ);
  $blob = stream_get_contents($object->blob);
  _var_dump($blob);

  $query = 'UPDATE "drupal_install_test" SET "blob" = EMPTY_BLOB() WHERE "id" = :id RETURNING "blob" INTO :blob';
  $stmt = $connection->prepare($query);
  $stmt->bindValue(':id', 1);
  $stmt->bindParam(':blob', $blob, PDO::PARAM_LOB);
  $blob = NULL;
  $connection->beginTransaction();
  $stmt->execute();
  _var_dump($blob);
  fwrite($blob, "This is a BLOB UPDATE action");
  fclose($blob);
  $connection->commit();

  $query = 'SELECT * FROM "drupal_install_test" WHERE "id" = 1';
  $stmt = $connection->prepare($query);
  $stmt->execute();
  $object = $stmt->fetch(PDO::FETCH_OBJ);
  $blob = stream_get_contents($object->blob);
  _var_dump($blob);

  $query = 'DROP TABLE "drupal_install_test"';
  $stmt = $connection->prepare($query);
  $stmt->execute();
  exit;
?>