PDO Error: Already Active Transaction -- Help

[FeelLikeANut]

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();

[C. (http://symcbean.blogspot.com/)]

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.

[FeelLikeANut]

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?

[Rik Wasmus]

On Thu, 24 Apr 2008 21:17:43 +0200, <Fe**********@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