Jump to content

Uploading and retrieving multiple BLOB columns


Recommended Posts

I tested this sample code and everything is fine to upload and display a BLOB.

<?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", "user1");             // username
define("ORA_CON_PW", "password1");             // password
define("ORA_CON_DB", "dbname"); // 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
}
?>

 

But what I would like to do now is have 2 seperate pages: One that uploads an image into the BLOB column for a specific row (already created; it is a table for Employee data. Employee ID, Pic, Name) and then gives a confirmation, and another page that queries the entire Employee data table and displays all the columns.

 

The above code only allows for one upload and one display. What steps would I take in order to accomplish this? For the latter page, I assume the query would be:

 

$query = 'SELECT * FROM BTAB';

But, do I still have to do special stuff to view the images properly? Or will that return just fine after blobs are uploaded?

 

Thanks in advance for any replies.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.