Retun of status code on sql calls.

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

Hi,

I am executing a stored procedure, other than capturing the resultset, is there a way to capture the returned status code from the sql calls made.

Thanks for the replies.

-Gana.

emmanuel · **Posted:** Mon May 10, 2004 12:33 pm

how do you do that in JDBC ?

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

This is how we currently do...

//SPM_SAMPLE is the stored procedure name.
String query = "{ ?=Call SPM_SAMPLE(?) }";

//conn is the Connection object.
CallableStatement stmt = conn.prepareCall(query);

stmt.registerOutParameter(1, java.sql.Types.INTEGER);
stmt.setString(2, sampleInputData);

ResultSet rset = stmt.executeQuery();

int status = rset.getInt(1);

//Is there is any error, status will be set to -1 and status set to 0 when there is no error.

Thanks
Gana.

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

Anybody....does my last note.. get u a clear picture????

-Gana.

christian · **Posted:** Wed May 12, 2004 12:18 pm

How is that related to Hibernate?

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

When Hibernate is supporting stored procedures.. is there a way to capture the return status code from the stored procedure calls.

christian · **Posted:** Wed May 12, 2004 12:22 pm

It doesn't support stored procedures.

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

How about 2.2... ??

christian · **Posted:** Wed May 12, 2004 12:24 pm

Not even alpha.

gana · **Joined:** Tue Apr 27, 2004 11:16 am **Posts:** 23

http://forum.hibernate.org/viewtopic.php?t=930517

Check Michael's reponse.

-Gana

christian · **Posted:** Wed May 12, 2004 12:29 pm

So, checkout v22branch and see for yourself. This is a developer version, you might not be able to use it. We also can't support it on this forum (and I wish my colleagues would stop mentioning it).

AMargheim · **Posted:** Thu Sep 16, 2004 2:40 pm

The company I work for is considering switching from TopLink to Hibernate. We need to call MS-SQL Server and Oracle 9i stored procedures in our applicaitions and I have the responsibility of figuring out how to work around Hibernate v2.x's lack of explicit stored procedure support.

The information in this forum has been quite helpful in coming up to speed on Hibernate, so I would like to add to the body of knowledge here.

I think that a better answer to Gana's original question would have been that a simple change to his JDBC code would have allowed him to retrieve the return value from the stored procedure.

I believe that all he needed to do was change this statement:

int status = rset.getInt(1);

to

int status = stmt.getInt(1);

Below you'll find a method from a Hibernate test application that calls a very simple stored procedure on a MS-SQL 2000 server. The stored procedure just returns the same value that was passed to it in the input parameter. The test application uses Hibernate 2.1.6 and creates a static SessionFactory when the application starts.

Note that this is demo code; its purpose is to demonstrate how to use a connection object from a Hibernate session to call a stored procedure with JDBC and get the return value. The code isn't meant to demonstrate how to deal with the other real-world issues that arise from using stored procedures in a Hibernate application.

Code:

private Integer callSPWithReturnValue(int inValue) {
int retVal=0;
   try {
      // ReturnValue is the stored procedure name. 
      String sqlcall = "{?=Call ReturnValue(?) }"; 
      Session session = sessionFactory.openSession();
      Connection conn = session.connection(); 
      CallableStatement cstmt = conn.prepareCall(sqlcall); 
      // Initialize the parameter values for the call.
      cstmt.registerOutParameter(1, java.sql.Types.INTEGER); 
      cstmt.setInt( 2, inValue);
      Transaction tx = session.beginTransaction();
      // Use the execute() method when the SP does not return a resultset, otherwise use executeQuery.
      cstmt.execute(); 
      tx.commit();
      
      retVal= cstmt.getInt(1); 
   } catch (HibernateException e) {
           e.printStackTrace();
   } catch (SQLException s) {
           s.printStackTrace(); 
   } 
   return new Integer(retVal);
} 

I hope this information will be of use to the other stored procedure users out there in the Hibernate user community.