1 2 Previous Next 20 Replies Latest reply on May 26, 2005 8:44 PM by 806557

    Java is not catching trigger error

    806557
      I am using MS SQL Server 2000.
      In my java code, I am building INSERT statement(s), add them in a batch and then executing the batch.
      Statement stmt = ...;
      String insertSQL = ...;
      try { 
          stmt.addBatch( insertSQL );
      } catch (SQLException sqle) {
          System.out.println( "An error occured while adding batch" );
          sqle.printStackTrace();
      } 
      ...
      try {
          stmt.executeBatch();
          stmt.clearBatch();
      } catch (SQLException sqle) {
          System.out.println( "An error occured while executing batch" );
          sqle.printStackTrace();
      }
      Everythinkg works ok and the data gets updated successfully. Now in this table I put a trigger to be fired. This trigger calls some stored procedures which can update one or more tables. Now if this trigger fails then I want to catch it in my java code (throws SQLException).
      Right now this is not happening. When the trigger runs successfully then every concerned table gets updated properly. But when the trigger fails (I failed it by calling a wrong stored procedure from my stored procedure ) then my java code is not throwing SQLException. What mistake I am making here?

      Please reply. I want to release this code to QA today.

      Thanks
        • 1. Re: Java is not catching trigger error
          796447
          You're asking about how to get your stored procedure (not a Java thing in the first place) to return the appropriate response such that the database driver (JDBC) turns that into an SQLException. I don't know, but that would be a question for database-specific people, not Java people.
          • 2. Re: Java is not catching trigger error
            806557
            whats is the trigger definition
            • 3. Re: Java is not catching trigger error
              806557
              Also, if you execute the statements on the database directly - what is the result.
              Do you get a database error ?
              • 4. Re: Java is not catching trigger error
                806557
                Also, if you execute the statements on the database
                directly - what is the result.
                Do you get a database error ?
                yes
                • 5. Re: Java is not catching trigger error
                  806557
                  You're asking about how to get your stored procedure
                  (not a Java thing in the first place) to return the
                  appropriate response such that the database driver
                  (JDBC) turns that into an SQLException. I don't know,
                  but that would be a question for database-specific
                  people, not Java people.
                  this is a java question because when i execute my INSERT directly on database I got the error message. its java which is not able to catch it throw SQLException.
                  • 6. Re: Java is not catching trigger error
                    DrClap
                    You can insist it's a Java question as much as you like. But the fact is that either your database doesn't report trigger errors to the caller, or the database driver doesn't. So if you want to change that behaviour, you need to change the way the database or database driver behaves.
                    • 7. Re: Java is not catching trigger error
                      806557
                      Trigger calls a stored proc "mainSP" which loops through a table's data (table name is STPROCDEF). this table has a column which stores stored proc names. the "mainSP" then executes each of those stored procs.
                      to make it fail i just have to change the data in STPROCDEF to name a stored proc which is not defined in the database. then when i run it on my database, i got error message :

                      Server: Msg 2812, Level 16, State 62, Line 64
                      Could not find stored procedure 'splitFloaterDays'.
                      The statement has been terminated.
                      • 8. Re: Java is not catching trigger error
                        806557
                        Error messages are not the same as exceptions right?
                        • 9. Re: Java is not catching trigger error
                          806557
                          You can insist it's a Java question as much as you
                          like. But the fact is that either your database
                          doesn't report trigger errors to the caller, or the
                          database driver doesn't. So if you want to change
                          that behaviour, you need to change the way the
                          database or database driver behaves.
                          Question: how can i know if my doesn't report trigger errors to the caller?

                          I am going to test different jdbc driver.
                          • 10. Re: Java is not catching trigger error
                            jschellSomeoneStoleMyAlias
                            But when the trigger fails (I
                            failed it by calling a wrong stored procedure from my
                            stored procedure )
                            What does that mean?
                            then my java code is not throwing
                            SQLException. What mistake I am making here?
                            Via odbc and MS SQL Server, 'errors' have a numeric value. If that numeric value is not within a specific range you will not see a java SQL exception.

                            This occurs most of the time with raiseerror.

                            And batch handling does not necessarily work the same way as a non-batch operation. You might check the warnings rather than waiting for an exception.
                            • 11. Re: Java is not catching trigger error
                              806557
                              DrClap is correct that it isn't a JAVA problem as you will not find a java solution to this problem.

                              The problem lies with the JDBC driver being used or how your DB's triggers report EXCEPTIONS.

                              Triggers are not expected to throw db errors (unchecked), so people normally would not be calling Dynamic SQL from the trigger.

                              If you want to check that all the procedures you want to execute actually exist
                              there are a lot of betters ways to do it. i can think of at least 2.
                              • 12. Re: Java is not catching trigger error
                                796447
                                Actually since this error is from a TRIGGER, I'd suspect that the trigger runs in a separate 'thread' (whatever the term is in the database world) from the statement that you're executing. So I wouldn't expect you to see errors from triggers anyway. And this would be the case regardless of whether you were using a JDBC driver, or some other language. It is not a Java-specific problem as far as I can tell.
                                • 13. Re: Java is not catching trigger error
                                  806557
                                  i guess it depends on the vendor but i have worked on the 4 most populare

                                  oracle, sysbase, db2, sql server and my experience is that a trigger error gets sent up the calling chain no matter what.

                                  but i have never used executeBatch, wonder if it stops executing if the first statement in the batch should generate an error. would think it would right ?
                                  • 14. Re: Java is not catching trigger error
                                    806557
                                    Is this problem related to this thread at all ?

                                    http://forum.java.sun.com/thread.jspa?threadID=628991&tstart=0
                                    1 2 Previous Next