3 Replies Latest reply: May 11, 2007 8:53 AM by 807606 RSS

    insert - database

    807606
      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
          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
            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
              That helps...


              Thanks a lot...

              Greeshma...