Prepared Statement syntax for ON DUPLICATE KEY UPDATE (number of params error)
java, mysql, on-duplicate-key, prepared-statement, sql-update
Solution
In the context of a `PreparedStatement`, the character `?` is a bind parameter to be replaced by some value later (with setters). If you use it within quotes, then the statement considers it as a literal, not to be replaced. This part of your query:
"`wai_type`='(?)', `wai_id`='(?)', `character_id`='(?)', `looted`='(?)'",
is setting `wai_type` to `(?)`, which is not what you want. Get rid of the single quotes. Actually, get rid of the
characters as well.Problem
Here is what I am trying to do. I want to insert into this table or update the record if the primary key(entity_id) exists. I am just having an issue with the SQL syntax. It wont let me have more params than the first amount of 'VALUES' so I get the following error: Parameter index out of range (7 > number of parameters, which is 6). ``` int insertOrUpdateSuccess = MyDBSyncher.UPDATE("INSERT INTO " + DB_NAME + ".entities " + "(`entity_id`, `wai_type`, `wai_id`, `character_id`, `looted`, `creation_time`) " + "VALUES ((?), (?), (?), (?), (?), (?)) " + "ON DUPLICATE KEY UPDATE " + "`wai_type`='(?)', `wai_id`='(?)', `character_id`='(?)', `looted`='(?)'", new String[]{tmpEntityId, values[0], values[1], values[2], values[3], values[4], values[0], values[1], values[2], values[3]}); ``` This is kind of similar I think to what I am asking but I could not interpret it for my needs. Sorry to possibly post a duplicate. Ohh and here is the UPDATE() function in my code: ``` public static int UPDATE(String updateStatement, String[] params){ try { if(!conn.isClosed()) { logger.trace("Successfully connected to MySQL server using TCP/IP - " + conn); stat = conn.prepareStatement(updateStatement); for (int i = 0; i < params.length; i++){ stat.setString(i+1, params[i]); } return stat.executeUpdate(); } } catch(SQLException eSQL) { logger.fatal(eSQL.getMessage()); } return -1; } ``` Thanks for any help with this. :)