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
On Tuesday, October 30, 2018 at 7:41:44 PM UTC+1, Mauricio Schmitz wrote:
>
>
> Hello Marco, You have found the solution to your problem in this case, I
> am having the same problem.
>
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
