SQLException - Generated keys not requested (MySQL)

Question

I get this error when im making a new character to my game, in the CreateCharHandler it sends "saveToDb(false);" but when im ingame with another char i manually created i can saveToDb(true); with no error. please help, why is this happening?

http://i56.tinypic.com/oh1pn5.png

SaveToDb method http://pastebin.com/9sT5XBxp

line 3514 is:

ResultSet rs = ps.getGeneratedKeys();

Thanks in advance!

Answer 1

Your SQLException clearly states that:

You need to specify Statement.RETURN_GENERATED_KEYS to the Statement.executeUpdate() or Connection.prepareStatement().

This can be achieved as follows (adding an additional value on Connection.prepareStatement() method):

String SQL = ""; //whatever my String is
PreparedStatement ps = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "value");
//Other necessary ps.setXXX() methods

//now update
ps.executeUpdate();

ResultSet rs = ps.getGeneratedKeys();

The Statement.RETURN_GENERATED_KEYS is key here.

Hope this helps!

PS: Useful resource.

---

@Charlie berg, since you prefer being lazy, I changed line 13 of your code to include the Statement.RETURN_GENERATED_KEYS:

ps = con.prepareStatement("INSERT INTO characters (level, fame, str, dex, luk, `int`, exp, hp, mp, maxhp, maxmp, sp, ap, gm, skincolor, gender, job, hair, face, map, meso, hpMpUsed, spawnpoint, party, buddyCapacity, messengerid, messengerposition, mountlevel, mounttiredness, mountexp, equipslots, useslots, setupslots, etcslots, monsterbookcover, watchedcygnusintro, vanquisherStage, dojopoints, lastDojoStage, finishedDojoTutorial, vanquisherKills, matchcardwins, matchcardlosses, matchcardties, omokwins, omoklosses, omokties, givenRiceCakes, partyquestitems, jailtime, accountid, name, world) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

Also, Statement class is of package java.sql (make sure you import correctly). :-)

Answer 2

Oracle Documents:

If there is no indication that auto-generated columns should be made available for retrieval, a call to Statement.getGeneratedKeys will return a null ResultSet.

You should explicitly tell to JDBC That you want generated keys.

like this:

Statement stmt = conn.createStatement();
stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);

or

conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);

and then you can use getGeneratedKeys().