Tranactions

[tonyce]

Is there a way to get "tranaction" (roll back if any queries in a transaction fail) type functionality when a database operation involves multiple sql commands? For example, an operation where something is done in one table (eg. order added to one table) and totals for an entity (eg. number of widgets have to be adjusted up or down) have to incremented or decremented on another table. So this tranaction involves multiple sql queries, therefore you would want it to roll back if all the sql tranactions did not succeed.

From looking a Dataface, it appears I would have to use triggers to do this sort of thing (without tranaction type functionality). Am I correct in this assumption?

Anyway, either way, transction type functionality would be an important feature.

[njw]

I'm not an expert but the transaction part would be done in MySQL, not Dataface wouldn't it? If you have a look in the MySQL manual you have the capability to create transactions. If you are using MySQL 5, then you could easily create a stored procedure that includes all the transactional stuff?

Neil

[shannah]

Hi Tony,

I haven't been using much in the way of transactions lately, but here goes:

If you're using storage engine that supports transactions (e.g. InnoDB) then you can indeed achieve transactions. Your thought of using triggers to do this would seem suitable to me. For example, add the following to your delegate classes:

function beforeSave(&$record){
mysql_query("start transaction" );
}

function afterSave(&$record){
if (/*test for some condition*/){

mysql_query("commit");
} else {

mysql_query("rollback");
return PEAR::raiseError('Transaction failed', DATAFACE_E_WARNING);
}
}


or something along those lines... you may want to look at the *addRelatedRecord() triggers too, because they involve multiple queries - inserts and updates generally don't.

Hope this helps a little.

-Steve

[tonyce]

Hi Tony,

>

I haven't been using much in the way of transactions lately, but here goes:

>

If you're using storage engine that supports transactions (e.g. InnoDB) then you can indeed achieve transactions. Your thought of using triggers to do this would seem suitable to me. For example, add the following to your delegate classes:

>

function beforeSave(&$record){

> mysql_query("start transaction" );

}

>

function afterSave(&$record){

> if (*test for some condition*/){

>

mysql_query("commit");

> } else {



> mysql_query("rollback");

return PEAR::raiseError('Transaction failed', DATAFACE_E_WARNING);

> }

}

>



>or something along those lines... you may want to look at the *addRelatedRecord() triggers too, because they involve multiple queries - inserts and updates generally don't.



>Hope this helps a little.



>-Steve

Thanks Steve. It did help. Now time to work with it and see how things work out.