java.sql.SQLException: Invalid column index

I have seen many beginners asking this question and searching on Google:

I am getting below exception, while running a Java Database Connectivity (JDBC) query. I have checked my query its working fine when I run directly from a SQL client. What could be the reason?

why am I getting java.sql.sqlexception: invalid column index? How to resolve java.sql.sqlexception: invalid column index. What does it mean when I get java.sql.sqlexception: invalid column index. how to solve java.sql.sqlexception: invalid column index, error using JDBC query java.sql.sqlexception: invalid column index

java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OraclePreparedStatement.setLongInternal(OraclePreparedStatement.java:4680)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9023)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8790)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9512)
at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9495)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:346)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:217)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:145)
at org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:51)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:646)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:710)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:187)
at com.dornerconsulting.rma.spring.hibernate.dao.SystemItemJDBCDao.findBySql(SystemItemJDBCDao.java:49)
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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy23.findBySql(Unknown Source)
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.springframework.binding.method.MethodInvoker.invoke(MethodInvoker.java:93)
at org.springframework.webflow.action.AbstractBeanInvokingAction.doExecute(AbstractBeanInvokingAction.java:127)
at org.springframework.webflow.action.AbstractAction.execute(AbstractAction.java:192)
at org.springframework.webflow.engine.AnnotatedAction.execute(AnnotatedAction.java:146)
at org.springframework.webflow.engine.ActionExecutor.execute(ActionExecutor.java:59)
at org.springframework.webflow.engine.ActionList.execute(ActionList.java:153)
at org.springframework.webflow.engine.ViewState.doEnter(ViewState.java:94)
at org.springframework.webflow.engine.State.enter(State.java:191)
at org.springframework.webflow.engine.Transition.execute(Transition.java:212)
at org.springframework.webflow.engine.TransitionableState.onEvent(TransitionableState.java:107)
at org.springframework.webflow.engine.Flow.onEvent(Flow.java:534)
at org.springframework.webflow.engine.impl.RequestControlContextImpl.signalEvent(RequestControlContextImpl.java:205)
at org.springframework.webflow.engine.impl.FlowExecutionImpl.signalEvent(FlowExecutionImpl.java:202)
at org.springframework.webflow.executor.FlowExecutorImpl.resume(FlowExecutorImpl.java:222)
at org.springframework.webflow.executor.support.FlowRequestHandler.handleFlowRequest(FlowRequestHandler.java:111)
at org.springframework.webflow.executor.mvc.FlowController.handleRequestInternal(FlowController.java:165)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
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.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
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:128)
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:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)


Answer : Most of the time, If you are getting this exception while running a JDBC query from a Java program check if you are passing correct number of parameters to the prepared statement query.
So for example if you have a select statement like below

select * from emp where empId = ? and deptId = ? 


In this case there are 2 parameters need to be passed to the prepared statement. If you pass less or more you are going to get the above exception and query will not execute.

Post a Comment Default Comments

  1. An exception that provides information on a database access error or other errors.

    Each SQLException provides several kinds of information:

    * a string describing the error. This is used as the Java Exception message, available via the method getMesage.
    * a "SQLstate" string, which follows either the XOPEN SQLstate conventions or the SQL 99 conventions. The values of the SQLState string are described in the appropriate spec. The DatabaseMetaData method getSQLStateType can be used to discover whether the driver returns the XOPEN type or the SQL 99 type.
    * an integer error code that is specific to each vendor. Normally this will be the actual error code returned by the underlying database.
    * a chain to a next Exception. This can be used to provide additional error information.

    ReplyDelete
  2. I just wanted to say thanks. While my specific problem was that i had the rowMapper incorrectly mapping one of the items coming back. Your post helped me get there a lot quicker ;)

    ReplyDelete
  3. This site is very informative. Your site will be bookmarked for future reference.

    ReplyDelete
  4. Information i got here made me to solve the issue in seconds ... Great job , Thanks a lot :)

    ReplyDelete

Individuals who comment on FromDev at regular basis, will be rewarded in Top Commenter section. (Comments are selectively moderated so please do not spam)

emo-but-icon

...

item