ZF2 + oracle + How to store LOB object ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

ZF2 + oracle + How to store LOB object ?

OMellinger
This post has NOT been accepted by the mailing list yet.
Hi everyone,

first time I am posting, generally I can find answers by myself but on this one I am stuck.

I am trying to store contents of files in my database using zf2 and oracle.

At the moment with my other models, inserting data in db is quite straightforward using TableGateway like:

in my class extending AbstractTableGateway

----------------------------------
$data = array(
'column1' => 'data',
'column2' => 'data',
);

$this->insert($data);
----------------------------------

Now I defined a column as a BLOB column.

my $data array is now like
----------------------------------
$data = array(
'column1' => 'data',
'column2' => file_get_contents($myfilepath),
);
$this->insert($data);
---------------------------------

I get this exception :

File:

    D:\(...)vendor\zendframework\zendframework\library\Zend\Db\Adapter\Driver\Oci8\Statement.php:260

Message:

    ORA-01461: can bind a LONG value only for insert into a LONG column

I suspect that the model doesn't know I am working with a BLOB column. I saw there is a ParameterContainer but I am lost how how to use it with a class extending AbstractTableGateway

Because I tried
-----------------------------------
$data = array(
'column1' => 'data',
'column2' => file_get_contents($myfilepath),
);

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

$this->insert($data2);
-------------------------------------------

but it doesn't work.

does anyone have a clue on how to deal with that ?

Best regards,

Reply | Threaded
Open this post in threaded view
|

Re: ZF2 + oracle + How to store LOB object ?

OMellinger
This post has NOT been accepted by the mailing list yet.
I found a way to do it but by modifying zf2 code as BLOB are not implemented (only CLOB).

So these are the lines I put in my model extending AbstractTableGateway


----
$data = array(
                                        'ID' => $version->getResourceId(),
                                        'VERSION' => $version->getVersion(),
                                    'CONTENT' => $version->getContent(),
                                        'CREATED_ON' => $version->getCreatedOn(),
                                        'CREATED_BY' => $version->getCreatedBy(),
                        );

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

                        $sql = 'INSERT INTO MYTABLE_VERSION VALUES (:ID, :VERSION, :CONTENT, :CREATED_ON,:CREATED_BY)';
                        $smtm = $this->getAdapter()->getDriver()->createStatement($sql);
                        $smtm->setParameterContainer($data2);
                        $smtm->execute();
----

This was not working as my table column is a B-LOB and not a C-LOB.

Digging into zend\Db code, I found this in 'Zend\Db\Adapter\Driver\Oci8\Statement' in the 'bindParametersFromContainer' method (line 293)
https://github.com/zendframework/zend-db/blob/master/src/Adapter/Driver/Oci8/Statement.php#L293


----
case ParameterContainer::TYPE_LOB:
                        $type = OCI_B_CLOB;
                        $clob = oci_new_descriptor($this->driver->getConnection()->getResource(), OCI_DTYPE_LOB);
                        $clob->writetemporary($value, OCI_TEMP_CLOB);
                        $value = $clob;
                        break;
----

so the TYPE_LOB constant is always a CLOB!

if you change only two lines and change it this way:

----
case ParameterContainer::TYPE_LOB:
                        $type = OCI_B_BLOB;
                        $clob = oci_new_descriptor($this->driver->getConnection()->getResource(), OCI_DTYPE_LOB);
                        $clob->writetemporary($value, OCI_TEMP_BLOB);
                        $value = $clob;
                        break;
----

It is WORKING !!!! Blob field is saved in db, I can retrieve it afterwards, etc ...

so the question is WHY ? Why there is no another ParameterContainer::TYPE_* constant for BLOB with another case statement in the Statement class ?

Now, If I want to use it, I need to create a StatementPrototype for my OCI8 driver, attach it (and I don't know how) and use an extended version of the ParameterContainer class to have a BLOB constant.

Am I missing something ?

(I am using Zf 2.4.8) at the moment

So still need explanations on this please :)