Announcement Announcement Module
Collapse
No announcement yet.
Spring JDBCTemplate gives InvalidResultSetAccessException Invalid column index Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring JDBCTemplate gives InvalidResultSetAccessException Invalid column index

    I use Spring's SimpleJDBCTemplate to access Oracle DB. Here is my code.

    Code:
    String sql = "SELECT from_bin_code FROM hbin_import_mapping";
    return jt.query(sql, new BeanHbinImportMappingMapper(), (Object) null);
    The row mapper is:

    Code:
    public class BeanHbinImportMappingMapper
        implements
            RowMapper<BeanHbinImportMapping> {
        public BeanHbinImportMapping mapRow(ResultSet rs, int rowno)
            throws SQLException {
            int fromBinCode = rs.getInt("from_bin_code");
    //        char fromBinCodeChar = rs.getString("from_bin_code_char").charAt(0);
    //        boolean fromBinCodeAllowed = rs.getString("from_bin_code_allowed")
    //            .equals("Y") ? true : false;
    //        int oliBinCode = rs.getInt("oli_bin_code");
    //        String oliBinQuality = rs.getString("oli_bin_quality");
    //        String oliBinGroup = rs.getString("oli_bin_group");
    //        String oliBinDesc = rs.getString("oli_bin_desc");
    //        boolean olibinRef = rs.getString("oli_bin_ref").equals("Y") ? true
    //            : false;
    
            BeanHbinImportMapping bean = new BeanHbinImportMapping(fromBinCode);
    //      BeanHbinImportMapping bean = new BeanHbinImportMapping(1, 'c', true);
            return bean;
        }
    }
    The sql is tested working.

    Exception stack trace:
    Code:
    javax.servlet.ServletException: org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [SELECT from_bin_code FROM hbin_import_mapping]; nested exception is java.sql.SQLException: Invalid column index
        org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:545)
        org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:486)
        org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
        org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
    
    
    root cause 
    
    org.springframework.jdbc.InvalidResultSetAccessException: PreparedStatementCallback; invalid ResultSet access for SQL [SELECT from_bin_code FROM hbin_import_mapping]; nested exception is java.sql.SQLException: Invalid column index
        org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
        org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
        org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:200)
        com.st.mas.wmr.persistence.atom.AtomStifOliBinConversion.getLoaderMatrix(AtomStifOliBinConversion.java:52)
        com.st.mas.wmr.persistence.process.ProcessStifOliBinConversion.getLoaderMatrix(ProcessStifOliBinConversion.java:50)
        com.st.mas.wmr.action.StifBinConversionAction.viewLoaderMapping(StifBinConversionAction.java:35)
        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:585)
        org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
        com.st.mas.wmr.action.AnyDispatchAction.execute(AnyDispatchAction.java:107)
        org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
        org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
        org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
    
    
    root cause 
    
    java.sql.SQLException: Invalid column index
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:124)
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:161)
        oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:226)
        oracle.jdbc.driver.OraclePreparedStatement.setNullInternal(OraclePreparedStatement.java:3661)
        oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:3652)
        org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108)
        org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:108)
        org.springframework.jdbc.core.StatementCreatorUtils.setNull(StatementCreatorUtils.java:252)
        org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:213)
        org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:144)
        org.springframework.jdbc.core.ArgPreparedStatementSetter.doSetValue(ArgPreparedStatementSetter.java:65)
        org.springframework.jdbc.core.ArgPreparedStatementSetter.setValues(ArgPreparedStatementSetter.java:46)
        org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:641)
        org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673)
        org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
        org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:200)
        com.st.mas.wmr.persistence.atom.AtomStifOliBinConversion.getLoaderMatrix(AtomStifOliBinConversion.java:52)
        com.st.mas.wmr.persistence.process.ProcessStifOliBinConversion.getLoaderMatrix(ProcessStifOliBinConversion.java:50)
        com.st.mas.wmr.action.StifBinConversionAction.viewLoaderMapping(StifBinConversionAction.java:35)
        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:585)
        org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)
        com.st.mas.wmr.action.AnyDispatchAction.execute(AnyDispatchAction.java:107)
        org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
        org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
        org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
        org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
        javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
    Thanks.

  • #2
    You are passing parameters (null in this case) and the query doesn't have any query parameters... Either add a parameter to the query or remove the parameter from the method call.

    Comment


    • #3
      Hi Martin
      Thanks.

      My query doesn't need any query parameters.

      Oh gosh, I didn't know that query() can accept a call without query parameters (only 2 method parameters passed in). Why is it not stated in the javadoc?
      http://static.springsource.org/sprin...cTemplate.html

      Comment


      • #4
        It is a varargs element and that is default/basic behavior of a varargs element... IMHO that should be common knowledge for a java developer...

        Comment

        Working...
        X