CODEWITHSUNDEEP
javamysqljdbc
LeoTietz
LeoTietz

Reputation: 329

Issue with prepared statement

In the following code:

PreparedStatement statement = conn
                .prepareStatement(SQLQueries.isMuted);
        statement.setString(1, player);
        statement.setString(2, player);
//in SQLQueries.java
public static final String isMuted = "SELECT EXISTS(SELECT * FROM "
        + "(SELECT playerid FROM mute, players AS player "
        + "WHERE player.username = '?' AND playerid = player.id"
        + "UNION ALL "
        + "SELECT playerid FROM tempmute, players AS player "
        + "WHERE player.username = '?' AND playerid = player.id) AS tbl) "
        + "AS isMuted;";

I get the following error:

[08:10:38 WARN]: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

I am very confused by that, because the documentation told me the parameter index starts at 1, and this is confirmed, because when I changed the setString statements to 0 and 1, respectivly, it gave me another error. Also, the statement has two placeholders, so I do not understand why this code fails.

Have I overlooked something in the documentation?

Upvotes: 0

Views: 71

Answers (2)

lreeder
lreeder

Reputation: 12206

Remove the quotes from around the ? PreparedStatement placeholders. The JDBC driver will quote the strings for you. When you quote the string placeholder yourself, the JDBC driver thinks you want to pass a literal ? in your statement.

You haven't mentioned the database you are using, but the main author for the MySQL JDBC driver confirms this here: http://forums.mysql.com/read.php?39,499385,499450#msg-499450

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

You need a space after player.id:

public static final String isBanned = "SELECT EXISTS(SELECT * FROM "
        + "(SELECT playerid FROM bans, players AS player "
        + "WHERE player.username = '?' AND playerid = player.id "
---------------------------------------------------------------^
        + "UNION ALL "
        + "SELECT playerid FROM tempban, players AS player "
        + "WHERE player.username = '?' AND playerid = player.id) AS tbl) "
        + "AS isBanned;";

There might be other problems as well.

Upvotes: 0

Related Questions