Wednesday, January 25, 2012

How to fix java.sql.SQLException: Invalid column index

"java.sql.SQLException: Invalid column index" is a frequent error while working in Java Database Connectivity (JDBC). As the name suggests "Invalid column index" its related to accessing or setting column in your SQL Query using prepared statement in Java. I have seen "java.sql.SQLException: Invalid column index" coming mostly due to two reason:

1) Setting column data using setXXXX(int coloumIndex) e.g. setInt(0) setString(0)
2) Getting column data using getXXX(int columnIndex) e.g. getInt(0) getString(0)

Most common cause of "java.sql.SQLException: Invalid column index" is misconception that column index started with "0" like array or String index but that's not true instead column index starts with "1" so whenever you try to get or Set column data with column index "0" you will get "java.sql.SQLException: Invalid column index".

java.sql.SQLException: Invalid column index

solve java.sql.SQLException: Invalid column indexThat's was the one case other case was you are simply putting wrong column index, like if you query is returning 3 columns in ResultSet and you are trying to access 4th column, JDBC will throw "java.sql.SQLException: Invalid column index" or if your PreparedStatement has 3 place holders but you are trying to set data on 4th column index you will be greeted by "java.sql.SQLException: Invalid column index". In next section we will see a real life example of "java.sql.SQLException: Invalid column index" while writing JDBC code.


Example of "java.sql.SQLException: Invalid column index"

here is simple code example of getting some data from PreparedStatement SELECT SQL query. here we have put just one place holder for passing order_id. if you pass place holder anything other than "1" like "0" or "2" you will get "java.sql.SQLException: Invalid column index" , just try it on your end and you will get hold of it. same is true while you are reading data from ResultSet.

Actually ResultSet offer two ways to access column data either by column name or column index. if you access column data using incorrect column name, JDBC will throw "java.sql.SQLException: Invalid column name" while if index is incorrect JDBC will throw "java.sql.SQLException: Invalid column index" . I prefer accessing column data using name because its more readable in code.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class InvalidColumnIndexExample {

    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1526:TESTID", "root", "root");
        PreparedStatement preStatement = conn.prepareStatement("select distinct item from Order where order_id=?");
    
        preStatement.setString(0, "123456"); //this will throw "java.sql.SQLException: Invalid column index" because "0" is not valid colum index

      
        ResultSet result = preStatement.executeQuery();
      
        while(result.next()){
            System.out.println("Item: " + result.getString(2)); //this will also throw "java.sql.SQLException: Invalid column index" because resultset has only one column

        }
    }
}

Output:
Exception in thread "main" java.sql.SQLException: Invalid column index
        at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:7700)
        at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:7654)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.setString(OraclePreparedStatementWrapper.java:910)


That’s all on how to fix “Exception in thread "main" java.sql.SQLException: Invalid column index”  Just beware that column index on ResultSet and PreparedStatement parametric query starts with 1 and not 0 and accessing column with index 0 will result in invalid column index error.

Some Java tutorial  you may like:

9 comments :

Anonymous said...

The colum index begin 1.
For example :ResultSet rs=ms.execute("SELECT id, description FROM news");

id=Integer.parseInt(rs.getString(1));
description=rs.getString(2);

Anonymous said...

I am facing problem of invalid column index plz sombody help me

PreparedStatement ps=con.prepareStatement("insert into ABC values(ID.nextval,?,?,?,?)");

ps.setInt(1,ID);
System.out.println(ID);
ps.setString(2,textField.getText());
ps.setString(3,textField_1.getText());
ps.setString(4,textField_2.getText());

if(rdbtnM.isSelected())
{
ps.setString(5, rdbtnM.getText());
}
if(rdbtnF.isSelected())
{
ps.setString(5, rdbtnF.getText());
}

ps.executeUpdate();

plz fix the error of invalid column index

Javin @ JDBC Connection Pool Setup said...

Hi Anonymous, Can you please post full exception stacktrace? From your code it looks like you are setting fifth position ps.setString(5) but I can only see 4 placeholders (?) in your prepared statement. This could be the cause of your SQLException, invalid column index.

Anonymous said...

final JButton btnRegister = new JButton("Register");
btnRegister.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent me) {
if(me.getSource()==btnRegister)
{
//ConnectionClass.id=ConnectionClass.id;
try
{

Connection con=ConnectionClass.getCon();
PreparedStatement ps=con.prepareStatement("insert into ABC values(ID.nextval,?,?,?,?)");

ps.setInt(1,ID);
System.out.println(ID);
ps.setString(2,textField.getText());
ps.setString(3,textField_1.getText());
ps.setString(4,textField_2.getText());

if(rdbtnM.isSelected())
{
ps.setString(5, rdbtnM.getText());
}
if(rdbtnF.isSelected())
{
ps.setString(5, rdbtnF.getText());
}

ps.executeUpdate();

}

catch(SQLException sqle)
{
sqle.printStackTrace();
}


}

}
});


