Re: MDB transactions

Lukas Smith wrote:

From: Jeroen Houben [mailto:jeroen@terena.nl] Sent: Monday, May 12, 2003 3:09 PM

I have a problem using transaction with the latest version of MDB and postgresql 7.2 $db->autoCommit(false); var_dump($db->auto_commit); // this prints bool(false) // some queries ... $db->commit(); That fails giving me this message MDB Error: unknown error Commit: transaction changes are being auto commited

please also check $err_obj->getUserInfo(); this might contain some more information. Okay I think I found the problem. It is in the replace function. What I do is I disable auto_commit to open a transaction block. Inside that block I do some delete calls and some replace calls (which are really also insert/delete blocks within a transaction block).

The problem is that the replace call commits changes and sets auto_commit to true whenever it's finished. even if it's inside an open transaction. 1 AutoCommit: Off 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=1 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 1, 'WRITE') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=19 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 19, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=18 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 18, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=20 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=3 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 3, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=2 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 2, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=5 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 5, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=4 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 4, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=7 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 7, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=6 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 6, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=9 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 9, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=8 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 8, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=11 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 11, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=10 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 10, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=13 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 13, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=12 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 12, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=15 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 15, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=14 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 14, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=17 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 17, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 AutoCommit: Off 1 Query: DELETE FROM permissions WHERE section_id=12 AND group_id=16 1 Query: INSERT INTO permissions (section_id, group_id, perm) VALUES (12, 16, 'READ') 1 Commit Transaction 1 AutoCommit: On 1 Commit Transaction) Then at the end I try and commit my transaction and of course it comes up with the error that changes are being auto committed. My question is why MDB commits the changes and sets the auto_commit to "On" even when it's inside an open transaction? I don't want it to commit anything until I chose to commit the whole lot at once. Or am I overlooking something here? here's my PHP code, just to make it complete ;-) $db->autoCommit(false); foreach ($_POST['perm'] as $group_id => $perm) {

       if ($perm=='NONE') {
           // DELETE permissions FROM TABLE              $query = 'DELETE FROM permissions WHERE section_id='.$db->getIntegerValue($section_id).' AND group_id='.$db->getIntegerValue($group_id);
           $db->query($query);
       } else {
           // insert or update (=REPLACE) permissions in table
           $fields = array
           (
               'section_id' => array
               (
                   'Value' => $section_id,
                   'Type'     => 'integer',
                   'Key'    => true
               ),
               'group_id' => array
               (
                   'Value' => $group_id,
                   'Type'     => 'integer',
                   'Key'    => true
               ),
               'perm' => array
               (
                   'Value' => $perm,
                   'Type' => 'text'
               )
           );          $db->replace('permissions', $fields);
       }

} Jeroen