DB_DataObject and transactions

Ok, this is gonna be long.

The question is: is there a way to use transactions with DB_DataObject?

Let's take a look at the following code:

$person = DB_DataObject::factory('person');
$conn = & $person->getDatabaseConnection();
$conn->autocommit(false);

$mom = DB_DataObject::factory('person');
$mom->name = 'Mary';
$momId = $mom->insert(); //query 1

//this comment is BOOKMARK 1 (the database connection breaks right here)
//do some stuff, then:

$person->momId = $momId;
$person->name = 'Fred';
$person->insert(); //query 2

$conn->rollback();

For an easier understanding I ommitted all the error checkings.

Because DataObjects reuse existent connections, this works great if
the database connection does not die. But what if....what if at the
line BOOKMARK 1 the connection breaks? Then "query 1" is automatically
rolled back (because the connection breaks), then DataObject creates a
new connection, with 'autocommit' being enabled (by default). Then
"query 2" goes through this new connection, is _executed_, and the
last command ($conn->rollback()) has absolutely no effect. So query 1
is rolled back, but query 2 is executed.

The problem is that I can't even get an error message telling me the
connection has broken and an new connection has been created and query
2 goes through this new connection.

An alternative to this problem is the one suggeted by Torsten Roehr,
on 2004-06-14 14:54:27:

> You can do this (simplified):
>
> $errors = 0;
>
> // start transaction
> $db->autocommit(false);
>
> // 1st query
> $result = $db->query($query1);
> if (DB::isError($result)) $errors++;
>
> // 2nd query
> $result = $db->query($query2);
> if (DB::isError($result)) $errors++;
>
> // and so on
>
> // rollback if errors occurred
> if ($errors) {
>     $db->query('ROLLBACK');
> } else {
>     $db->query('COMMIT');
> }
>
> $db->autocommit(true);

Perfect, now if the connection breaks between the two queries I can
catch the error. The divantage of this method is the lack of
DataObjects.

We've got to the point: is there a way to use DB_DataObject in
transactions? Is there a way to check if a new connection has been
established? Or can I 'tell' DataObject not to establish new
connections? I mean instead of establishing a new connection, return
me an error.

Laszlo Hermann.

oh, if only it used exceptions ;)

- try sending
$do->query("BEGIN");
$do->query("COMMIT");
$do->query("ROLLBACK");
- Query intercepts these and calls autocommit/rollback etc. for you.

I dont think (Although I've never tested it)., that DO will actually try
and remake the connection if it's failed.. - If the PEAR::DB Object
exists, it will try and use it, if it fails (due to the database
connection going down) It should just return an error.

so you should be able to check for a false return, and a PEAR:Error in
the _lastError property.

although I'm not that running ROLLBACK on the failed connection would
have much effect..

Regards
Alan






Laszlo Hermann wrote:
> Ok, this is gonna be long.
>
> The question is: is there a way to use transactions with DB_DataObject?
>
> Let's take a look at the following code:
>
> $person = DB_DataObject::factory('person');
> $conn = & $person->getDatabaseConnection();
> $conn->autocommit(false);
>
> $mom = DB_DataObject::factory('person');
> $mom->name = 'Mary';
> $momId = $mom->insert(); //query 1
>
> //this comment is BOOKMARK 1 (the database connection breaks right here)
> //do some stuff, then:
>
> $person->momId = $momId;
> $person->name = 'Fred';
> $person->insert(); //query 2
>
> $conn->rollback();
>
> For an easier understanding I ommitted all the error checkings.
>
> Because DataObjects reuse existent connections, this works great if
> the database connection does not die. But what if....what if at the
> line BOOKMARK 1 the connection breaks? Then "query 1" is automatically
> rolled back (because the connection breaks), then DataObject creates a
> new connection, with 'autocommit' being enabled (by default). Then
> "query 2" goes through this new connection, is _executed_, and the
> last command ($conn->rollback()) has absolutely no effect. So query 1
> is rolled back, but query 2 is executed.
>
> The problem is that I can't even get an error message telling me the
> connection has broken and an new connection has been created and query
> 2 goes through this new connection.
>
> An alternative to this problem is the one suggeted by Torsten Roehr,
> on 2004-06-14 14:54:27:
>
> 
>
>
> Perfect, now if the connection breaks between the two queries I can
> catch the error. The divantage of this method is the lack of
> DataObjects.
>
> We've got to the point: is there a way to use DB_DataObject in
> transactions? Is there a way to check if a new connection has been
> established? Or can I 'tell' DataObject not to establish new
> connections? I mean instead of establishing a new connection, return
> me an error.
>
> Laszlo Hermann.

Thank you for your answer, Alan. You're right, DO does not remake connection
s.

Hoping to contribute to this list, I'm gonna summarize the conclusion:

DB_DataObject and transactions work great. The following example is
tested and it works fine. If a query fails or the connection
disconnects (see commented line), $errors is greater than zero and you
can roll it back.

$errors = 0;

$person = DB_DataObject::factory('person');

$conn = & $person->getDatabaseConnection();
$conn->autocommit();

$mom = DB_DataObject::factory('mom');
$mom->name = 'Mary';
$mom_id = $mom->insert();
if (!$mom_id) $errors++;

//$conn->disconnect();

$person->name = 'Fred';
$person->mom_id	= $mom_id;
$res = $person->insert();
if (!$res) $errors++;

//do some stuff, more queries

if ($errors == 0) $conn->commit();
else $conn->rollback();

Regards,
Laszlo Hermann


On Mon, 20 Sep 2004 17:36:25 +0800, Alan Knowles <alan@akbkhome.com> wrote:
> oh, if only it used exceptions ;)
>
> - try sending
> $do->query("BEGIN");
> $do->query("COMMIT");
> $do->query("ROLLBACK");
> - Query intercepts these and calls autocommit/rollback etc. for you.
>
> I dont think (Although I've never tested it)., that DO will actually try
> and remake the connection if it's failed.. - If the PEAR::DB Object
> exists, it will try and use it, if it fails (due to the database
> connection going down) It should just return an error.
>
> so you should be able to check for a false return, and a PEAR:Error in
> the _lastError property.
>
> although I'm not that running ROLLBACK on the failed connection would
> have much effect..
>
> Regards
> Alan
>
>
>
>
> Laszlo Hermann wrote: 
>
>

Laszlo Hermann wrote:
> $person = DB_DataObject::factory('person');
>
> $conn = & $person->getDatabaseConnection();
> $conn->autocommit();
>


AFAIR - this may fail if you have not disabled overload on PHP4, (due to
bugs in the overload extension) which is why the $do->query('BEGIN') is
the recommended way to do it.

Regards
alan

> $mom = DB_DataObject::factory('mom');
> $mom->name = 'Mary';
> $mom_id = $mom->insert();
> if (!$mom_id) $errors++;
>
> //$conn->disconnect();
>
> $person->name = 'Fred';
> $person->mom_id	= $mom_id;
> $res = $person->insert();
> if (!$res) $errors++;
>
> //do some stuff, more queries
>
> if ($errors == 0) $conn->commit();
> else $conn->rollback();
>
> Regards,
> Laszlo Hermann
>
>
> On Mon, 20 Sep 2004 17:36:25 +0800, Alan Knowles <alan@akbkhome.com> wrote
:
>