Looking for a clear example about how to use BLOB/CLOB with OCI8

[parktrip]

Dear all,

i have some difficulties to use CLOB with OCI8 driver. I was able to do it with ZF1 like this:

$sql = "INSERT INTO MY_TABLE (COL1, COL2,COLCLOB)  values (:col1, :col2, EMPTY_CLOB()) RETURNING COLCLOB INTO :mylob_loc";
$statement = $this->getAdapter()->prepare($sql);

$blob = oci_new_descriptor($conn, OCI_D_LOB);
        
$statement->bindParam('col1', 'some data');
$statement->bindParam('col2', 'some other data');
$statement->bindParam('mylob_loc', $blob, OCI_B_CLOB);     

$statement->execute();
        
$blob->save($myblobcontent);
 
$blob->free();

with zf2, I am using the ParameterContainer that looks like doing few things like creating some temporaryblob space but it doesn't work.

I tried like this:

$data = array(
   'MYCOL1' => 'somestring',
   'MYCOL2' => 'somestring',
   'mylob' => $someBlobContent
);

$data2 = new ParameterContainer();
$data2->setFromArray($data);
$data2->offsetSetErrata('mylob', ParameterContainer::TYPE_LOB);

$sql = 'INSERT INTO MY_TABLE (MYCOL1,MYCOL2,COLBLOB) VALUES (:MYCOL1,:MYCOL2,:mylob)';

$smtm = $this->getAdapter()->getDriver()->createStatement($sql);
$smtm->setParameterContainer($data2);
$smtm->prepare();
$smtm->execute();

and I have this error:

ORA-22275: invalid LOB locator specified

After searching on the web, I tried this:

$blob = oci_new_descriptor($this->getAdapter()->getDriver()->getConnection()->getResource(), OCI_D_LOB);
$data = array(
	'MYCOL1' => 'some string',
	'MYCOL2' => 'some string',
   'mylob' => $blob
);

$data2 = new ParameterContainer();
$data2->setFromArray($data);
$data2->offsetSetErrata('mylob', ParameterContainer::TYPE_LOB);

$sql = 'INSERT INTO ERN_DOCUMENT_VERSION (MYCOL1,MYCOL2,COLBLOB)
								VALUES (:MYCOL1, :MYCOL2,EMPTY_CLOB())
								RETURNING V_CONTENT INTO :mylob';

$smtm = $this->getAdapter()->getDriver()->createStatement($sql);
$smtm->setParameterContainer($data2);

$smtm->prepare();
$smtm->execute();

$blob->save($fileContent);

I have this

Warning: OCI-Lob::writetemporary() expects parameter 1 to be string, object given in C:\www\ern\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Oci8\Statement.php on line 296

and an exception

File:
C:\www\ern\vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Oci8\Statement.php:260

Message:
ORA-24343: user defined callback error

I don't know if it's me but I can't find example on how to use BLOB columns with zend-db components.

PS: I hope I am not spamming this part of the zend-db repo, if there is a better place to ask my question to zf experts, please tell me.

Thank you in advance,

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#74.