Solve java.sql.SQLException: Invalid column name/ index in JDBC


In this post we will find out reasons and solution for >
  • java.sql.SQLException: Invalid column index
  • java.sql.SQLException: Invalid column name



First let's discuss  java.sql.SQLException: Invalid column index and solve it in java.


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;

--If table already exists then execute the DROP command >
drop table EMPLOYEE;


EMPLOYEE table will look like this >


JDBC- Program1 - Which can throw java.sql.SQLException: Invalid column index in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class InvalidColumnIndexExample {
public static void main(String... arg) throws ClassNotFoundException, SQLException {
     Connection con = null;
     PreparedStatement prepStmt = null;
     ResultSet rs = null;             
    
     // registering Oracle driver class
     Class.forName("oracle.jdbc.driver.OracleDriver");
     // getting connection
     con = DriverManager.getConnection(
              "jdbc:oracle:thin:@localhost:1521:orcl",
              "ankit", "Oracle123");
     System.out.println("Connection established successfully!");
    
     prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE where ID=?");
     prepStmt.setInt(2, 7); //It will throw java.sql.SQLException: Invalid column index
     rs = prepStmt.executeQuery();
     while (rs.next()) {
          System.out.print(rs.getInt("ID")+" ");
          System.out.println(rs.getString("NAME"));
     }
}
}
/*OUTPUT
Connection established successfully!
Exception in thread "main" java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OraclePreparedStatement.setIntInternal(OraclePreparedStatement.java:4605)
at oracle.jdbc.driver.OraclePreparedStatement.setInt(OraclePreparedStatement.java:4594)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setInt(OraclePreparedStatementWrapper.java:198)
at InvalidColumnIndexExample.main(InvalidColumnIndexExample.java:24)
*/

Solution to java.sql.SQLException: Invalid column index in above java program?
Using prepStmt.setInt(1, 7); will SOLVE java.sql.SQLException: Invalid column index

And output of program will be -

Connection established successfully!
7 ankit




But why java.sql.SQLException: Invalid column index was thrown in above java program?
Because  prepStmt.setInt(2, 7) was searching for second occurrence of ? in
"select ID, NAME from EMPLOYEE where ID=?" while there was only one ? in whole query (searching was done at invalid column index).

But,
prepStmt.setInt(1, 7)
searched for first occurrence of ? in
"select ID, NAME from EMPLOYEE where ID=?" and replace first occurrence of ? in whole query.



Now, let's discuss  java.sql.SQLException: Invalid column name and solve it in java.


JDBC- Program2 - Which can throw java.sql.SQLException: Invalid column name in java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class InvalidColumnNameExample {
public static void main(String... arg) throws ClassNotFoundException, SQLException {
     Connection con = null;
     PreparedStatement prepStmt = null;
     ResultSet rs = null;             
    
     // registering Oracle driver class
     Class.forName("oracle.jdbc.driver.OracleDriver");
     // getting connection
     con = DriverManager.getConnection(
              "jdbc:oracle:thin:@localhost:1521:orcl",
              "ankit", "Oracle123");
     System.out.println("Connection established successfully!");
    
     prepStmt = con.prepareStatement("select ID, NAME from EMPLOYEE where ID=?");
     prepStmt.setInt(1, 7);
     rs = prepStmt.executeQuery();
     while (rs.next()) {
          System.out.print(rs.getInt("EMP_ID")+" "); //It will throw java.sql.SQLException: Invalid column index
          System.out.println(rs.getString("NAME"));
     }
}
}
/*OUTPUT
Connection established successfully!
Exception in thread "main" java.sql.SQLException: Invalid column name
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.java:3367)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:2009)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:434)
at InvalidColumnIndexExample.main(InvalidColumnIndexExample.java:27)
*/


Solution to java.sql.SQLException: Invalid column name in above java program?
Using rs.getInt("ID"); will SOLVE java.sql.SQLException: Invalid column name

And output of program will be -

Connection established successfully!
7 ankit




But why java.sql.SQLException: Invalid column name was thrown in above java program?
Because  rs.getInt("EMP_ID") was searching for column name EMP_ID in "select ID, NAME from EMPLOYEE where ID=?" while there was no such column name (neither in database nor as any alias name in query).  (searching was done with invalid column name)

But,
rs.getInt("ID")
searched for column ID in
"select ID, NAME from EMPLOYEE where ID=?" and found it.



RELATED LINKS>

12 Differences between Statement, PreparedStatement and CallableStatement in java


java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, INSERT, UPDATE and DELETE

JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java


JDBC- Calling Oracle database FUNCTION - CallableStatement example in java


JDBC- Insert/Store/save IMAGE in database using BLOB data type - in java


JDBC- Retrieve IMAGE from database using BLOB data type


No comments:

Post a Comment