CODEWITHSUNDEEP
javasql-updateh2textfield
Juris Puņeiko
Juris Puņeiko

Reputation: 3

Can't find an Error in SQL update statement

I'm working in one quiz game. There is question maker window. Which works good for saving question. But when want update one of text Field and press save, than error is happening. something is wrong with syntax?!

void insertCell(String tableNamer, String column, String value, int id) throws ClassNotFoundException, SQLException{
    Class.forName("org.h2.Driver");
    Connection conn = DriverManager.getConnection("jdbc:h2:file:C:/Users/Juris Puneiko/IdeaProjects/for_my_testings/src/sample/DB/Questions/For_Private/Easy", "Juris", "1");

    PreparedStatement ps = conn.prepareStatement("UPDATE ? SET ? = ? where  ID = ?");
    ps.setString(1, tableNamer);
    ps.setString(2, column);
    ps.setString(3, value);
    ps.setInt(4, id);
    ps.executeUpdate();
    ps.close();
    conn.close();
}

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "UPDATE ?[*] SET ? = ? WHERE ID = ? "; expected "identifier"; SQL statement: UPDATE ? SET ? = ? where ID = ? [42001-196]

What is this >>> [*]?

What does it mean?

Screenshot

Upvotes: 0

Views: 846

Answers (2)

Jiri Tousek
Jiri Tousek

Reputation: 12440

The placeholders can only be used for values in most SQL databases, not for identifiers like table or column names:

"UPDATE myTable SET myCol = ? where ID = ?" -- OK
"UPDATE ? SET ? = ? where ID = ?" -- not OK

The reason is that those parameters are also used for prepared statements, where you send the query to the database once, the database "prepares" the statement, and then you can use this prepared statement many times with different value parameters. this can improve DB performance because DB can compile and optimize the query and then use this processed form repeatedly - but to be able to do this, it needs to know names of the tables and columns involved.

To fix this, you only leave the ?s in for the values, and you concatenate the tableNamer and column manually: 

"UPDATE " + tableNamer + " SET " + column + " = ? where ID = ?"

Keep in mind though that by doing this, tableNamer and column are now potentially vulnerable to SQL injection. Make sure that you don't allow user to provide or affect them, or else sanitize the user input.

Upvotes: 2

Mountain-Lion
Mountain-Lion

Reputation: 31

String sql = "UPDATE " + tableNamer + " SET " + column + " = ? where  ID = ?";
PreparedStatement ps = conn.prepareStatement(sql);
    ps.setString(1, value);
    ps.setInt(2, id);
    ps.executeUpdate();
    ps.close();
    conn.close();

Upvotes: 3

Related Questions