2

I am writing a student management app and have created a function to update student data-

 public static void updateStudent(int id, int input) throws SQLException {
    Scanner sc = new Scanner(System.in);   //Scanner object
    Connection connection = ConnectionSetup.CreateConnection();  //Setting up connection
    String updateStatement = "UPDATE student_details SET ? = ? WHERE 's_id' = ?;"; //Initializing query
    PreparedStatement pstmt = connection.prepareStatement(updateStatement);

    System.out.println("Enter new name: ");
    String newName = sc.nextLine();
    pstmt.setString(1,"s_name"); //sets first ? to the columnname
    pstmt.setString(2,newName); //sets the second ? to new name
    pstmt.setString(3, String.valueOf(id));  //sets the third ? to the student ID
    pstmt.execute(); //executes the query

All the other CRUD functions work fine, but this one throws the following error after inputting all the info-

Exception in thread "main" java.sql.SQLSyntaxErrorException: 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 ''s_name' = 'Prateek' WHERE 's_id' = '6'' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
at com.Student.manage.StudentFunc.updateStudent(StudentFunc.java:76)
at Start.main(Start.java:58)

I tried printing the finalized query and it has correct syntax and works in the MySQL Console-

SQL Query is: UPDATE student_details SET 's_name' = 'new name' WHERE 's_id' = '6';

What is the bug here? Please help me understand.

ipritik
  • 80
  • 7

1 Answers1

3

You can't use query parameters for column names (or any other identifer, or SQL keywords, etc.). When you use a query parameter, it is interpreted as a constant value. So your UPDATE statement is executed as if you had written it this way:

UPDATE student_details SET 's_name' = 'new name' WHERE 's_id' = '6';

This does not work. You can't use a string constant value as the left hand side of an assignment*. When I test it in my local MySQL client, I get this error:

ERROR 1064 (42000): 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 ''s_name' = 'new name' WHERE 's_id' = '6'' at line 1

The error reports it got confused at 's_name', because a quoted string literal is not valid at that place in an UPDATE statement.

The WHERE clause is also a problem. It's not a syntax error, but it doesn't do what you probably intended.

WHERE 's_id' = '6';

This compares the string value 's_id' to the string value '6', it does not compare the column s_id to a value. Obviously the string 's_id' is not equal to '6', so the condition will always evaluate as false, and no rows will be updated.

* You can't put a constant value on the left hand of an assignment in most other programming languages, either.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • 1
    In addition to that, a semicolon is not required and should generally be omitted when using anything but a query console – Felix Nov 11 '21 at 18:59
  • 2
    @Felix, You're right that it's not required, but it's not the cause of the syntax error in this question. – Bill Karwin Nov 11 '21 at 18:59
  • Hey, thanks for helping me out. Is there a way to send column names as a parameter in a named query? Or do I have to write different queries for all different columns like s_phone, s_number, s_age, s_city etc? – ipritik Nov 11 '21 at 19:21
  • The column name must be fixed in the SQL query before you pass it as an argument to `connection.prepareStatement()`. You can build the string using string-concatenation techniques or a `StringBuilder`. – Bill Karwin Nov 11 '21 at 19:29