What does the following Oracle error mean invalid column index

Question

I got the following error while testing some code       SQLException  Invalid column index   What exactly does that mean    Is there an online document explaining what all the Oracle error codes and statements

User · Answer

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.

User · Answer

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

User · Answer

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 lt Map lt String  Object gt  gt  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

User · Answer

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

User · Answer

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    lt 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     lt 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

User · Answer

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

User · Answer

If that s a SQLException thrown by Java  it s most likely because you are trying to get or set a value from a ResultSet  but the index you are using isn t within the range    For example  you might be trying to get the column at index 3 from the result set  but you only have two columns being returned from the SQL query

User · Answer

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

[java] What does the following Oracle error mean: invalid column index

Examples related to java

Examples related to sql

Examples related to oracle