CODEWITHSUNDEEP
javaoracle-databasejdbc
Sri2110
Sri2110

Reputation: 335

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

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.

Upvotes: 1

Views: 85

Answers (2)

jeremyt
jeremyt

Reputation: 593

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.

Upvotes: 0

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions