Symfony - Doctrine - Cannot store a blob file in Oracle database

user_2147426 0
Viewed 482

I want to store uploaded files in Oracle database .

I'm using Doctrine in Symfony 5. I made an entity "Attachment", with a property "filename" (type string) and "filecontent" (type blob).

The following code is in controller, to get files uploaded, transform file content in stream, and store it in database

$attachments = $form->get('attachments')->getData();
if (count($attachments) > 0) {
    foreach ($attachments as $attachment) {

        $attach = new Attachment();

        $attach->setFilename($attachment->getClientOriginalName());
        $strm = fopen($attachment->getRealPath(), 'rb');
        $attach->setFilecontent(stream_get_contents($strm));

        $em->persist($attach);
    }
}

When i submit the form, i have the error :

Warning: PDOStatement::execute(): supplied argument is not a valid stream resource

enter image description here

On a MySQL database, all is allright. File is correctly stored in database, no "stream ressource" issue. I've found an old workaround here : https://groups.google.com/g/doctrine-user/c/JILLBji__MU but maybe there is a final solution to this problem.

Could you help me ? Thanks

symfony doctrine-orm
1 Answers

Oracle and doctrine have not a good compatibility.
So I do it in php and native SQL to do the trick
More information here:
https://www.php.net/manual/en/pdo.lobs.php

In your repository, it's looks like this :

$conn = $this->getEntityManager()->getConnection();
// Get Next Sequence
$sqlSeq  = "SELECT DOCUMENT_FILE_id_seq.nextval FROM DUAL";
$stmtSeq = $conn->prepare($sqlSeq);
$stmtSeq->execute();
$nextId  = (int)$stmtSeq->fetchFirstColumn()[0];

$fp = fopen($attachment->getRealPath(), 'rb');

// Insert file
$sql  = "INSERT INTO DOCUMENT_FILE (id, file_content) VALUES (?,EMPTY_BLOB()) RETURNING file_content INTO ?";
$stmt = $conn->prepare($sql);
$stmt->bindParam(1,  $nextId, \PDO::PARAM_INT);
$stmt->bindParam(2, $fp, \PDO::PARAM_LOB);
$conn->beginTransaction();
$stmt->execute();
$conn->commit();

That work for me.

user_58727210
Related