invalid column index error in java while fetching data from oracle 10g database. Cant figure out whats wrong

Question

Code snippet: On a button click, actionevent will be called

public void actionPerformed(ActionEvent e)
{
Function f = new Function();

Function is a nested class which i have used to establish the connection with the database. The code snippet for function class is also provided in the end.

ResultSet rs = null;
String Cid ="cust_id";
String Pno="cust_phone";
String cat="cust_cat";
String start_date="st_date";
String Adv_amt="adv";
String Adv_end="end_date";
String Address="addr";

t2 is the Textfield name which i have used to get entry of customer name. I want to use this customer name as a PK to fetch all the other data about that customer from DB.

rs=f.find(t2.getText());
try{
    if(rs.next())
    {
        t1.setText(rs.getString("cust_id"));
        t3.setText(rs.getString("cust_phone"));
        t4.setText(rs.getString("cust_cat"));
        t5.setText(rs.getString("st_date"));
        t6.setText(rs.getString("adv"));
        t7.setText(rs.getString("end_date"));
        t8.setText(rs.getString("addr"));
    }
    else
        JOptionPane.showMessageDialog(null,"No data for this name");
}
catch(Exception ex)
{
    JOptionPane.showMessageDialog(null,ex.getMessage());
}
}

Here is the code snippet for nested class Function which is inside the main class:

class Function{
Connection con=null;
ResultSet rs= null;
PreparedStatement ps = null;
public ResultSet find(String s)
{
    try
    {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@Localhost:1521:xe","system","qwerty");
    ps= con.prepareStatement("Select * from gkkdb where cust_name='?'");

    ps.setString(1,s);

    rs= ps.executeQuery();
    }
    catch(Exception ex)
    {
        JOptionPane.showMessageDialog(null, ex.getMessage());
    }
        return rs;
}
}

Please help figure out the problem.

user330315 · Accepted Answer · 2016-10-07 11:43:23Z

2

Don't put the parameter placeholder ? in single quotes.

This:

ps = con.prepareStatement("Select * from gkkdb where cust_name='?'");

should be

ps = con.prepareStatement("Select * from gkkdb where cust_name = ?");

The ? is not recognized as a placeholder if you enclose it in single quotes.

answered Oct 7, 2016 at 11:43

user330315

Sign up to request clarification or add additional context in comments.

Comments

jeremyt · Accepted Answer · 2016-10-07 11:57:21Z

0

Sorting out the bind variable will fix your immediate issue.

You should explicitly specify what columns you want selected and that way you'll only get what you need (someone might add a BLOB column later) and you'll get them in the right order (someone might change the table create script before running on another DB instance, although you are looking up the columns by name, a different order would only impact if you were using positional indexes).

Ditto on the other answer re: bind variables (i.e. no quotes)

Plus, "select * from" is never a good idea, ask your DBA.

Obviously your code is for example, but you should make sure you free up any resources (Connection, Statement, ResultSet) as soon as they are done with. Use Java 7 try-with-resources.

edited Oct 7, 2016 at 11:57

answered Oct 7, 2016 at 11:51

jeremyt

6035 silver badges6 bronze badges

Collectives™ on Stack Overflow

invalid column index error in java while fetching data from oracle 10g database. Cant figure out whats wrong

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related