Page 1 of 1

[SOLVED]Can't retrieve BLOB data as file

Posted: Wed Jul 13, 2011 2:02 pm
by vaughtg
I have an app that requires uploading files of various formats (.txt, .doc, .jpg and .pdf) into our Oracle database. I believe I'm uploading and storing them as BLOB data, but I can't pull them back out and display them properly. Here is the test code that I'm using to do this:

Code: Select all

  public function loadBLOB($filePurpose, $fileType, $fileName){
    if(!isset($_SESSION['user'])) $_SESSION['user']=1;
    $sequence = "blobs_seq";
    $myblobid = $this->getNextId($sequence);
    $conn = $this->getConnection();
    // Delete any existing BLOB so the query at the bottom
    // displays the new data

    $query = 'DELETE FROM blobTab WHERE FILEPURPOSE = :FILEPURPOSE '.
        'AND USERID = :USERID';
    $stid = oci_parse ($conn, $query);
    if(!$stid){
      $err = oci_error($stid);
      $this->setOciErrorMsg($err);
      return;
    }
    oci_bind_by_name($stid, ':FILEPURPOSE', $filePurpose);
    oci_bind_by_name($stid, ':USERID', $_SESSION['user']);
    $e = oci_execute($stid, OCI_COMMIT_ON_SUCCESS);
    if(!$e){
      $err = oci_error($e);
      $this->setOciErrorMsg($err);
      return;
    }
    oci_free_statement($stid);

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

    $lob = oci_new_descriptor($conn, OCI_D_LOB);
    $stid = oci_parse($conn, 'INSERT INTO blobTab (BLOBID, FILETYPE, '.
        'USERID, FILEPURPOSE, FILENAME, FILEDATA) VALUES (:MYBLOBID, '.
        ':FILETYPE, :USERID, :FILEPURPOSE, :FILENAME, EMPTY_BLOB()) '.
        'RETURNING FILEDATA INTO :BLOBDATA');
    if(!$stid){
      $err = oci_error($stid);
      $this->setOciErrorMsg($err);
      return;
    }
    oci_bind_by_name($stid, ':MYBLOBID', $myblobid);
    oci_bind_by_name($stid, ':FILEPURPOSE', $filePurpose);
    oci_bind_by_name($stid, ':FILETYPE', $fileType);
    oci_bind_by_name($stid, ':USERID', $_SESSION['user']);
    oci_bind_by_name($stid, ':FILENAME', $fileName);
    oci_bind_by_name($stid, ':BLOBDATA', $lob, -1, OCI_B_BLOB);
    $success = oci_execute($stid, OCI_DEFAULT);
    if(!$success){
      $err = oci_error($stid);
      $this->setOciErrorMsg($err);
    }

    // The function $lob->savefile(...) reads from the uploaded file.
    if ($lob->savefile($_FILES['lob_upload']['tmp_name'])) {
      oci_commit($conn);
    }else{
      echo "Couldn't upload Blob\n";
    }
    $lob->free();
    oci_free_statement($stid);

    // Now query the uploaded BLOB and display it

    $query = 'SELECT FILEDATA, FILETYPE, FILENAME FROM blobTab WHERE '.
        'BLOBID = :MYBLOBID';

    $stid = oci_parse ($conn, $query);
    if(!$stid){
      $err = oci_error($stid);
      $this->setOciErrorMsg($err);
      return;
    }
    oci_bind_by_name($stid, ':MYBLOBID', $myblobid);
    $success = oci_execute($stid, OCI_DEFAULT);
    if(!$success){
      $err = oci_error($stid);
      $this->setOciErrorMsg($err);
    }
    $arr = oci_fetch_assoc($stid);
    $result = $arr['FILEDATA']->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("Pragma: no-cache");
    header("Content-type: ".$arr['FILETYPE']);
    header('Content-Disposition: attachment; filename="'.$arr['FILENAME'].'"');
    echo $result;

    oci_free_statement($stid);

    oci_close($conn); // log off
  }
I don't know how to verify that the data being stored in the BLOB is good without pulling it back out and displaying the file and loading it for display. What I'm getting back appears VERY much like I'm trying to open a binary file in a text editor - a lot of ASCII and illegal characters.

Can someone point me in an appropriate direction to get past this obstacle?

Re: Can't retrieve BLOB data as file

Posted: Wed Jul 13, 2011 2:38 pm
by vaughtg
When scammin' someone else's code - READ THE COMMENTS. They have a purpose.

Code: Select all

    // 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.
The page that was calling my function had a single carriage-return/newline character at the top of the file. I removed that and it works well.

:banghead: :crazy: :banghead: