invalid column index error with Prepare statement

Question

I am getting invalid column index for following prepared statement.
Here is my code

// Excluding some unnecessary code
counter = 1;
if (rsTableNames.next()) 
{
    // Creating Query for prepared statement
    String getCode = "select * from ( select c_name from " 
          + rsTableNames.getString(1)+ " where lower(c_name) like ?%'";
    while (rsTableNames.next()) 
    {
      getCode += " union select c_name from " + 
      rsTableNames.getString(1)+ " where lower(c_name) like ?%'";
      counter++;
    }
    getCode += " ) where rownum <= " + maxRecords;
        // Now  The getCode contains 3 place holders ie ?           
    pst = con.prepareStatement(getCode);
    String param = "'" + query.toLowerCase();

    for(int i=1;i<=counter;i++)
    {
        pst.setString(i,param);  // when i=3 exception is thrown
    }
}

I am getting the exception when i becomes 3 though the query contains 3 place holders.
EDIT (HINT): I think the problem is with the ' which is creating havoc. How can we escape it?

Jon Skeet · Accepted Answer · 2011-08-11 05:20:02Z

6

I don't know whether it's the cause of the problem, but I don't think parameters work quite the way you think they do when it comes to quoting. You're still adding quotes in your code after each parameter and as the start of your parameter. I suspect you just want:

rsTableNames.getString(1)+ " where lower(c_name) like ?";

in each place, then:

String param = query.toLowerCase() + "%";

It's possible that due to quote parsing, this will fix the issue - I think your middle parameter is being deemed to be part of a big literal.

answered Aug 11, 2011 at 5:20

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

5 Comments

Ajinkya Over a year ago

Thanks for the suggestion. I tried it, it removed the error but it creates wrong query. I guess ' is required for like clause. I think I need to escape ' but dont know how.

Jon Skeet Over a year ago

@Ajinkya What do you mean by "wrong query"? Please give examples of what you're getting in terms of results vs what you expected to get.

Ajinkya Over a year ago

@John: It creates query with .. where like query% (It removes ' from like clause which is required. ) so it gives zero result thoguh there are some records in database.I think clause should be somewhat like ..where like 'query%'.

Jon Skeet Over a year ago

@Ajinkya: Phew - I was running out of things I could think of. Glad it's all sorted!

Ajinkya Over a year ago

Sorry I made you think out of the box :)

Gapton · Accepted Answer · 2011-08-11 05:24:32Z

0

are you sure there are 3 ? in the sql? try print the entire preparedStatement in the for loop.

I suspect that the many loops you have here might not have worked like you expected, and in the end there were only 2 parameters in the preparedStatement.

What exception was thrown? From Java API: Throws: SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement

also, if you share the con connection, and the con.preparedStatement instance, make sure you have close each properly

answered Aug 11, 2011 at 5:24

Gapton

2,1823 gold badges21 silver badges34 bronze badges

1 Comment

Ajinkya Over a year ago

Everything is in place. The problem is with the '.

Collectives™ on Stack Overflow

invalid column index error with Prepare statement

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related