Announcement Announcement Module
Collapse
No announcement yet.
SimpleJdbcCall error with MySQL function Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJdbcCall error with MySQL function

    I'm having problems using the class SimpleJdbcCall. I adapted my Java code to the example shown in section 12.5.8 reference manual, but without success. So, I would like to help to solve it.

    Below is my code, the function created in MySQL and the returned exception. Thank you.

    * Function
    Code:
    CREATE DEFINER=`root`@`localhost` FUNCTION `COMPARA_QTDE_EMPRESA`(AG INT, P INT, ANO INT, AINSERIR INT) RETURNS tinyint(1) DETERMINISTIC
    BEGIN
        DECLARE TOTAL_EMPRESA_PESQUISA INT;
        DECLARE TOTAL_EMPRESA_AGENTE INT;
        
        SELECT IF(COUNT(*), EP.TOTAL, 0) INTO TOTAL_EMPRESA_PESQUISA
        FROM CTRLPRODUCAO.EMPRESA_PESQUISA EP
        WHERE EP.AGENCIA = AG
        AND EP.PESQUISA = P
        AND EP.ANO = ANO;
    
        SELECT IFNULL(SUM(EA.TOTAL), 0) INTO TOTAL_EMPRESA_AGENTE
        FROM CTRLPRODUCAO.EMPRESA_AGENTE EA
        WHERE EA.AGENCIA = AG
        AND EA.PESQUISA = P
        AND EA.ANO = ANO;
        
        RETURN IF(TOTAL_EMPRESA_PESQUISA = 0, FALSE,(TOTAL_EMPRESA_PESQUISA < (TOTAL_EMPRESA_AGENTE) + AINSERIR));
    END
    * Java code
    Code:
    (...)
    SimpleJdbcCall jdbcCall = new SimpleJdbcCall(getJdbcTemplate());
    jdbcCall.withFunctionName("COMPARA_QTDE_EMPRESA");
    MapSqlParameterSource in = new MapSqlParameterSource()
    							.addValue("AG", ag.getId())
    							.addValue("P", p.getId())
    							.addValue("ANO", p.getAno())
    							.addValue("AINSERIR", p.getTotal());
    boolean result = jdbcCall.executeFunction(Boolean.class, in);     <<<< ERROR !!!
    * Exception
    Code:
    28/10/2011 09:18:45 org.springframework.jdbc.core.metadata.CallMetaDataContext matchInParameterValuesWithCallParameters
    AVISO: Unable to locate the corresponding parameter for "AG" in the parameters used: []
    28/10/2011 09:18:45 org.springframework.jdbc.core.metadata.CallMetaDataContext matchInParameterValuesWithCallParameters
    AVISO: Unable to locate the corresponding parameter for "P" in the parameters used: []
    28/10/2011 09:18:45 org.springframework.jdbc.core.metadata.CallMetaDataContext matchInParameterValuesWithCallParameters
    AVISO: Unable to locate the corresponding parameter for "AINSERIR" in the parameters used: []
    28/10/2011 09:18:45 org.springframework.jdbc.core.metadata.CallMetaDataContext matchInParameterValuesWithCallParameters
    AVISO: Unable to locate the corresponding parameter for "ANO" in the parameters used: []
    28/10/2011 09:20:28 org.apache.catalina.core.StandardWrapperValve invoke
    GRAVE: Servlet.service() for servlet [Faces Servlet] in context with path [/controleProducao] threw exception [/inserirEmpresasPorAgentePesquisa.xhtml at line 84 and column 89 action="#{ctrlMB.inserirTotalEmpresasPorAgentePesquisa}": java.lang.NullPointerException] with root cause
    java.lang.NullPointerException
    	at com.mysql.jdbc.CallableStatement$CallableStatementParamInfo.iterator(CallableStatement.java:365)
    	at com.mysql.jdbc.CallableStatement.setInOutParamsOnServer(CallableStatement.java:2108)
    	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:905)
    	at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:962)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:911)
    	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:960)
    	at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:369)
    	at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:343)
    	at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:166)
    	at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:141)
    	at br.gov.ibge.controleProducao.dao.EmpresaPorAgenteDAOImpl.comparaQtdeEmpresa(EmpresaPorAgenteDAOImpl.java:121)
    	at br.gov.ibge.controleProducao.dao.EmpresaPorAgenteDAOImpl.inserir(EmpresaPorAgenteDAOImpl.java:211)
    	at br.gov.ibge.controleProducao.facade.EmpresaPorAgenteFacadeImpl.inserir(EmpresaPorAgenteFacadeImpl.java:27)
    	at br.gov.ibge.controleProducao.managedBean.ControleProducaoMB.inserirTotalEmpresasPorAgentePesquisa(ControleProducaoMB.java:657)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    	at java.lang.reflect.Method.invoke(Unknown Source)
    	at org.apache.el.parser.AstValue.invoke(AstValue.java:262)
    	at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:278)
    	at org.apache.myfaces.view.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:83)
    	at javax.faces.component._MethodExpressionToMethodBinding.invoke(_MethodExpressionToMethodBinding.java:88)
    	at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:100)
    	at javax.faces.component.UICommand.broadcast(UICommand.java:120)
    	at javax.faces.component.UIViewRoot._broadcastAll(UIViewRoot.java:972)
    	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:275)
    	at javax.faces.component.UIViewRoot._process(UIViewRoot.java:1284)
    	at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:710)
    	at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:34)
    	at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:171)
    	at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
    	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:189)
    	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
    	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
    	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:164)
    	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
    	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
    	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:563)
    	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:399)
    	at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:317)
    	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:204)
    	at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:182)
    	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:311)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    	at java.lang.Thread.run(Unknown Source)
    Last edited by rogerioag; Oct 31st, 2011, 05:22 AM.

  • #2
    Can someone help me?

    Comment

    Working...
    X