my requirement is to search for the list of destinations that are stored in DB2, when i enter a character or list of characters, i am using jquery autocomplete and ajax call. once i enter a character or string in the destination text box, it should retrieve the list of destinations for the entered format by querying the DB2. I am using Spring JDBC template. please help me find out , what is causing the error and also i am new to Spring JDBC. Thanks !

Jquery and ajxa call:

$("#destinationName").autocomplete({ source : function(request, response) { $.ajax({ url : "${pageContext.request.contextPath}/showDestinations", type : "POST", data : { term : request.term }, dataType : "json", success : function(data) { response(data); } }); } });

controller:

@RequestMapping(value = "/showDestinations") public void getDestinations(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { LOG.info("start of--/showDestinations -- MonitoringController.getDestinations()"); response.setContentType("application/json"); try { String term = request.getParameter("term"); List<String> destinationList = null; LOG.info("scenario 1"); destinationList = monitorDAO.fetchDestinations(term); String searchList = new Gson().toJson(destinationList); response.getWriter().write(searchList); LOG.info("scenario 2"); } catch (Exception e) { e.printStackTrace(); } LOG.info("end of -- MonitoringController.getDestinations()"); }

DAO 

public List<String> fetchDestinations(String destinationName) throws Exception { LOG.info("start of -- MonitorDAOImpl.fetchDestinations()"); LOG.info("scenario 3"); return this.jdbcTemplate.query(this.fetchDestinationsQuery, new Object[]{"%" + destinationName.toUpperCase() + "%",}, new RowMapper<String>(){ public String mapRow(ResultSet resultSet, int rowNum) throws SQLException { return resultSet.getString((EmsTypes.DESTINATION.getValue())); } }); }

LOGS:

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT DISTINCT destination FROM ems_monit (destination) like ?]; SQL state [ ]; error code [-313]; DB2 SQL error: SQLCODE: -313, SQLSTATE: , SQLERRMC: null; nested exception is com.ibm.db2.j -313, SQLSTATE: , SQLERRMC: null at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) at com.macys.platform.messaging.ems.monitoring.dao.MonitorDAOImpl.fetchDestinations(MonitorDAOImpl.java:191) at com.macys.platform.messaging.ems.monitoring.controller.MonitoringController.getDestinations(MonitoringController.java:216) 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:597) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:426) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:414) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560) at javax.servlet.http.HttpServlet.service(HttpServlet.java:637) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.(BasicAuthenticationFilter.java:150) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.session.ConcurrentSessionFilter.doFilter(ConcurrentSessionFilter.java:125) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342) at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Thread.java:619) Caused by: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -313, SQLSTATE: , SQLERRMC: null at com.ibm.db2.jcc.c.fg.e(fg.java:1596) at com.ibm.db2.jcc.c.fg.a(fg.java:1206) at com.ibm.db2.jcc.b.gb.g(gb.java:140) at com.ibm.db2.jcc.b.gb.a(gb.java:39) at com.ibm.db2.jcc.b.w.a(w.java:34) at com.ibm.db2.jcc.b.vb.g(vb.java:139) at com.ibm.db2.jcc.c.fg.n(fg.java:1177) at com.ibm.db2.jcc.c.gg.eb(gg.java:1862) at com.ibm.db2.jcc.c.gg.d(gg.java:2295) at com.ibm.db2.jcc.c.gg.V(gg.java:424) at com.ibm.db2.jcc.c.gg.executeQuery(gg.java:407) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:643) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586) ... 61 more
2

1 Answer

The error message says:

THE NUMBER OF HOST VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF PARAMETER MARKERS

See IBM Knowledge Center for more Details.

Your query need 2 parameter. But your array contains only one parameter. You have to add destination two times to the array.

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.