Prepared Statement syntax for ON DUPLICATE KEY UPDATE (number of params error)

Question

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. :)

You don't want `'` quotes around your `?`, which I notice is what your have after the 6 first parameters. Maybe that's the beef. — Sotirios Delimanolis, Apr 02 '13 at 20:03

score 33 · Answer 1 · edited Aug 01 '17 at 18:49

33

Why not just get rid of putting binding param in the duplicated values itself that is following query:

INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE col1=?, col2=?, col3=?;

Can be re-written as:

INSERT INTO mytable (col1, col2, col3) VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3);

IMO one should prefer 2nd over 1st because:

not using binding param for 'ON DUPLICATE KEY UPDATE' part in the query means you don't have to write extra piece of code (or iteration in your case) to bind them
writing less code means fewer chances of making binding errors (typos etc.)
it has better readability

edited Aug 01 '17 at 18:49

Community

1
1

answered Mar 27 '14 at 06:05

sactiw

21,935
4
41
28

1

I don't understand how this is any better. It is more verbose for sure, but also does not take into account anything surrounding my original question(why quotes don't work, or anything with dynamic values). – KisnardOnline Mar 28 '14 at 17:14
1

@JayAvon First of all, it perfectly supports dynamic values i.e. both the statement in my answer will give you exactly same result in case of duplicate inserts. That said, one should prefer 2nd over 1st because it gives you following benefits: - since you no more use binding param for 'ON DUPLICATE KEY UPDATE' part thus you don't have to write extra piece of code (or iteration in your case) to bind them - Writing lesser code means lesser chance of making binding errors (typos etc) - Also, as you have already figured out it has better readability – sactiw Apr 01 '14 at 08:09

score 5 · Accepted Answer · edited Dec 10 '19 at 13:53

5

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.

edited Dec 10 '19 at 13:53

Machavity

30,841
27
92
100

answered Apr 02 '13 at 20:23

Sotirios Delimanolis

274,122
60
696
724

1

+1 for explaining why '(?)' won't work since it gets considered as string literal and not a binding param – sactiw Mar 27 '14 at 06:08

Prepared Statement syntax for ON DUPLICATE KEY UPDATE (number of params error)

2 Answers2