Announcement Announcement Module
Collapse
No announcement yet.
Using StoredProcedure Spring libraries in Seam app Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using StoredProcedure Spring libraries in Seam app

    Implementation-Title: Spring Framework
    Implementation-Version: 2.0.8
    Spring-Version: 2.0.8

    I am getting SQLServerException stating there are four input params (proc only takes two). It happens if you uncomment the commented lines in the SeamJdbcTemplate() constructor method. Why and how to fix?

    Code:
    public SeamJdbcTemplate(DataSource ds){
    		super(ds, SPROC_NAME);
    		
    		declareParameter(new SqlParameter("SERIAL_NUMBER", Types.VARCHAR));
    		declareParameter(new SqlParameter("SITE_ID", Types.INTEGER));
    		//declareParameter(new SqlOutParameter("ITEM_NUMBER", Types.VARCHAR));
    		//declareParameter(new SqlOutParameter("MAC_ADDRESS", Types.VARCHAR));
    		declareParameter(new SqlReturnResultSet("resultSet", new RowMapperResultSetExtractor(rowMapper, 1)));
    		compile();
    
    
    	}
    	
    	@SuppressWarnings("unchecked")
    	public Map execute(String serialNumber, Integer siteId) {
    		Map inParams = new HashMap();
    		inParams.put("SERIAL_NUMBER", serialNumber);
    		inParams.put("SITE_ID", siteId);
    		Map outParams = super.execute(inParams);
    		if (outParams.size() > 0){
    			log.info("ITEM_NUMBER = "+outParams.get("ITEM_NUMBER"));
    			log.info("MAC_ADDRESS = "+outParams.get("MAC_ADDRESS"));
    		}
    		return outParams;
    	}
    With the code as is above, I am getting:

    Code:
    17:01:35,327 WARN  [JdbcTemplate] Results returned from stored procedure but a corresponding SqlOutParameter/SqlReturnResultSet parameter was not declared
    I want to return a resultset to the client code. Must you return a Map? Is there any advantage in using JdbcTemplate instead of StoredProcedure class?

    client code:
    Code:
    import java.util.Iterator;
    import java.util.Map;
    import java.util.Set;
    
    import org.jboss.seam.annotations.Create;
    import org.jboss.seam.annotations.Logger;
    import org.jboss.seam.annotations.Name;
    import org.jboss.seam.log.Log;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    
    @Name("testSprocClient")
    public class TestSprocClient {
    	
    	@Logger private Log log;
    	
    	//@In(required=false) private SeamJdbcTemplate seamJdbcTemplate;
    	
    	//private DataSource ds;
    	
    	private DriverManagerDataSource dataSource;
    	
    	@Create
    	public void init(){
    		dataSource = new DriverManagerDataSource();
    		dataSource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    		dataSource.setUrl("jdbc:sqlserver://CORG0DB901:1433;databaseName=boBETS");
    		dataSource.setUsername("_AppUser_JavaTestAcct");
    		dataSource.setPassword("JavaTestAcct");
    		
    		//DataSource ds = (DataSource)dataSource;
    	}
    	
    	public void callSproc(){
    		SeamJdbcTemplate seamJdbcTemplate = new SeamJdbcTemplate(dataSource);
    		Map results = seamJdbcTemplate.execute("M40551ND2383", 333);
    		
    		log.info("results.size() = "+results.size());
    		
    		Set set = results.keySet();
    		Iterator it = set.iterator();
    		while(it.hasNext()){
    			Object o = it.next();
    			log.info("o = "+o);
    		}
    		
    		
    	}
    	
    }
    results.size() = 0
    Last edited by rabies; Jun 18th, 2009, 07:09 PM. Reason: adding spring.jar version

  • #2
    This example looks very similar to mine. I don't understand why/how it's adding the SqlOutParameter values to the inParams Map instance in my code??

    http://javaj2eekp.blogspot.com/2008/...ng-spring.html

    Code:
    public class GetSalaryProcedure extends StoredProcedure {
      
      private static final String SPROC_NAME="get_salary";
      private static final String EMPLOYEE_ID="p_emp_id";
      private static final String SALARY="o_salary";
       public GetSalaryProcedure(DataSource dataSource) {
            super();
              setDataSource(dataSource);
              setFunction(false);
              setSql(SPROC_NAME);
              
        declareParameter(new SqlParameter(EMPLOYEE_ID, Types.DECIMAL));
        declareParameter(new SqlOutParameter(SALARY, Types.NUMERIC));
             
              compile();
          }
       public Map execute(Integer empId ) {
         Map outs=null;
         try{
              Map inputs = new HashMap();
              System.out.println(empId.intValue());
              inputs.put(EMPLOYEE_ID,empId);
              outs= super.execute(inputs);
         }catch(Exception e){
           e.printStackTrace();
         }
              return outs;
        }
    }
    My JVM has crashed several times after running this functional test:
    Code:
    #
    # An unexpected error has been detected by HotSpot Virtual Machine:
    #
    #  EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x6dabc59b, pid=6012, tid=5420
    #
    # Java VM: Java HotSpot(TM) Server VM (1.5.0_17-b04 mixed mode)
    # Problematic frame:
    # V  [jvm.dll+0x1cc59b]
    #
    # An error report file with more information is saved as hs_err_pid6012.log
    #
    # If you would like to submit a bug report, please visit:
    #   http://java.sun.com/webapps/bugreport/crash.jsp
    #
    Here is the stack trace when I try to set the out params (uncomment the two lines):
    Code:
    17:16:33,266 ERROR [ExceptionFilter] exception root cause
    javax.faces.FacesException: #{testSprocClient.callSproc()}: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call usp_GetEquipmentDetail(?, ?, ?, ?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function usp_GetEquipmentDetail has too many arguments specified.
    	...
    Caused by: javax.faces.el.EvaluationException: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call usp_GetEquipmentDetail(?, ?, ?, ?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function usp_GetEquipmentDetail has too many arguments specified.
    	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:102)
    	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    	... 51 more
    Caused by: org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call usp_GetEquipmentDetail(?, ?, ?, ?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function usp_GetEquipmentDetail has too many arguments specified.
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:107)
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:899)
    	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:915)
    	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:113)
    	at com.cox.ers.utils.jdbc.SeamJdbcTemplate.execute(SeamJdbcTemplate.java:51)
    	at com.cox.ers.utils.jdbc.TestSprocClient.callSproc(TestSprocClient.java:37)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:585)
    	at org.jboss.seam.util.Reflections.invoke(Reflections.java:21)
    	at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:31)
    	at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
    	at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:31)
    	at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
    	at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:42)
    	at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
    	at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
    	at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:166)
    	at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:102)
    	at com.cox.ers.utils.jdbc.TestSprocClient_$$_javassist_6.callSproc(TestSprocClient_$$_javassist_6.java)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:585)
    	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
    	at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:274)
    	at org.jboss.el.parser.AstMethodSuffix.getValue(AstMethodSuffix.java:59)
    	at org.jboss.el.parser.AstMethodSuffix.invoke(AstMethodSuffix.java:65)
    	at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
    	at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
    	at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
    	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
    	... 52 more
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function usp_GetEquipmentDetail has too many arguments specified.
    	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
    	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(Unknown Source)
    	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:917)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:883)
    	... 83 more

    Comment


    • #3
      Microsoft SQL Server 2005 JDBC Driver 1.2.2828.100
      Sun HotSpot JDK 1.5.0_17-b04
      Seam 2.0.2-FP
      Spring 2.0.8

      I'm wondering if I should use a later sqljdbc.jar or spring.jar?

      btw, I tried this (even though this is MSSQL and no function, but I got it from another post on this forum who was having same problem with SqlOutParameter):

      super(ds, SPROC_NAME);

      setFunction(true);

      declareParameter(new SqlParameter("SERIAL_NUMBER", Types.VARCHAR));
      declareParameter(new SqlParameter("SITE_ID", Types.INTEGER));
      declareParameter(new SqlOutParameter("ITEM_NUMBER", Types.VARCHAR));
      declareParameter(new SqlOutParameter("MAC_ADDRESS", Types.VARCHAR));
      And it didn't help. JVM still crashing but not sure if root cause is related to this JDBC code or not...

      Comment


      • #4
        I tried the 2.5 spring.jar and same exception as before...

        Comment

        Working...
        X