PDO Error: Already Active Transaction -- Help

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • FeelLikeANut@gmail.com
    #1

    PDO Error: Already Active Transaction -- Help

    I have the code below. First there is a transaction where I select
    data. I wrapped it in an explicit transaction because in my real
    program I run a couple different selects. Nevertheless, the
    transaction should be closed with the commit command.

    Next, I there is a separate transaction where I update data. But I get
    the error: Uncaught exception 'PDOException' with message 'There is
    already an active transaction'.

    I don't understand why the first transaction is still considered
    active.



    $dbh = new PDO('sqlite:Mov ies.sqlite');
    $movieID = 1; // or whatever, just for testing

    $dbh->beginTransacti on();
    $selectMovie = $dbh->prepare('
    SELECT movie_title
    FROM movies
    WHERE movie_id = :movieID
    ');
    $selectMovie->bindParam(':mo vieID', $movieID);
    $selectMovie->execute();
    $movieResult = $selectMovie->fetch(PDO::FET CH_ASSOC);
    $dbh->commit();
    var_dump($movie Result);

    $movieTitle = 'Some Other Blah Title';
    $dbh->beginTransacti on();
    $updateMovie = $dbh->prepare('
    UPDATE movies
    SET movie_title = :movieTitle
    WHERE movie_id = :movieID
    ');
    $updateMovie->bindParam(':mo vieTitle', $movieTitle);
    $updateMovie->bindParam(':mo vieID', $movieID);
    $updateMovie->execute();
    $dbh->commit();
    Tags: None
    • C. (http://symcbean.blogspot.com/)
      #2
      Re: PDO Error: Already Active Transaction -- Help

      On 24 Apr, 12:24, FeelLikeA...@gm ail.com wrote:
      I have the code below. First there is a transaction where I select
      data. I wrapped it in an explicit transaction because in my real
      program I run a couple different selects. Nevertheless, the
      transaction should be closed with the commit command.
      >
      Next, I there is a separate transaction where I update data. But I get
      the error: Uncaught exception 'PDOException' with message 'There is
      already an active transaction'.
      >
      I don't understand why the first transaction is still considered
      active.
      >
      $dbh = new PDO('sqlite:Mov ies.sqlite');
      $movieID = 1; // or whatever, just for testing
      >
      $dbh->beginTransacti on();
      $selectMovie = $dbh->prepare('
      SELECT movie_title
      FROM movies
      WHERE movie_id = :movieID
      ');
      $selectMovie->bindParam(':mo vieID', $movieID);
      $selectMovie->execute();
      $movieResult = $selectMovie->fetch(PDO::FET CH_ASSOC);
      $dbh->commit();
      var_dump($movie Result);
      >
      $movieTitle = 'Some Other Blah Title';
      $dbh->beginTransacti on();
      $updateMovie = $dbh->prepare('
      UPDATE movies
      SET movie_title = :movieTitle
      WHERE movie_id = :movieID
      ');
      $updateMovie->bindParam(':mo vieTitle', $movieTitle);
      $updateMovie->bindParam(':mo vieID', $movieID);
      $updateMovie->execute();
      $dbh->commit();
      You don't check if the commit was successful but more probably, its
      because you haven't closed the cursor before executing the next exec.
      Also why do you think you need a transaction when you're not doing any
      DML/DDL updates?

      C.

        Comment

        • FeelLikeANut@gmail.com
          #3
          Re: PDO Error: Already Active Transaction -- Help

          On Apr 24, 8:09 am, "C. (http://symcbean.blogsp ot.com/)"
          <colin.mckin... @gmail.comwrote :
          On 24 Apr, 12:24, FeelLikeA...@gm ail.com wrote:
          >
          >
          >
          I have the code below. First there is a transaction where I select
          data. I wrapped it in an explicit transaction because in my real
          program I run a couple different selects. Nevertheless, the
          transaction should be closed with the commit command.
          >
          Next, I there is a separate transaction where I update data. But I get
          the error: Uncaught exception 'PDOException' with message 'There is
          already an active transaction'.
          >
          I don't understand why the first transaction is still considered
          active.
          >
          $dbh = new PDO('sqlite:Mov ies.sqlite');
          $movieID = 1; // or whatever, just for testing
          >
          $dbh->beginTransacti on();
          $selectMovie = $dbh->prepare('
          SELECT movie_title
          FROM movies
          WHERE movie_id = :movieID
          ');
          $selectMovie->bindParam(':mo vieID', $movieID);
          $selectMovie->execute();
          $movieResult = $selectMovie->fetch(PDO::FET CH_ASSOC);
          $dbh->commit();
          var_dump($movie Result);
          >
          $movieTitle = 'Some Other Blah Title';
          $dbh->beginTransacti on();
          $updateMovie = $dbh->prepare('
          UPDATE movies
          SET movie_title = :movieTitle
          WHERE movie_id = :movieID
          ');
          $updateMovie->bindParam(':mo vieTitle', $movieTitle);
          $updateMovie->bindParam(':mo vieID', $movieID);
          $updateMovie->execute();
          $dbh->commit();
          >
          You don't check if the commit was successful but more probably, its
          because you haven't closed the cursor before executing the next exec.
          Also why do you think you need a transaction when you're not doing any
          DML/DDL updates?
          >
          C.
          Yup, closing the cursor was the trick. Thanks.

          About checking the return value of commits... can I configure the PDO
          instance to throw an exception if the operation failed rather than
          checking return values?

            Comment

            • Rik Wasmus
              #4
              Re: PDO Error: Already Active Transaction -- Help

              On Thu, 24 Apr 2008 21:17:43 +0200, <FeelLikeANut@g mail.comwrote:
              On Apr 24, 8:09 am, "C. (http://symcbean.blogsp ot.com/)"
              <colin.mckin... @gmail.comwrote :
              >On 24 Apr, 12:24, FeelLikeA...@gm ail.com wrote:
              >>
              >>
              >>
              I have the code below. First there is a transaction where I select
              data. I wrapped it in an explicit transaction because in my real
              program I run a couple different selects. Nevertheless, the
              transaction should be closed with the commit command.
              >>
              Next, I there is a separate transaction where I update data. But I get
              the error: Uncaught exception 'PDOException' with message 'There is
              already an active transaction'.
              >>
              I don't understand why the first transaction is still considered
              active.
              >>
              $dbh = new PDO('sqlite:Mov ies.sqlite');
              $movieID = 1; // or whatever, just for testing
              >>
              $dbh->beginTransacti on();
              $selectMovie = $dbh->prepare('
              SELECT movie_title
              FROM movies
              WHERE movie_id = :movieID
              ');
              $selectMovie->bindParam(':mo vieID', $movieID);
              $selectMovie->execute();
              $movieResult = $selectMovie->fetch(PDO::FET CH_ASSOC);
              $dbh->commit();
              var_dump($movie Result);
              >>
              $movieTitle = 'Some Other Blah Title';
              $dbh->beginTransacti on();
              $updateMovie = $dbh->prepare('
              UPDATE movies
              SET movie_title = :movieTitle
              WHERE movie_id = :movieID
              ');
              $updateMovie->bindParam(':mo vieTitle', $movieTitle);
              $updateMovie->bindParam(':mo vieID', $movieID);
              $updateMovie->execute();
              $dbh->commit();
              >>
              >You don't check if the commit was successful but more probably, its
              >because you haven't closed the cursor before executing the next exec.
              >Also why do you think you need a transaction when you're not doing any
              >DML/DDL updates?
              >>
              >C.
              >
              Yup, closing the cursor was the trick. Thanks.
              >
              About checking the return value of commits... can I configure the PDO
              instance to throw an exception if the operation failed rather than
              checking return values?
              Probably, I'm not sure how to make a commit fail save for not starting a
              transaction, that works though:
              <?php
              $db = new PDO('mysql:host =localhost;dbna me=test','***** *','******');
              //the magic:
              $db->setAttribute(P DO::ATTR_ERRMOD E,PDO::ERRMODE_ EXCEPTION);
              //test:
              $db->commit();
              ?>
              Results in:
              PDOException: There is no active transaction in ....
              --
              Rik Wasmus

                Comment

                Previous template Next
                Working...