This discussion is archived
3 Replies Latest reply: May 11, 2007 6:53 AM by 807606 RSS

insert - database

807606 Newbie
Currently Being Moderated
Hello All...

When my jsp page is submitted I have to perform 3 insert statements so as to insert records into several tables in the database.But how to make sure that all the 3 inserts are performed correctly...and how to rollback if any one of them fails...

Help is greatly appreciated...

Thanks,
Greeshma....
  • 1. Re: insert - database
    807606 Newbie
    Currently Being Moderated
    you have to build a structure (made of regex, in general) to make sure the data entered by the user is necessarily correct

    this way, you will launch the insert if and only if the user has filled the form correctly
  • 2. Re: insert - database
    794254 Newbie
    Currently Being Moderated
    The Connection object has a method, setAutoCommit() and a property autoCommit.
    con.setAutoCommit(false);
    would set turn off auto commit. So all data would be entered into the database, but not committed. If you think everything is ok, you would call,
    con.commit();
    else, in case exceptions are thrown, call,
    con.rollback();
    j

    This basically allows you to make transactions on the database and rollback if it fails.

    By the way, you should turn on auto commit once you finish with your transaction so that at other places, you do not accidently use a connection that you will need to explicitly call commit() on.

    Your code should be something like this:
    try
    {
        con.setAutoCommit(false);
        try
        {
    
            //execute your preparedstatement 
    
            //once you're done, i.e after your loop or whatever and you're sure you've entered all data, commit
            con.commit();
        }
        catch ( Exception e )
        {
            con.rollback(); //undo whatever inserts may have happened
        }
    }
    catch ( Exception e )
    {
        System.out.println("Unable to turn off auto-commit: " + e );
    }
    finally
    {
        //try to put autocommit back on
    
        try
        {
            con.setAutoCommit(true);
           //not sure but I think an exception is thrown if it is already true
        }
        catch ( Exception e )
        {
            System.out.println("Failed to turn on autocommit: " + e );
        }
    }
  • 3. Re: insert - database
    807606 Newbie
    Currently Being Moderated
    That helps...


    Thanks a lot...

    Greeshma...