CODEWITHSUNDEEP
javamysqljdbc
user1508140
user1508140

Reputation: 13

JDBC MySql bind variable syntax error in where clause

I am getting this error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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

public static Person getDetails(int id) {
    Connection conn = null;
       PreparedStatement stmt = null;
       Person newPerson = new Person();
       try{
          //STEP 2: Register JDBC driver
          Class.forName("com.mysql.jdbc.Driver");

          //STEP 3: Open a connection
          System.out.println("Connecting to database...");
          conn = DriverManager.getConnection(DB_URL,USER,PASS);

          //STEP 4: Execute a query
          System.out.println("Creating statement...");
          String sql = "SELECT firstName, lastName, birthday FROM person WHERE id=?";
          System.out.println("SQL Statement:\n\t" + stmt);
          stmt = conn.prepareStatement(sql);
          System.out.println("Prepared Statement before bind variables set:\n\t" + stmt.toString());
          //Bind values into the parameters.
          System.out.println("ID " + id);
          stmt.setInt(1, id);  // This would set id
          System.out.println("Prepared Statement after bind variables set:\n\t" + stmt.toString());

          // Let us select all the records and display them.
          ResultSet rs = stmt.executeQuery(sql);

          //STEP 5: Extract data from result set
          while(rs.next()){
             //Retrieve by column name
             String firstName = rs.getString("firstName");
             String lastName = rs.getString("lastName");
             Date birthday = rs.getDate("birthday");

             newPerson.setBirthday(birthday);
             newPerson.setFirstName(firstName);
             newPerson.setLastName(lastName);
             newPerson.setId(id);

             //Display values
             System.out.print("ID: " + id);
             System.out.print(", First: " + firstName);
             System.out.println(", Last: " + lastName);
             System.out.println(", Birthday: " + birthday);
          }
          //STEP 6: Clean-up environment
          rs.close();
          stmt.close();
          conn.close();
       }catch(SQLException se){
          //Handle errors for JDBC
          se.printStackTrace();
       }catch(Exception e){
          //Handle errors for Class.forName
          e.printStackTrace();
       }finally{
          //finally block used to close resources
          try{
             if(stmt!=null)
                stmt.close();
          }catch(SQLException se2){
          }// nothing we can do
          try{
             if(conn!=null)
                conn.close();
          }catch(SQLException se){
             se.printStackTrace();
          }//end finally try
       }//end try
       System.out.println("Goodbye!");
       return newPerson;
}

I have success executing the query without the where clause. I have looked at many examples and nothing I try fixes this.

Upvotes: 1

Views: 1565

Answers (3)

SparkOn
SparkOn

Reputation: 8956

Change 

ResultSet rs = stmt.executeQuery(sql);

to

ResultSet rs = stmt.executeQuery();

Upvotes: 1

Elliott Frisch
Elliott Frisch

Reputation: 201507

If I understand your question, the problem is you used Statement.executeQuery(String). I'm fairly certain you meant to use PreparedStatement.executeQuery(),

// Let us select all the records and display them.
ResultSet rs = stmt.executeQuery(sql);   // <-- adding sql here makes it use the
                                         //     Statement version.

You wanted to use

// Let us select all the records and display them.
ResultSet rs = stmt.executeQuery();      // <-- use the version from PreparedStatement

Upvotes: 1

MadProgrammer
MadProgrammer

Reputation: 347314

Don't use executeQuery(String) with prepared statements...

Instead of....

ResultSet rs = stmt.executeQuery(sql);

use...

ResultSet rs = stmt.executeQuery();

Take a look at How to use Prepared Statements for more details

Upvotes: 2

Related Questions