时间戳

首页 > 解决方案 > 如何使用 PHP 更新 oracle 上的 CLOB 列

php - 如何使用 PHP 更新 oracle 上的 CLOB 列

问题描述

我的功能有问题：

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);
}

执行后我得到“更新”，但在数据库列是空的。在执行之前，我在此列上有 null。

标签： phporacleclob

解决方案

这里有两种方法。架构是：

create table photo_tab (photo blob, photo_id number);

代码如下：

// 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);

您可能想查看 Oracle 的免费 PHP 书籍The Underground PHP and Oracle Manual的后半部分。

推荐阅读