General database programming question
Hi to all!
I want to ask a general database programming question. Here it is...
In a programming language that handles exceptions, where I have to put the
db.commit/db.rollback statement? These are some solutions...
//db is a generic database connection object
/* SOLUTION 1 */
db.begin(); //begin transaction
try:
db.query("UPDATE...."); //Execute a db query
db.commit(); //commit changes to database
except:
//the query has generated an exception
db.rollback();
/* SOLUTION 2 */
db.begin(); //begin transaction
try:
db.query("UPDATE...."); //Execute a db query
except:
//the query has generated an exception
db.rollback();
else:
//Here executes only if there are no exceptions in try statement
db.commit(); //commit changes to database
Which is the best solution according to your experience? Is there others
different solutions?
Thank for the tips...
Regards,
Eng. Denis Gasparin: denis@edistar.com
---------------------------
Programmer & System Administrator - Edistar srl
Denis Gasparin wrote:
Use this:
//db is a generic database connection object
/* SOLUTION 1 */
db.begin(); //begin transaction
try:
db.query("UPDATE...."); //Execute a db query
db.commit(); //commit changes to database
except:
//the query has generated an exception
db.rollback();
because it will catch any problem with the commit. Solution #2 won't.
Of course the ultimate solution is this (syntax altered for clarity,
assuming this is pseudocode anyway):
/* SOLUTION 3 */
db.begin(); //begin transaction
try{
db.query("UPDATE...."); //Execute a db query
db.commit(); //commit changes to database
}
except{
try{
//the query has generated an exception
db.rollback();
}
except{
// You're hosed. Leave and hope for the best
exit;
}
}
if your language allows "nested" try/exception blocks.
--
Matthew O. Persico
http://www.acecape.com/dsl
AceDSL:The best ADSL in Verizon area