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:Movies.sqlite');
$movieID = 1; // or whatever, just for testing

$dbh->beginTransaction();
$selectMovie = $dbh->prepare('
SELECT movie_title
FROM movies
WHERE movie_id = :movieID
');
$selectMovie->bindParam(':movieID', $movieID);
$selectMovie->execute();
$movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
$dbh->commit();
var_dump($movieResult);

$movieTitle = 'Some Other Blah Title';
$dbh->beginTransaction();
$updateMovie = $dbh->prepare('
UPDATE movies
SET movie_title = :movieTitle
WHERE movie_id = :movieID
');
$updateMovie->bindParam(':movieTitle', $movieTitle);
$updateMovie->bindParam(':movieID', $movieID);
$updateMovie->execute();
$dbh->commit();

Re: PDO Error: Already Active Transaction -- Help

On 24 Apr, 12:24, FeelLikeA...@gmail.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:Movies.sqlite');
> $movieID = 1; // or whatever, just for testing
>
> $dbh->beginTransaction();
> $selectMovie = $dbh->prepare('
> SELECT movie_title
> FROM movies
> WHERE movie_id = :movieID
> ');
> $selectMovie->bindParam(':movieID', $movieID);
> $selectMovie->execute();
> $movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
> $dbh->commit();
> var_dump($movieResult);
>
> $movieTitle = 'Some Other Blah Title';
> $dbh->beginTransaction();
> $updateMovie = $dbh->prepare('
> UPDATE movies
> SET movie_title = :movieTitle
> WHERE movie_id = :movieID
> ');
> $updateMovie->bindParam(':movieTitle', $movieTitle);
> $updateMovie->bindParam(':movieID', $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.

Re: PDO Error: Already Active Transaction -- Help

On Apr 24, 8:09 am, "C. (http://symcbean.blogspot.com/)"
wrote:
> On 24 Apr, 12:24, FeelLikeA...@gmail.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:Movies.sqlite');
> > $movieID = 1; // or whatever, just for testing
>
> > $dbh->beginTransaction();
> > $selectMovie = $dbh->prepare('
> > SELECT movie_title
> > FROM movies
> > WHERE movie_id = :movieID
> > ');
> > $selectMovie->bindParam(':movieID', $movieID);
> > $selectMovie->execute();
> > $movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
> > $dbh->commit();
> > var_dump($movieResult);
>
> > $movieTitle = 'Some Other Blah Title';
> > $dbh->beginTransaction();
> > $updateMovie = $dbh->prepare('
> > UPDATE movies
> > SET movie_title = :movieTitle
> > WHERE movie_id = :movieID
> > ');
> > $updateMovie->bindParam(':movieTitle', $movieTitle);
> > $updateMovie->bindParam(':movieID', $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?

Re: PDO Error: Already Active Transaction -- Help

On Thu, 24 Apr 2008 21:17:43 +0200, wrote:

> On Apr 24, 8:09 am, "C. (http://symcbean.blogspot.com/)"
> wrote:
>> On 24 Apr, 12:24, FeelLikeA...@gmail.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 =3D new PDO('sqlite:Movies.sqlite');
>> > $movieID =3D 1; // or whatever, just for testing
>>
>> > $dbh->beginTransaction();
>> > $selectMovie =3D $dbh->prepare('
>> > SELECT movie_title
>> > FROM movies
>> > WHERE movie_id =3D :movieID
>> > ');
>> > $selectMovie->bindParam(':movieID', $movieID);
>> > $selectMovie->execute();
>> > $movieResult =3D $selectMovie->fetch(PDO::FETCH_ASSOC);
>> > $dbh->commit();
>> > var_dump($movieResult);
>>
>> > $movieTitle =3D 'Some Other Blah Title';
>> > $dbh->beginTransaction();
>> > $updateMovie =3D $dbh->prepare('
>> > UPDATE movies
>> > SET movie_title =3D :movieTitle
>> > WHERE movie_id =3D :movieID
>> > ');
>> > $updateMovie->bindParam(':movieTitle', $movieTitle);
>> > $updateMovie->bindParam(':movieID', $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 an=
y
>> 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:
$db =3D new PDO('mysql:host=3Dlocalhost;dbname=3Dtest','******','******' =
);
//the magic:
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//test:
$db->commit();
?>
Results in:
PDOException: There is no active transaction in ....
-- =

Rik Wasmus