Problems getting autogenerated Keys in Oracle with Oracle JDBC 10.2.03

Sébastien Pennec added a comment - 30/Mar/07 3:10 PM Hello Klaus, I've checked your problem, I guess that we could change the call to the prepareStatement() method. But before we change it, We have to test it against other databases. Dev notes: The test case of DBAppender uses HSQLDB. That database's driver does not support the call to prepareStatement(String sqlQuery, int getGeneratedKeys)[1]. It looks like MySQL supports that call. [1]http://hsqldb.org/doc/src/org/hsqldb/jdbc/jdbcConnection.html#prepareStatement(java.lang.String,%20int)

Klaus Unger added a comment - 26/Jun/07 4:42 PM Hello Sébastien, any news on this topic, as we are desperately waiting for a fix/solution many thanks in advance. kind regards K:)

John Gibson added a comment - 11/Jan/08 5:16 AM I think that we can fix this problem by making different prepareStatement() calls in DBAppenderBase depending upon the value of cnxSupportsGetGeneratedKeys. For example, this seemed to fix the problem for me with Oracle 10: PreparedStatement insertStatement; if(cnxSupportsGetGeneratedKeys) {   insertStatement = connection       .prepareStatement(getInsertSQL(), new String[] { "EVENT_ID" } ); } else {   insertStatement = connection       .prepareStatement(getInsertSQL()); }

Søren Boisen added a comment - 15/Oct/08 4:03 PM It's important to note that the original suggestion, to use prepareStatement(String, int) will not work. In this case Oracle will return the ROWID of the inserted row instead of the EVENT_ID column. As John Gibson wrote, prepareStatement(String, String[]) should be used instead, specifying EVENT_ID as the name of the column to be returned. For other people that cannot wait for this bug to be fixed, a workaround is to write your own class extending DBAppender: public class OracleDBAppender extends DBAppender {     @Override     public void append (LoggingEvent eventObject) {         Connection connection = null;         try {             connection = connectionSource.getConnection();             connection.setAutoCommit(false);             PreparedStatement insertStatement = connection.prepareStatement(                     getInsertSQL(), new String[] {"EVENT_ID"});             subAppend(eventObject, connection, insertStatement);             // we no longer need the insertStatement             if (insertStatement != null) {                 insertStatement.close();                 insertStatement = null;             }             connection.commit();         } catch (Throwable sqle) {             addError("problem appending event", sqle);         } finally {             DBHelper.closeConnection(connection);         }     } } Then in your configuration file simply declare the class of the appender to attach as your new OracleDBAppender class.

Ceki Gulcu added a comment - 21/Oct/08 11:51 PM I have tested the existing code on Oracle 11g (version 11.1.0.6.0) and it seems to work just fine. I'll see if I can reproduce it with Oracle 10g.

Ceki Gulcu added a comment - 22/Oct/08 11:00 PM - edited It's actually more of a JDBC driver issue then a RDBMS version issue. I could reproduce the problem on Oracle 10g using ojdbc14.jar (version 10.2.0.1). This JDBC drivers support the getGeneratedKeys method. The problem cannot be reproduced with an older driver, classic12.jar, version 9.0.1.4.0, because it does not support getGeneratedKeys(). Anyway, the code has been modified to use the connection. prepareStatement() method taking two arguments, the sql statement follows by the auto-generated columnNames, as suggested John Gibson. Surprisingly enough, this change is compatible with other databases supporting getGeneratedKeys(). Fixed in revision 1860