4 Replies Latest reply on Nov 25, 2010 7:54 AM by jrequioma jrequioma

    How to call Stored Procedure?

    Vrxy  Newbie

      Hi,


      I'm new in learning web application. I just want to know how to call a stored procedure from Oracle database in Seam framework? Do I need to use persistence framework like Hibernate to do that? Thank you in advance :)

        • 1. Re: How to call Stored Procedure?
          Arbi Sookazian Master

          You can use CallableStatement from JDBC API directly (although direct JDBC API usage in JPA apps is frowned upon).  Hibernate supports sprocs as well.


          Read JPwH or use google to learn more...

          • 2. Re: How to call Stored Procedure?
            Richard Qin Newbie

            if you want to call a stored procedure in Seam Framework, you need to use JPA or Hibernate.

            • 4. Re: How to call Stored Procedure?
              jrequioma jrequioma Newbie

              Hi,

              I am new to SEAM and i got a requirement to execute an oracle sproc from a seam-gen'ed Form which i replaced the code from the discussion here to try out for myself. But there are some error messages displayed in my eclipse IDE saying:

              The method execute(ConnectionCallback) is undefined for the type SimpleJdbcTemplate
              for this line:
              BigDecimal result = (BigDecimal) template.execute(connectionCallBack);


              What am i missing here?
              So far i don't see a consolidated listing of the sample code. So i just tried to patch up as per advice. Did i patch correctly?


              Below is my action code:




              package com.mydomain.testapp.action;
              
              import java.io.Serializable;
              import java.sql.CallableStatement;
              import java.sql.Connection;
              import java.sql.Types;
              import java.math.BigDecimal;
              
              import org.hibernate.sql.Template;
              import org.jboss.seam.annotations.In;
              import org.jboss.seam.annotations.Name;
              import org.springframework.jdbc.core.ConnectionCallback;
              import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
              
              @Name("testspcall")
              public class Testspcall implements Serializable
              {
              
                 @In("#{jdbcTemplate}")
                      private transient static SimpleJdbcTemplate template;
              
              @SuppressWarnings("unchecked")
              public static BigDecimal calcDistance(final String zipcode, final BigDecimal lat, final BigDecimal lon) 
              {
                      ConnectionCallback connectionCallBack = new ConnectionCallback() {
                              @Override
                              public Object doInConnection(Connection conexion_db)
                                              {
                                                      BigDecimal result;
                                                      CallableStatement statement = conexion_db.prepareCall("{call CalcDistance(?, ?, ?, ?)}");
                                  statement.setString(1, zipcode);
                                  statement.setBigDecimal(2, lat);
                                  statement.setBigDecimal(3, lon);
                                  statement.registerOutParameter(4, Types.DOUBLE);
                                  statement.execute();
                                  result = statement.getBigDecimal(4);
                                  return result;
                                              }
              
                      };
              
              BigDecimal result = (BigDecimal) template.execute(connectionCallBack);
              return result;               
              }