java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). While using PreparedStatement [duplicate]

Question

Using preparedStatement in Java/MariaDb as in the following function

public ArrayList<String> findPath(String roomName) throws SQLException, IOException, InstantiationException, IllegalAccessException, ClassNotFoundException
{
    ArrayList<String> path = new ArrayList<String>(); 
    connection = getConnection();
    String queryPattern = "SELECT `Livello_1`, `Livello_2`, `Livello_3`, `Livello_4` FROM Camera WHERE Camera.Nome = '?'";
    PreparedStatement queryStatement = connection.prepareStatement(queryPattern);
    queryStatement.setString(1, roomName);
    ResultSet rs = queryStatement.executeQuery();
    if(rs.next())
    {
        for(int i = 0; i < 3; i++)
        {
            path.add(rs.getString(i));
        }
    }
    return path;
}

I obtain the error message:

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

and the error line number points to line

queryStatement.setString(1, roomName);

Remove the quotes from '?'. And stop using ` for your column names, they are not necessary. — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Dec 3, 2013 at 22:06
Doing that I obtain java.sql.SQLException: Column Index out of range, 0 < 1. — Germano Massullo
– Germano Massullo, Commented Dec 3, 2013 at 22:07
'?' counts as a literal questionmark, not a placeholder for paramters. — zapl
– zapl, Commented Dec 3, 2013 at 22:09

Eran · Accepted Answer · 2013-12-03 22:09:27Z

4

As was mentioned in the comment, you should remove the quotes from the ?.

In addition, in rs.getString(i), i should be positive. Start the count of the loop from 1.

To summarize:

public ArrayList<String> findPath(String roomName) throws SQLException, IOException, InstantiationException, IllegalAccessException, ClassNotFoundException
{
    ArrayList<String> path = new ArrayList<String>(); 
    connection = getConnection();
    String queryPattern = "SELECT Livello_1, Livello_2, Livello_3, Livello_4 FROM Camera WHERE Camera.Nome = ?";
    PreparedStatement queryStatement = connection.prepareStatement(queryPattern);
    queryStatement.setString(1, roomName);
    ResultSet rs = queryStatement.executeQuery();
    if(rs.next())
    {
        for(int i = 1; i < 4; i++)
        {
            path.add(rs.getString(i));
        }
    }
    return path;
}

answered Dec 3, 2013 at 22:09

Eran

395k57 gold badges726 silver badges793 bronze badges

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

1 Comment

Jake Over a year ago

Great solution for me too, I never saw anywhere that these sorts of SQL methods were not zero-indexed. Had the same problem setting prepared statement params from a for loop starting at 0. Thanks.

Collectives™ on Stack Overflow

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0). While using PreparedStatement [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related