DBAL-202: Preparing Statements outside Transaction #1375

Closed
doctrinebot opened this Issue Jan 15, 2012 · 4 comments

2 participants

@doctrinebot

Jira issue originally created by user @beberlei:

From the mailing list:

I'm using DBAL 2.1 with Oracle and it appears that if I call prepare() for my SQL and cache the prepared statement for later, if I then start a new transaction and call execute() on the statement, it commits the transaction. Is this behavior intentional? If so, does that mean I have to prepare my statement anew for every transaction?

Note that I'm seeing the aforementioned behavior with code as basic as the following:

$stmt = $dbh->prepare("INSERT INTO test_table (id, description) VALUES
(:my*id, :my*desc)");
$dbh->beginTransaction();
$stmt->execute(array(":my*id" => 1, ":my*desc" => "test"));
$dbh->rollBack();

After executing the above, a record has been committed to the db. If I had a more complex scenario involving a transaction with multiple statements where the entire transaction is inside a loop, this becomes problematic--I can't then prepare my statements outside the loop to improve performance.

When I try the same thing using straight PDO, it works fine. Can anyone else confirm this behavior?

@doctrinebot

Comment created by @beberlei:

Are you using PDO_OCI or oci8 with Doctrine?

@doctrinebot

Comment created by dpb587:

I had responded to him on our intranet and intended to submit a patch, but haven't yet found the time to prepare and test one. As a temporary workaround I suggested he prepare the statement inside the transaction. We are using oci8 and the following was my response and analysis:

When the doctrine2 oci8 driver prepares a statement, the generated statement will forever use the active execute mode, regardless of the mode when it's actually executed. I disagree with the current behavior.

As you noted, this is not the behavior used by PDO OCI. Short-term, I think you should prepare the statement inside a transaction. Long-term, I think we should submit a patch to doctrine2, something like follows:

  • add a getExecuteMode to OCI8Connection
  • add a getDriverOptions to OCI8Connection
  • remove the $executeMode parameter from OCI8Statement::**construct
  • remove the $driverOptions parameter from OCI8Statement::**construct
  • store a reference to $dbh to $this->*dbh from OCI8Statement::_*construct
  • replace $this->*executeMode with $this->*dbh->getExecuteMode()
  • replace $this->*driverOptions with $this->*dbh->getDriverOptions()
@doctrinebot

Comment created by dpb587:

Created an independent test - https://gist.github.com/2515100
Submitted a pull request - #137

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added the Bug label Dec 6, 2015
@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment