SimpleJdbcCall error with MySQL function

[rogerioag]

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

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

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

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)

[rogerioag]

Can someone help me?