btnRegister.setBounds(202, 308, 89, 23);
contentPane.add(btnRegister);


my connection class is connected with oracle 10g database in which a table is created ABC having columns as (ID number(10) primary key,Fname varchar2(20),Lname varchar2(20),Username varchar2(20),Gender char(1))

above is the order in the table

I have to dynamically auto-increament the value of ID and it is giving me the error of INVALID COLUMN INDEX

PLZ SOLVE THE PROBLEM !!!!!

Javin @ Must Override method in Eclipse Error said...

Try removing ps.setInt(1) call and reduce subsequent numbers till 4 e.g. ps.setString(2,textField.getText()); should be ps.setString(1,textField.getText());, change all of them so that your last index should be 4. I hope this will solve your invalid index problem.

Anonymous said...

thanks I have solved my problem :)

one problem is also there i.e. I have to apply the constraints on respective text feilds as per their requirement..

so plz solve this problem I am not able to apply the constraint it gives lots of error when I apply it.

Anonymous said...

best way to solve this type of problem is put a sop before the starting of sql and then compile the program.you will get error in system out log and now check the failed query.try to resolve the query and put it back into program.now compile it.its done

Anonymous said...

Given below is my Java source code for Issuing a book. I want to enter the date of Issue of book in database and I want to pick up that date from the desktop/laptop shows in corner of bottom of screen.

public class Issue extends JFrame {

protected static final String String = null;
private JPanel contentPane;
private JTextField textField;
private JTextField textField_1;

/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
Issue frame = new Issue();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}

/**
* Create the frame.
*/
public Issue() {

setDefaultCloseOperation(JFrame.HIDE_ON_CLOSE);
setBounds(100, 100, 509, 467);
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
setContentPane(contentPane);
contentPane.setLayout(null);

JLabel lblNewLabel = new JLabel("Book Id");
lblNewLabel.setHorizontalAlignment(SwingConstants.CENTER);
lblNewLabel.setBounds(177, 128, 92, 14);
contentPane.add(lblNewLabel);

/* JLabel lblNewLabel_1 = new JLabel("Date of Issue");
lblNewLabel_1.setHorizontalAlignment(SwingConstants.CENTER);
lblNewLabel_1.setBounds(192, 196, 77, 17);
contentPane.add(lblNewLabel_1);*/

textField = new JTextField();
textField.setBounds(309, 125, 86, 20);
contentPane.add(textField);
textField.setColumns(10);

/*textField_1 = new JTextField();
textField_1.setBounds(309, 194, 86, 20);
contentPane.add(textField_1);
textField_1.setColumns(10);*/

final JButton btnIssue = new JButton("Issue");
btnIssue.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent me)
{

if(me.getSource()==btnIssue)
{
try
{
**********************Calendar currentDate = Calendar.getInstance();
SimpleDateFormat formatter=
new SimpleDateFormat("yyyy/MMM/dd");
String dateNow = formatter.format(currentDate.getTime());
System.out.println("Now the date is :=> " + dateNow);*******************

PreparedStatement ps;
Connection con;
con = ConnectionClass.getCon();
ps=con.prepareStatement("insert into Book_Issue values(?,?)");

ps.setInt(1,Integer.parseInt(textField.getText()));
++++++++ps.setDate(2, "dateNow");++++++++++++
// ps.executeUpdate();

ps.executeUpdate();
JOptionPane.showMessageDialog(null, "THIS BOOK IS ISSUED TO YOU ");
// ps.executeUpdate();


}
catch(Exception sqle)
{
sqle.printStackTrace();
}

}


}

});
btnIssue.setBounds(180, 297, 89, 23);
contentPane.add(btnIssue);

JButton btnNewButton_1 = new JButton("Reset");
btnNewButton_1.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent me1) {
textField.setText("");
textField_1.setText("");
}
});
btnNewButton_1.setBounds(316, 297, 89, 23);
contentPane.add(btnNewButton_1);

JButton btnNewButton_2 = new JButton("Back");
btnNewButton_2.addMouseListener(new MouseAdapter() {
@Override
public void mouseClicked(MouseEvent arg) {
new Confirm().show();
}
});
btnNewButton_2.setBounds(253, 380, 89, 23);
contentPane.add(btnNewButton_2);
}
}



here above the code present in between ***** is of obtaning code from the laptop/desktop and the code betweeen ++++ is the place where I have to use it to display it in database, without writing the date in textfiled.

I hope you would be able to uderstand my query.

plz help me

Anonymous said...

We can see similar issue when it mismatches in number of '?' in the given query string.

Post a Comment