Обсуждение: 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