MySQL syntax exception, working manually

Question

I'm using this query manually and it works fine, what I'm missing? If I don't use WHERE, it executes perfectly.

for (ArrayList<String> match : matches) {
 System.out.println(match.get(0));
 // String
 // 7412095225787794836
 String query1 = "SELECT COUNT(*) FROM `matches_players` WHERE `match_id` = ?";
 PreparedStatement preparedStmt1 = (PreparedStatement) conn.prepareStatement(query1);
 preparedStmt1.setString(1, match.get(0));
 ResultSet rs1 = preparedStmt1.executeQuery(query1);
 // You have an error in your SQL syntax; check the manual that corresponds to your 
 // MySQL server version for the right syntax to use near '?' at line 1
 while (rs1.next()) {
 System.out.println("players=" + rs1.getInt("COUNT(*)"));
 }
}

• O. Jones: Does the match_id column have INT type? Can you try preparedStmt1.setInt(1, match.get(0).intValue());
• Kenny: @OllieJones It's a string. I know, that's stupid. But it comes from a ArrayList<String>.
• Antoan Milkov: Sometime integers are returned as strings, depending on the driver used. Can you see the DDL for the field match_id? I can bet it is integer.
• Kenny: @AntoanMilkov Using preparedStmt1.setString(1, "test") I get the same error, column type is VARCHAR.
• JB Nizet: You should ALWAYS post the full stack trace of an exception when asking about it.

Answer 1

I don't think the executeQuery method takes an argument. The SQL text has already been supplied in the prepare. Try removing the argument from the executeQuery method.

Replace this:

 ResultSet rs1 = preparedStmt1.executeQuery(query1);
 ^^^^^^

With this:

 ResultSet rs1 = preparedStmt1.executeQuery();

And see how big a smoke ball that makes.