CODEWITHSUNDEEP
phporacle-databaseoracle11g
JMW
JMW

Reputation: 7807

how to update a LOB with PHP in oracle? (OCI_INVALID_HANDLE)

i'm trying to do an update a (C)LOB in the same way i do an insert. (insert is already working).

$queryHandle = oci_parse($dbHandle, "update MYTABLE set "MYCLOB" = EMPTY_CLOB() , "OTHERCOL" = :col0 where "PKIDCOL" = :wherecol0 returning "OTHERCOL" , "MYCLOB", into :retcol0 , :retcol1");
if(!is_resource($queryHandle)) {
$error=oci_error($dbHandle);
die($error['message'], $error['code']);
}
oci_bind_by_name($queryHandle, ":col0", $othercolvalue);
oci_bind_by_name($queryHandle, ":wherecol0", $pkidcol);
oci_bind_by_name($queryHandle, ":retcol0", $retcol1, 100);
$lob=oci_new_descriptor($dbHandle);
oci_bind_by_name($queryHandle, ":retcol1", $lob, -1, SQLT_CLOB);
if(!oci_execute($queryHandle , OCI_NO_AUTO_COMMIT)) {
$error=oci_error($dbHandle);
die($error['message'], $error['code']);
}
$lob->save($mylobvalue);  // gives an  PHP Warning:  OCI-Lob::save(): OCI_INVALID_HANDLE in file.php on line 123

this does not update the lob and gives an: PHP Warning: OCI-Lob::save(): OCI_INVALID_HANDLE in file.php on line 123

Upvotes: 1

Views: 5243

Answers (2)

shadyyx
shadyyx

Reputation: 16055

There are dozens of examples of how to insert/update a row with a LOB value.

First of all, you have to create a LOB descriptor:

$desc = oci_new_descriptor($connection, OCI_DTYPE_LOB);

Then use this descriptor as your binding value for the LOB placeholder:

oci_bind_by_name($queryHandle, ":retcol1", $desc, -1, SQLT_CLOB);

Then temporary write the data:

$desc->writeTemporary($data);

And after that execute that query...

More information could be find at the documentation + read carefully all the comments for more examples!

Some examples use $desc->write($data); + execute + commit, some use execute + $desc->saveFile($data); + commit, all should work.

Upvotes: 2

JMW
JMW

Reputation: 7807

As PLB said: the update query did not update any rows due to a wrong primary key value. This led to the php warning message.

http://www.oracle.com/technetwork/articles/fuecks-lobs-095315.html

Upvotes: 0

Related Questions