DevOps, CI/CD and Automation

I keep being asked about BLOBs. For posterity, here is my example updated
to use the new PHP 5 OCI8 function names, and using bind variables for the BLOB id.

-- cj

<?php

//
// Sample form to upload and insert an image into an ORACLE BLOB
// column using PHP 5's OCI8 API.  
//
// Note: Uses the new PHP 5 names for OCI8 functions.
//
// Before running this script, execute these statements in SQL*Plus:
//   drop table btab;
//   create table btab (blobid number, blobdata blob);
//
// This example uploads an image file and inserts it into a BLOB
// column.  The image is retrieved back from the column and displayed.
// Make sure there is no whitespace before "<?php" else the wrong HTTP
// header will be sent and the image won't display properly.
//
// Make sure php.ini's value for upload_max_filesize is large enough
// for the largest lob to be uploaded.
//
// Tested with Zend Core for Oracle 1.3 (i.e. PHP 5.0.5) with Oracle 10.2
//
// Based on a sample originally found in
//     http://www.php.net/manual/en/function.ocinewdescriptor.php
//

$myblobid = 1;  // should really be a unique id e.g. a sequence number

define("ORA_CON_UN", "hr");             // username
define("ORA_CON_PW", "hr");             // password
define("ORA_CON_DB", "//localhost/XE"); // connection string

if (!isset($_FILES['lob_upload'])) {
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="POST" 
   enctype="multipart/form-data">
Image filename: <input type="file" name="lob_upload">
<input type="submit" value="Upload">
</form>

<?php
}
else {

  $conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);

  // Delete any existing BLOB so the query at the bottom
  // displays the new data

  $query = 'DELETE FROM BTAB WHERE BLOBID = :MYBLOBID';
  $stmt = oci_parse ($conn, $query);
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  $e = oci_execute($stmt, OCI_COMMIT_ON_SUCCESS);
  if (!$e) {
    die;
  }
  oci_free_statement($stmt);

  // Insert the BLOB from PHP's tempory upload area

  $lob = oci_new_descriptor($conn, OCI_D_LOB);
  $stmt = oci_parse($conn, 'INSERT INTO BTAB (BLOBID, BLOBDATA) '
         .'VALUES(:MYBLOBID, EMPTY_BLOB()) RETURNING BLOBDATA INTO :BLOBDATA');
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  oci_bind_by_name($stmt, ':BLOBDATA', $lob, -1, OCI_B_BLOB);
  oci_execute($stmt, OCI_DEFAULT);

  // The function $lob->savefile(...) reads from the uploaded file.
  // If the data was already in a PHP variable $myv, the
  // $lob->save($myv) function could be used instead.
  if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
    oci_commit($conn);
  }
  else {
    echo "Couldn't upload Blob\n";
  }
  $lob->free();
  oci_free_statement($stmt);

  // Now query the uploaded BLOB and display it

  $query = 'SELECT BLOBDATA FROM BTAB WHERE BLOBID = :MYBLOBID';

  $stmt = oci_parse ($conn, $query);
  oci_bind_by_name($stmt, ':MYBLOBID', $myblobid);
  oci_execute($stmt, OCI_DEFAULT);
  $arr = oci_fetch_assoc($stmt);
  $result = $arr['BLOBDATA']->load();

  // If any text (or whitespace!) is printed before this header is sent,
  // the text won't be displayed and the image won't display properly.
  // Comment out this line to see the text and debug such a problem.
  header("Content-type: image/JPEG");
  echo $result;

  oci_free_statement($stmt);

  oci_close($conn); // log off
}
?>