CODEWITHSUNDEEP
phporacle-databaseblob
Ingila Ejaz
Ingila Ejaz

Reputation: 233

Oracle Query Inserting Empty Blob

I want to insert a text file in a Blob column of a table. I am using PHP for it. The query I used is:

$qry = "INSERT INTO blobTable(date_input,file_name,blob_CONTENT) VALUES (SYSDATE,'$fileName',empty_blob()) RETURNING blob_CONTENT INTO :blob_CONTENT";
$stmt = OCIparse($conn,$qry);
$blob = ocinewdescriptor($conn, OCI_D_LOB);
ocibindbyname($stmt, ":Blob_CONTENT", $blob, -1, OCI_B_BLOB);
ociexecute($stmt);
$result = oci_num_rows($stmt);
ocicommit($conn);
ocifreestatement($stmt);
$blob->free();

But instead it inserts an Empty Blob in that table. I also tried it with utl_raw.cast_to_raw but it has a limitation of 2K but my files are expected to be > 10M in size.

What should I modify my query with?

Upvotes: 1

Views: 797

Answers (1)

Ingila Ejaz
Ingila Ejaz

Reputation: 233

Ok I got it working by changing the code a bit.

$tmpName  = $_FILES['datafile']['tmp_name']; //Temporary name of my file
$qry = "INSERT INTO fileTable(date_input,file_name,file_CONTENT) VALUES (SYSDATE,'$fileName',empty_blob()) RETURNING file_CONTENT INTO :file_CONTENT";
$stmt = OCIparse($conn,$qry);
$blob = ocinewdescriptor($conn, OCI_D_LOB);
ocibindbyname($stmt, ":file_CONTENT", $blob, -1, OCI_B_BLOB);
ociexecute($stmt, OCI_DEFAULT);
if ($blob->savefile($tmpName)){ 
//Missed this step initially.
    oci_commit($conn);
    echo "Blob successfully uploaded\n";
 }
 else

 {
    echo "Couldn't upload Blob\n";
 }
$result = oci_num_rows($stmt);
ocicommit($conn);
ocifreestatement($stmt);
$blob->free();

Upvotes: 1

Related Questions