[doctrine-user] Symfony2 save file in BLOB via Doctrine Warning:

[m***@dalessandri.it]

I'm trying to save a file in an *Oracle* *BLOB* with symfony2 and Doctrine.

I'm using Symfony 2.7.25, PHP 5.5 and Oracle 11.

I found many others doing the same and adopting their solutions I got a PDO
error

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

I have an entity with the blob field declared as follow:

class AttachmentData{
/**
* @var string
*
* @ORM\Column(name="DATA", type="blob", nullable=true)
*/
private $data;

In my controller I save the file in this way:

$stream= fopen($attachment->getRealPath(),'rb');
$fileData->setData(stream_get_contents($stream));
$file->setData($fileData);
$em->persist($fileData);
$em->flush();

This error makes sense, because in the doctrine documentation at chapter
8.2
http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html#mapping-matrix is
written that the *blob* type is converted i a PHP *resources* but
*stream_get_contents* returns a string.

I finally tryed to pass just the file resources like:

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

$file->setData($stream);

In this case I got no error and the records where saved in the DB, however,
the BLOB field is empty.

Do someone have any idea?

Thanks

Marco

[Mauricio Schmitz]

Hello Marco, You have found the solution to your problem in this case, I am
having the same problem.

[M***@dalessandri.it]

Hello Mauricio,

unfortunatelly I didn't foud a solution for the problem but I found myself a
workaround.

I created an hibryd solution. I have two tables, one contains attachment's
metadata (file name, size, mimetype etc.) and one contains only data (id
metadata record and LOB), thus the lob field.

When I have to save a file I use the Doctrine ORM as much as possible to
persist both entities, however the data entity will be persisted with empty
lob.

After I persisted the records with the relationship but with no lob data, I
call a dedicated service to which I pass the data persisted entity,
AttachmentData.
In the service I used the php oci low level functions to store just the lob
into the oracle table through an UPDATE statement (since the record has
been already persisted by doctrine).

The only think to care about, is to manage the rollback operation.
Since the data are stored in two separated transaction (one from doctrine
and one from oci), a db rollback will affect only the update statement used
to persist the lob. In case of a failure, after calling the rollback, both
entities have to be removed manually calling the remove() function from
doctrine manager.

I adopted this solution in order to use as much as possible the ORM and to
delegate to the oci low level functions only one statement.

Below you can find the save method I implemented in the service.
It returns true in case of success or false in case of failure.

public function save(){
/*
* INSERT DATA Without doctrine.
*/
if(!$this->_connect()) return false;
/**
* Prepare the update statement
*/
$sql = sprintf("UPDATE %s SET data = EMPTY_BLOB() WHERE id = :fileId
RETURNING data INTO :dataLocator", $this->tableName);
$this->stmt = oci_parse($this->connection, $sql);
if(!$this->stmt){
$e = oci_error($this->connection);
$this->logger->error("[AppBundle\Services\SaveAttachment] OCI Parse error:
".$e['message']);
return false;
exit;
}
/**
* Create the LOB object
*/
$dataBLOB = oci_new_descriptor($this->connection, OCI_DTYPE_LOB);
if(!$dataBLOB){
$e = oci_error($this->connection);
$this->logger->error("[AppBundle\Services\SaveAttachment] OCI NEW
Descriptor error: ".$e['message']);
$this->_cleanAndClose();
return false;
exit;
}
/**
* Bind variables
*/
$id = $this->getFileDataEntity()->getId();
if(!oci_bind_by_name($this->stmt, ':fileId', $id, -1)){
$e = oci_error($this->stmt);
$this->logger->error("[AppBundle\Services\SaveAttachment] OCI BIND error:
".$e['message']);
$this->_cleanAndClose();
return false;
exit;
}
if(!oci_bind_by_name($this->stmt, ':dataLocator', $dataBLOB, -1,
OCI_B_BLOB)){
$e = oci_error($this->stmt);
$this->logger->error("[AppBundle\Services\SaveAttachment] OCI BIND error:
".$e['message']);
$dataBLOB->free();
$this->_cleanAndClose();
return false;
exit;
}
/**
* Execute the statement
*/
if(!oci_execute($this->stmt, OCI_DEFAULT)){
$e = oci_error($this->stmt);
$this->logger->error("[AppBundle\Services\SaveAttachment] OCI Execute
error: ".$e['message']);
$dataBLOB->free();
$this->_cleanAndClose();
return false;
exit;
}
/**
* Import the file content into the LOB
*/
if (!$dataBLOB->import($this->getFullPath())) {
$error = oci_error($this->connection);
oci_rollback($this->connection);
$dataBLOB->free();
$this->_cleanAndClose();
$this->logger->error("[AppBundle\Services\SaveAttachment] LOB DATA load
failed. Error: ". $error['message']);
return false;
exit();
}
$dataBLOB->free();
$this->_cleanAndClose();
return true;
}

Below there is a snipped of code I used in the controller to save a file.
/*
* CREATE AND PERSIST ENTITIES VIA DOCTRINE
*/

$attachmentMetadata= new AttachmentMetadata();
$attachmentMetadata->setFilename(...);
$attachmentMetadata->setMimetype(...);
$attachmentMetadata->setFileSize(...);
...
$attachmentData = new AttachmentData();
$attachmentData->setAttachmentMetadata($attachmentMetadata);

$em->persist($attachmentData);
$em->persist($attachmentMetadata);
$em->flush();

/*
* CREATE THE SERVICE INSTANCE
*/

$tableName = $em->getClassMetadata(get_class($attachmentData)
)->getTableName();

$saveAttachment = $this->get('app.save.attachment');
$saveAttachment->setTableName($tableName);

// Passing the data entity to be used to get the id of already persisted
record

$saveAttachment->setFileDataEntity($attachmentData);

// $attachment is an instance
of Symfony\Component\HttpFoundation\File\UploadedFile
$saveAttachment->setFileUploadWrapper($attachment);

$saveAttachment->setContainer($this->container);

/*
* SAVE THE LOB AND REMOVE ENTITIES IN CASE OF FAILURE
*/
if (!$saveAttachment->save()){
$em->remove($attachmentData);
$em->remove($attachmentMetadata);
$em->flush();
}




I hope thi will help you

Marco Dalessandri

Post by Mauricio Schmitz
Hello Marco, You have found the solution to your problem in this case, I
am having the same problem.

[Mauricio Schmitz]

Hello Marco, Thanks for your comeback, So I'll do a routine separates just
to save the blobs. Also using a rawquery

Big hug