Announcement Announcement Module
No announcement yet.
Displaying error codes from stored procedures Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Displaying error codes from stored procedures

    I have an MVC webapp with Oracle DB underneath it. My Dao does an insert and the stored procedure returns a number - ie. 0 if the insert went fine, -1 if the data already exists in the database, -2 if something else occurred etc.

    What I want is for the JSP to display a certain error message based on the code returned from the database, but I don't want the logic to be done on the JSP (ie. have a bunch of c:if to decode which integer was received).

    Ideally, what I'd have in the JSP is something like

    <c:if test="${not empty errorCode}"><fmt:message key="${errorCode}"/></c:if>
    meaning that the errorCode variable would hold a key for the error message.

    My question is - where and how do I put the logic to process the error codes form the database and where and how do I convert them to message keys?

  • #2
    Just a rough sketch:
    • Let the DAOs throw Exceptions instead of returning error codes
    • Create a template for your controllers, that knows how to convert the exceptions to meaningful error messages and puts them into the errors object. Exceptions, that cannot be converted, should be rethrown
    • Wrap your service calls inside the controllers into that template
    • Use Spring's taglib to output the error message


    • #3
      Thanks for the advice!

      Is there an example (like in the PetStore example or something similar) where error handling is done in such way?

      Also, for a successful database operation, the database returns 0. I don't want to throw an exception there (because that doesn't seem natural), but I do want to display a message that the submit was successful, and I want that message to reach the JSP (view) somehow. Any ideas?


      • #4
        No Exceptions = Success??

        May be I am missing something here. If you throw exceptions for all error conditions like Degenaar suggested. Would no exceptions not mean that the submit was successful?
        Senior Member

        Originally posted by slnm View Post
        Also, for a successful database operation, the database returns 0. I don't want to throw an exception there (because that doesn't seem natural), but I do want to display a message that the submit was successful, and I want that message to reach the JSP (view) somehow. Any ideas?
        Last edited by justin_t; Mar 16th, 2010, 01:21 PM. Reason: Fixed typo


        • #5
          Let me make a typical scenario so everyone can see what I'm talking about

          1. there's a view with a table showing data about something (ie, users of an aplication). There's also a link to a form to add new data into the table. The user clicks the link.

          2. there's a form to insert a new user. The required fields are username, user_id, password.

          3. user enters the required field and clicks submit. Form data is then bound to the appropriate command, the controller calls the service layer, service layer calls the database layer which calls a stored procedure to execute the input. The database constraints are that both the username and user_id must be unique.

          4. the stored procedure executes and returns an appropriate result code, ie:
          • 0 - the procedure was executed normally
          • -1 - username already exists
          • -2 - user_id already exists
          • -3 - something went terribly wrong with the database

          5. the database object now holds the result code (0, -1, -2, -3)

          Now, between the database object and the view some magic happens and, depending on the error code, the first view (the one that had a table in it) is displayed, but there's also an appropriate message displayed - the important thing is that a message is displayed no matter what - whether the insert was successful or not (I want the user to know that his input went fine and also show the updated table immediately).

          What was suggested was I throw an exception from the database layer. What I'd rather have is some kind of conversion from the integer code to a message key, ie

          integer -> String containing message properties key -> JSP checking ${resultCode} variable (which contains the message properties key).

          Maybe I'd throw an exception in case of something really going horribly bad on the database.

          As I said, throwing an exception when all goes well seems unnatural, but I still want to display that "All went well" message (but without making an assumption based on the resultCode being empty and displaying the "all is well" message if it is).


          • #6
            Any further suggestions/examples?

            What I did was I now have an object called ResultCode

            public class ResultCode {
            private String resultCodeKey;
            private String resultCodeType;
            getters and setters... }
            and a Constants object that consists of static string variables ie

            public static final String GROUP_EXISTS = "errorCodes.groupExists";
            which are actually message property keys.

            In my DAO, I have for example an insert function which returns a ResultCode, based on what the database procedure returns (ie, if the result from the database is -1, I return ResultCode with resultCodeKey set to GROUP_EXISTS and resultCodeType set to warning).

            Service layer just propagates this to the controller, which then checks whether the type of the result code (ie error, warning, ok) and returns the key to the session.

            In my JSP I then check the variables for error, warning and ok types and based on that display an appropriate message to the user (errors in red, warnings in orange, ok's in green - not really important).

            What I want to know is whether there's anything wrong with this architecture. I'm not sure my DAO should be the one to deal with message keys, as those are strictly presentation related (so I'm guessing the controller should be the one doing that bit of magic). Should the service layer remain dumb about this?

            On the other hand, controller calls the service layer, it has no idea whether it will call one stored procedure or five of them, therefore if I give it just an integer, it will not know which stored procedure returned that integer, hence it won't know what the actual cause of that result is and will not know which message to display.

            I'd like to hear other people's opinions on this and how you handle your inserts, updates and deletes in the DAO layer.