UnsupportedOperationException Statement.getGeneratedKeys()

[David Weber]

Hi guys I am trying to retrieve the auto_incremented value from a MySQL Insert Statement,
And I keep getting the following exception:

java.lang.UnsupportedOperationException
        at sun.jdbc.odbc.JdbcOdbcStatement.getGeneratedKeys(JdbcOdbcStatement.java:1460)
        at dc3_stock_server.MyImplementation.update(MyImplementation.java:31)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:305)
        at sun.rmi.transport.Transport$1.run(Transport.java:159)
        at java.security.AccessController.doPrivileged(Native Method)
        ....

I have googled and googled and so far no fix, have tried prepared statements as well as statements.

My code:

    public synchronized int update(String sql) throws RemoteException {
        int i = -1;
        try {
            Connection con = new DBConnection().getConnection();
            Statement st = con.createStatement();
            st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
-- Throws Exception Here
            ResultSet res = st.getGeneratedKeys();
            while(res.next()){
                i= res.getInt(1);
            }
            st.close();
            con.close();
        } catch (SQLException ex) {
            System.out.println("Problem in my implementation:");
            ex.printStackTrace();
        }
        return i;
    }

Any help will be great guys!, thanks in advance.

Extra Info:

Windows 7 32-bit
NetBeans 6.7
MySQL 5.1
MySQL ODBC Connecter 5.1.5
JDK 6

Regards, David

[Scott Selikoff]

I love error messages like these because its pretty clear what the problem is. In this case "UnsupportedOperationException" means your JDBC driver does not support this feature. Retrieving generated keys is NOT a standard feature nor is it supported by all drivers. Your only choices are to switch to a different driver (often there isn't one), switch to a different database (thats rarely an option), or find another way programmatically to deal with the problem. I actually wrote an article on this some time back: Database Key Generation in Java Applications

[David Weber]

Thanks Scott,

My mistake was using the standard jdcb.odbc.driver instead of the mysql.jdbc.driver

Problem solved!

[Jan Cumps]

The native MySQL jdbc driver for your MySQL version supports it.
If you stop using the odbc bride, and start using the jdbc driver, your code will work.

Regards, Jan

(Edit: I managed to miss the last post in the thread. Glad it's solved.)