Re: DB_DataObject and transactions

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.