CODEWITHSUNDEEP
javasqlprepared-statement
Ajinkya
Ajinkya

Reputation: 22730

invalid column index error with Prepare statement

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?

Upvotes: 1

Views: 4682

Answers (2)

Gapton
Gapton

Reputation: 2124

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

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1504092

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.

Upvotes: 6

Related Questions