CODEWITHSUNDEEP
phpstored-proceduresplsqlblob
TenG
TenG

Reputation: 4004

Writing BLOB in PHP to Oracle via stored procedure

Here is a table in Oracle:

CREATE TABLE upload_blob ( id NUMBER, doc BLOB );
CREATE SEQUENCE doc_id;

Now a stored procedure:

CREATE OR REPLACE PROCEDURE prUpload ( p_id OUT NUMBER, p_doc IN BLOB ) AS
BEGIN
   INSERT INTO upload_blob ( id, doc ) VALUES ( doc_id.NEXTVAL, p_doc )
   RETURNING id INTO p_id;
END;

Now I want to insert in the table from PHP. The examples I've seen do this via an INSERT into the table using EMPTY_BLOB, and then populate the OCI_D_LOB type variable with the content.

However, in my case direct access to the tables is not liked, hence why create the store procedure.

My PHP code is as follows:

       $sql = <<<EOF
begin
   pr_upLoad
   ( 
      p_doc           => :p_doc_blob,
      p_id            => :p_doc_id
   );
end;
EOF;

   $stmt = oci_parse ( $db_conn, $sql );

   $docId = "";

   $blob = oci_new_descriptor ( $db_conn, OCI_D_LOB);
   $file = file_get_contents($_FILES['docName']['tmp_name']);
   $blob->write ( $file );

   oci_bind_by_name ( $stmt, ':p_doc_id', $docId, 20); 
   oci_bind_by_name ( $stmt, ':p_doc_blob', $blob, -1, OCI_B_BLOB);

   if ( oci_execute ( $stmt, OCI_DEFAULT ) && empty ($errMsg) )
   {
      oci_commit($db_conn);
      $msg = "Document " . $docId . " upload successful.";
   }
   else
   {
      $msg = "Document Save  failed.";
   }

   $blob->free();
   oci_free_statement($stmt);

This gibes me the following error:

Warning: OCI-Lob::write(): OCI_INVALID_HANDLE in saveDoc.php on line 54

Line 54 being the following.

$blob->write ( $file );

I have tried changing the stored procedure to create an EMPTY_BLOB in the INSERT and return the EMPTY_BLOB, and then do the $blob->write after the call, followed by a commit, but this too gve a similar error.

What am I doing wrong?

Upvotes: 2

Views: 2020

Answers (1)

TenG
TenG

Reputation: 4004

It seems BLOBS as parameters to procedures is not liked by PHP.

Creating a temp staging table with a BLOB column, INSERT into this with a EMPTY_BLOB, map that blob to the PHP variable and then write works.

This is poor from a point of view of not wanting to expose tables directly to the account that the PHP connects as. Hence why I use stored procedures.

Upvotes: 1

Related Questions