I had this problem using a prepared statement. I didn't add enough "?" for the "VALUES" My eclipse had crashed after I did add the proper amount, and lost those changes. But that didn't occur to me to be the error until I started combing through the SQL as p.campbell suggested.
the final sql statement is something like:
select col_1 from table_X where col_2 = 'abcd';
i run this inside my SQL IDE and everything is ok.
Next, i try to build this statement with java:
String queryString= "select col_1 from table_X where col_2 = '?';";
PreparedStatement stmt = con.prepareStatement(queryString);
stmt.setString(1, "abcd"); //raises java.sql.SQLException: Invalid column index
Although the sql statement (the first one, ran against the database) contains quotes around string values, and also finishes with a semicolumn, the string that i pass to the PreparedStatement should not contain quotes around the wildcard character ?, nor should it finish with semicolumn.
i just removed the characters that appear on white background
"select col_1 from table_X where col_2 =
' ?
' ; ";
to obtain
"select col_1 from table_X where col_2 = ?";
(i found the solution here: https://coderanch.com/t/424689/databases/java-sql-SQLException-Invalid-column)
I also got this type error, problem is wrong usage of parameters to statement like, Let's say you have a query like this
SELECT * FROM EMPLOYE E WHERE E.ID = ?
and for the preparedStatement object (JDBC) if you set the parameters like
preparedStatement.setXXX(1,value);
preparedStatement.setXXX(2,value)
then it results in SQLException: Invalid column index
So, I removed that second parameter setting to prepared statement then problem solved
Using Spring's SimpleJdbcTemplate, I got it when I tried to do this:
String sqlString = "select pwy_code from approver where university_id = '123'";
List<Map<String, Object>> rows = getSimpleJdbcTemplate().queryForList(sqlString, uniId);
I had an argument to queryForList that didn't correspond to a question mark in the SQL. The first line should have been:
String sqlString = "select pwy_code from approver where university_id = ?";
I had the exact same problem when using Spring Security 3.1.0. and Oracle 11G. I was using the following query and getting the invalid column index error:
<security:jdbc-user-service data-source-ref="dataSource"
users-by-username-query="SELECT A.user_name AS username, A.password AS password FROM MB_REG_USER A where A.user_name=lower(?)"
It turns out that I needed to add: "1 as enabled" to the query:
<security:jdbc-user-service data-source-ref="dataSource" users-by-username query="SELECT A.user_name AS username, A.password AS password, 1 as enabled FROM MB_REG_USER A where A.user_name=lower(?)"
Everything worked after that. I believe this could be a bug in the Spring JDBC core package...
It sounds like you're trying to SELECT
a column that doesn't exist.
Perhaps you're trying to ORDER BY
a column that doesn't exist?
Any typos in your SQL statement?
I had this problem in one legacy application that create prepared statement dynamically.
String firstName;
StringBuilder query =new StringBuilder("select id, name from employee where country_Code=1");
query.append("and name like '");
query.append(firstName + "' ");
query.append("and ssn=?");
PreparedStatement preparedStatement =new prepareStatement(query.toString());
when it try to set value for ssn, it was giving invalid column index error, and finally found out that it is caused by firstName having ' within; that disturb the syntax.
Source: Stackoverflow.com