RETURN_GENERATED_KEYS doesn't work using JDBC ODBC

Question

I'm trying to get insert ID while after inserting some data in my database.

String sql = "INSERT INTO ADI.DUMMY(dummy_data) VALUES('from database logger')";
PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
int extUptReturn = ps.executeUpdate(sql);

But I got this exception:

Java exception: ''java.lang.UnsupportedOperationException''; 
    thrown from class name: ''sun.jdbc.odbc.JdbcOdbcConnection'', method name: ''prepareStatement'', file: ''JdbcOdbcConnection.java'', line: '1762'

score 7 · Accepted Answer · edited May 23 '17 at 10:27

7

The ODBC bridge driver doesn't support it. Nothing to do against. Either replace the driver or live with it. I would just use a real JDBC driver instead of the poorly-developed, feature-lacking, bug-rich Sun ODBC bridge driver. Almost all self-respected server based RDBMS vendors provides a fullworthy JDBC driver for download at their homepage. Just Google "[vendorname] jdbc driver download" to find it. Here's an overview:

MySQL JDBC driver
PostgreSQL JDBC driver (note: older versions didn't support generated keys as well).
Oracle JDBC driver (note: older versions didn't support generated keys as well).
MSSQL JDBC driver (or performancewise better, the jTDS JDBC driver)
DB2 JDBC driver is hard to find in IBM's online forest, but it's usually already included in the /java folder of the DB2 installation.
UCanAccess driver for Microsoft Access databases (more details here).

edited May 23 '17 at 10:27

Community

1
1

answered Feb 12 '10 at 13:55

BalusC

992,635
352
3,478
3,452

1

-1 for unnecessary Access bashing. The reason why there isn't a driver for it is not because it's not an RDBMS, but because it's not a server database engine and shouldn't be used as a data store in the contexts in which JDBC is in use. Of course, I'm one of thos who believes Java has no place on the client... – David-W-Fenton Apr 04 '10 at 00:05
@David: I didn't bash Access, I bashed the JDBC-ODBC bridge driver. – BalusC Apr 04 '10 at 04:14
1

You said Access "isn't a fullfledged RDBMS". That's bashing Access, period, end of statement, no ambiguities whatsoever, and is only true if you have a WRONG definition of RDMBS. You lessened the quality of your answer by including it. You could delete that phrase and it wouldn't change the advice you give one iota. – David-W-Fenton Apr 04 '10 at 19:28
@David: I should indeed maybe have added "server" behind "RDBMS" to clarify the bit a bit more. Thank you for the nitpick and sorry that it wasn't clear to you at first glance. – BalusC Apr 04 '10 at 21:17
Then you don't need RDBMS, just "Jet/ACE is not a database server". And I'm the first to suggest that it's probably not appropriate for most Java-based apps for that reason. That is, it's inappropriate not because it's not an RDBMS (for some definition of RDBMS), but that it's not a *server* database engine, as you say. Why not edit, and I'll back out my downvote. – David-W-Fenton Apr 06 '10 at 00:47
1

BTW, it occurs to me that it may be possible to get to Jet/ACE data via the SQL Server driver, after having set up your Jet/ACE database as a linked server... – David-W-Fenton Apr 06 '10 at 00:48
@David: There, I put "server based" in. – BalusC Apr 06 '10 at 01:45
I tried to reverse my downvote based on your edit, but it's too old to allow me to do so. Thanks for taking the time to edit. – David-W-Fenton Apr 07 '10 at 20:50
*"Only for MS-Access ... you'll be forced to stick to ODBC bridge driver"* - That is no longer true. [UCanAccess](http://ucanaccess.sourceforge.net/site.html) is a free, open-source, pure Java JDBC driver for Access databases, and it supports `RETURN_GENERATED_KEYS`. More details [here](http://stackoverflow.com/q/21955256/2144390). – Gord Thompson Mar 09 '15 at 10:33

score 0 · Answer 2 · answered Feb 12 '10 at 04:07

0

MAy be the JDBC implementation could not be supporting the sepcific opration. CHeck the JDBC driver used.

answered Feb 12 '10 at 04:07

PassionatedDeveloper

706
1
6
10

score 0 · Answer 3 · answered Dec 14 '15 at 08:42

Try this example instead of Statement.RETURN_GENERATED_KEYS:

String[] returnId = { "BATCHID" };
String sql = "INSERT INTO BATCH (BATCHNAME) VALUES ('aaaaaaa')";
PreparedStatement statement = connection
        .prepareStatement(sql, returnId);
int affectedRows = statement.executeUpdate();

if (affectedRows == 0) {
    throw new SQLException("Creating user failed, no rows affected.");
}

try (ResultSet rs = statement.getGeneratedKeys()) {
    if (rs.next()) {
        System.out.println(rs.getInt(1));
    }
    rs.close();

}

Where BRANCHID is the auto generated id

RETURN_GENERATED_KEYS doesn't work using JDBC ODBC

3 Answers3

Linked