"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)
The most common cause of "java.sql.SQLException: Invalid column index" is a 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
That's was the one case another case was you are simply putting wrong column index, like if your 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 the next section, we will see a real-life example of "java.sql.SQLException: Invalid column index" while writing JDBC code.
An example of "java.sql.SQLException: Invalid column index"
here is a sample code example of getting some data from the PreparedStatement SELECT SQL query. here we have put just one placeholder 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. The same is true while you are reading data from ResultSet.
Actually, ResultSet offers two ways to access column data either by column name or column index.
If you access column data using an incorrect column name, JDBC will throw "java.sql.SQLException: Invalid column name" while if the index is incorrect JDBC will throw "java.sql.SQLException: Invalid column index". I prefer accessing column data using the name because it's 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 tutorials you may like:
21 comments :
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);
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
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.
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 !!!!!
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.
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.
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
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
We can see similar issue when it mismatches in number of '?' in the given query string.
package org.java.com;
import java.sql.*;
public class PrepareState {
public static void main(String[] args)throws Exception {
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","jayabhargav38");
//PreparedStatement stmt=con.prepareStatement("insert into emp values(?,?)");
PreparedStatement stmt=con.prepareStatement("update emp set name=?,id=?");
stmt.setInt(5,161);
stmt.setString(6,"karan");
int i=stmt.executeUpdate();
System.out.println(i+ "records inserted");
con.close();
}catch(Exception e){
System.out.println(e);
}
}
}
output:invalid column index
Hi
I have very simple query
Select ipaddress, logontime, logofftime from usersssion.
It has some other fields as well but we are looking only for ipadddress, logogg and logon time
But when we run this query through arcsight smart connector it is giving invalid index coloum error.
Can you please advise
pls guys I need help on how to retrieve data from java embedded database
@Anonymous, you need to write the Java program using JDBC to connect to Java embedded database to get the data. You also make sure to use the relevant JDBC drivers.
///services
String sname=request.getParameter("sname");
String classic=request.getParameter("classic");
String deluxe=request.getParameter("deluxe");
String tax=request.getParameter("tax");
//category
String cat=request.getParameter("category");
Connection connect = null;
PreparedStatement ps=null;
PreparedStatement ps1=null;
try{
Class.forName("com.mysql.jdbc.Driver");
// Setup the connection with the DB
connect = DriverManager
.getConnection("jdbc:mysql://localhost:3306/tariff","root","123456");
ps=connect.prepareStatement(
"insert into services(service_name,classic,deluxe,tax) values(?,?,?,?)");
ps.setString(1,sname);
ps.setString(2,classic);
ps.setString(3,deluxe);
ps.setString(4,tax);
ps.executeUpdate();
ps.close();
ps1=connect.prepareStatement("insert into category(category) values(?)");
ps1.setString(1,cat);
ps1.executeUpdate();
ps1.close();
}
catch(Exception e){
System.out.println(e);
}
showing error: incorrect integer value; ' ' for column at row 1
@Shiva, because you are setting String "ps.setString(1,sname);" and I guess your database column is of integer type. Whatever you enter must match schema of table, check your service table.
Thanks a lot
I try to create a login page using jsp, but it is showing invalid column index. While I run the same code in db. It runs smoothly.
please someone help me.
Here is my code,
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","ksrce_ceats","ceats");
PreparedStatement pre=null;
ResultSet rs;
pre = con.prepareStatement("select * from login_tbl where empid='?' and pass='?'");
pre.setString(1,request.getParameter(user));
pre.setString(2,request.getParameter(pass));
rs = pre.executeQuery();
if(rs.next()){
response.sendRedirect("user.html");
session.setAttribute("empid", user);
}else {
out.println("Invalid password try again");
}
}
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.
or may be a column doesn't exist
I am facing problem of invalid column index error.please help me
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.util.GetMyConnection;
public class JdbcResultSet {
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con;
PreparedStatement prp;
String sql = "select * from sid where id =?";
ResultSet rs;
try{
con = GetMyConnection.getMyConnection();
prp = con.prepareStatement(sql);
// setter
prp.setInt(1,Integer.parseInt(args[0]));
rs = prp.executeQuery();
// looping through the rows and cols
/*while(rs.next()){
// fetching the first column data
System.out.println("id is"+ rs.getInt(1));
// fetching the column name
System.out.println("name "+rs.getString("name"));
}*/
if(rs.next()){
System.out.println("sucess");
System.out.println(rs.getString(2));
}
else
System.out.println("sorry");
}
catch(Exception e){
e.printStackTrace();
}
}
}
select
dd.DELIVERY_ID,
dd.DELIVERY_NAME,
dd.DELIVERY_STATUS_ID,
dd.PUBLIC_FLAG,
case
when dd.DELIVERY_STATUS_ID = '2' and dd.PUBLIC_FLAG = '1' Then 'Y'
Else 'N'
END as Is_it_Deliverable?
from data_governance.DC_DELIVERABLE as dd
where dd.DELIVERABLE_NAME ilike '%ix_man.DIM_PORT_TO_CITY%';
facing an error >>>code 0 sql state 10940
In case statement >>> (dd.DELIVERY_STATUS_ID = '2' and dd.PUBLIC_FLAG = '1')
Which data type is IDE and PUBLIC_FLAG? Are they varchar?
Post a Comment