How to call Stored Procedure?

[vrxyz]

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 :)

[asookazian]

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...

[richard.qin]

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

[raphaufrj]

Repetead post... http://www.seamframework.org/Community/HowDoYouCallAStoredProcedure

[jrequioma]

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;               
}