Raise exception after calling stored procedure

[wvreeven]

Hi all,

In our EntityHome subclasses we need to call Oracle stored procedures to persist data to our Oracle database. Using code like this

HibernateSessionProxy hsp = (HibernateSessionProxy)getEntityManager().getDelegate();
CallableStatement stmt = null;
Connection conn = null;
ConnectionManager conManager = null;
try {
     conManager = hsp.getJDBCContext().getConnectionManager();
     conn = conManager.borrowConnection();
     stmt = conn.prepareCall("{call OURPROCEDURE(?,?,?)}");
     stmt.setObject(1, getInstance().getId());
     stmt.registerOutParameter(2, Types.VARCHAR);
     stmt.registerOutParameter(3, Types.VARCHAR);
     stmt.execute();
     message = stmt.getString(19);
     failed = stmt.getString(20);
     if (failed.equals("TRUE")) {
     } else {
          getEntityManager().flush();
          assignId(newId);
          createdMessage();
          raiseAfterTransactionSuccessEvent();
          return "persisted";
     }
} catch (SQLException e) {

     System.out.println("Fout: " + e.getMessage());
} finally {
     if (conn != null) {
          try {
               conManager.releaseBorrowedConnection();
} catch (Throwable th) {
          System.out.println(th.getMessage());
}
}
}
return "";

The question is what to put in the if (failed.equals(TRUE)) block. If I add a faces message it will show in the page that is loaded after the submit button is clicked. However, our users get redirected to an overview page.

How can I make sure that the page in which the user enters a new entity is loaded again with the entered values and a message specifying the error returned by the procedure?

Thanks in advance for your help.

Cheers, Wouter van Reeven

[nickarls]

Well, they don't get redirected automagically?

Catch the exceptions or error conditions and navigate them back to the same page and only go to the summary page if all is ok

[wvreeven]

Well, the redirecting is what I don't know how to do. I guess I am too new to Seam. Should I register my own event and redirect our users by raising that event? Or should I use server side validation? Or is there another option?

Thanks, Wouter

[wvreeven]

OK since I am using JBoss Tools to generate all classes and pages I only needed to modify the navigation rules for the page. They are stored in an accompanying .xml file and I solved it like this

   <navigation from-action="#{customerHome.update}">
             <rule if-outcome="updated">
            <end-conversation/>
            <redirect view-id="/Customer.xhtml"/>
         </rule>
   </navigation>

If I return updated when the stored procedure finishes correctly, I get redirected to the Customer.xhtml page. If I return anything else when the stored procedure doesn't finish correctly, I stay in the same page and see the faces message.

Thanks, Wouter

[samuelesanvito]

Hi,
I can't find documentation on a standard way to call procedures from a EJB3 architecture.
Do I need an Entity Bean and a Session Bean?
I think I'll need something different than an Entity, but what?

Thank you