Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-202

Preparing Statements outside Transaction

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Drivers
    • Security Level: All
    • Labels:
      None

      Description

      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?

        Activity

        Hide
        Benjamin Eberlei added a comment -

        Are you using PDO_OCI or oci8 with Doctrine?

        Show
        Benjamin Eberlei added a comment - Are you using PDO_OCI or oci8 with Doctrine?
        Hide
        Danny Berger added a comment -

        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()`
        Show
        Danny Berger added a comment - 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. https://github.com/doctrine/dbal/blob/b066e54fa048952b4ca71e7e609707d66cd7448e/lib/Doctrine/DBAL/Driver/OCI8/OCI8Connection.php#L60 https://github.com/doctrine/dbal/blob/b066e54fa048952b4ca71e7e609707d66cd7448e/lib/Doctrine/DBAL/Driver/OCI8/OCI8Statement.php#L182 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()`
        Hide
        Danny Berger added a comment -

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

        Show
        Danny Berger added a comment - Created an independent test - https://gist.github.com/2515100 Submitted a pull request - https://github.com/doctrine/dbal/pull/137

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Benjamin Eberlei
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: