Announcement Announcement Module
Collapse
No announcement yet.
calling ms sql stored procedure with parameters Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • calling ms sql stored procedure with parameters

    Hi, I'm having a hard time figuring this out... any help would really be appreciated..

    I have a stored procedure that returns a record by passing the ID as input. All I want to do is to call it and get the record back.. but i got the following exception when i try to execute this code:

    Code:
      private static final String SQL_SELECT_TESTJOB = "spgettestjobbyid";
      public SelectTestJob (DataSource ds){
          setDataSource (ds);
          setFunction(false);
          setSql(SQL_SELECT_TESTJOB);
          declareParameter(new SqlReturnResultSet("rs", new TestJobRowMapper()));
          declareParameter(new SqlParameter("in_jobId", Types.INTEGER));
          compile();
        }
        public TestJob retreive (int jobId){
          Map params = new HashMap();
          params.put("in_jobId", new Integer(jobId));
          Map results = execute(params);
          System.out.println("test2"+results.toString());
          return (TestJob) ((LinkedList)results.get("rs")).getFirst();
    
        }
      }
    Code:
    org.springframework.dao.InvalidDataAccessApiUsageException: SQL 'spgettestobjectsbyjobid' requires 0 bind variables, but 1 variables were declared for this object
    	org.springframework.jdbc.object.SqlOperation.compileInternal(SqlOperation.java:54)
    	org.springframework.jdbc.object.RdbmsOperation.compile(RdbmsOperation.java:303)
    	org.springframework.jdbc.object.RdbmsOperation.validateParameters(RdbmsOperation.java:341)
    	org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:112)
    	org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:148)
    	org.springframework.jdbc.object.SqlQuery.execute(SqlQuery.java:156)
    	com.symantec.response.arts.dao.jdbc.JdbcTestDao.getTestObjListByTestJobId(JdbcTestDao.java:243)
    	com.symantec.response.arts.domain.logic.ArtsFacade.getTestObjsListByTestJob(ArtsFacade.java:46)
    	com.symantec.response.arts.web.TestJobController.handleView(TestJobController.java:42)
    	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	java.lang.reflect.Method.invoke(Method.java:324)
    	org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:351)
    	org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:305)
    	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
    	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
    	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
    	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
    	org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
    	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:346)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

    I tried changing the sql from "spgettestjobbyid" to "spgettestjobbyid ?" to fix the bind variable exception... but I got the following exception instead:

    Code:
    org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [{call spgettestjobbyid ?(?)}] in task 'executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementCreatorImpl: sql=[{call spgettestjobbyid ?(?)}]: params=[{in_jobId=2001}]]'; nested exception is java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Invalid parameter binding(s).
    	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:90)
    	org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:257)
    	org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:795)
    	org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:812)
    	org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:100)
    	com.symantec.response.arts.dao.jdbc.JdbcTestDao$SelectTestJob.retreive(JdbcTestDao.java:90)
    	com.symantec.response.arts.dao.jdbc.JdbcTestDao.getTestJob(JdbcTestDao.java:209)
    	com.symantec.response.arts.domain.logic.ArtsFacade.getTestJob(ArtsFacade.java:42)
    	com.symantec.response.arts.web.TestJobController.handleView(TestJobController.java:41)
    	sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	java.lang.reflect.Method.invoke(Method.java:324)
    	org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:351)
    	org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:305)
    	org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
    	org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
    	org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
    	org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
    	org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:386)
    	org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:346)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
    	javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

    What am I doing wrong? Please help! :shock:

  • #2
    Could you post more of your code. The two stacktraces don't match up - one is for a SqlQuery called from a "getTestObjListByTestJobId" method and the other one is for a StoredProcedure called from "getTestJob". In general for a query you must supply the parameter placeholders but for a stored procedure Spring will build the call string so you don't supply the parameter placeholders.

    Comment


    • #3
      just realize that the exception was thrown from another function, not from "spgettestjobbyid" stored procedure.. i was looking at it for way too long that they all looked to same to me.. :oops:
      problem is fixed now.. thanks

      Comment

      Working...
      X