Re: DB_DataObject and transactions

Thank you for your answer, Alan. You're right, DO does not remake connections. 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: > > 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 disadvantage 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. > >