Skip to content
Advertisement

How do I update CLOB column on oracle with PHP

I have problem with function:

function updateClob($id_tow,$description_csv,$conn) {
    $sql = "UPDATE SHOPPER_PRODUCTS SET SHOP_TO_DESCRIPTION = EMPTY_CLOB() WHERE ID_TOW = 
            '$id_tow' RETURNING SHOP_TO_DESCRIPTION INTO :lob";
    echo $sql."n";
    //echo $sql."n";
    $clob = OCINewDescriptor($conn, OCI_D_LOB);
    $stmt = OCIParse($conn, $sql);
    OCIBindByName($stmt, ':lob', $clob, -1, OCI_B_CLOB);
    OCIExecute($stmt,OCI_DEFAULT);
    if($clob->save($description_csv)){
      OCICommit($conn);
      echo " Updated"."n";
    }else{
      echo " Problems: Couldn't upload Clob.  This usually means the where 
            condition had no match n";
    }
    $clob->free();
    OCIFreeStatement($stmt);
}


After execute I get “Updated” but in database column is empty. Before execute i have null on this column.

Advertisement

Answer

Here are two ways. The schema is:

create table photo_tab (photo blob, photo_id number);

And the code would be like:

// Update an existing BLOB

$photo_id = 1;
$stid = oci_parse($con, "insert into photo_tab (photo, photo_id) values(empty_blob(), :photo_id)");
$r = oci_bind_by_name($stid, ":photo_id", $photo_id);
$r = oci_execute($stid, OCI_DEFAULT);
oci_commit($con);


$sql = "update photo_tab set photo = empty_blob() where photo_id = :photo_id returning photo into :photo";
$stid = oci_parse($con, $sql);
$blob = oci_new_descriptor($con, OCI_D_LOB);
oci_bind_by_name($stid, ":photo_id", $photo_id);
oci_bind_by_name($stid, ":photo", $blob, -1, OCI_B_BLOB);
$r = oci_execute($stid, OCI_NO_AUTO_COMMIT);
$blob->savefile("/tmp/oraclelogo.jpg");
$blob->free();
oci_commit($con);
oci_free_statement($stid);

// Update an existing BLOB using a Temp BLOB

$photo_id = 2;
$stid = oci_parse($con, "insert into photo_tab (photo, photo_id) values(empty_blob(), :photo_id)");
$r = oci_bind_by_name($stid, ":photo_id", $photo_id);
$r = oci_execute($stid, OCI_DEFAULT);
oci_commit($con);


$sql = "update photo_tab set photo = :photo where photo_id = :photo_id";
$stid = oci_parse($con, $sql);
$blob = oci_new_descriptor($con, OCI_D_LOB);
oci_bind_by_name($stid, ":photo_id", $photo_id);
oci_bind_by_name($stid, ":photo", $blob, -1, OCI_B_BLOB);
$blob->writeTemporary("/tmp/oraclelogo.jpg");
$r = oci_execute($stid, OCI_NO_AUTO_COMMIT);
$blob->close();
oci_commit($con);
oci_free_statement($stid);

You may want to look at the second half of Oracle’s free PHP book The Underground PHP and Oracle Manual.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement