[logback-dev] [JIRA] Commented: (LBCORE-15) Problems getting autogenerated Keys in Oracle with Oracle JDBC 10.2.03

[Søren Boisen (JIRA)]

    [ http://jira.qos.ch/browse/LBCORE-15?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=10771#action_10771 ] 

Søren Boisen commented on LBCORE-15:
------------------------------------

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.

> Problems getting autogenerated Keys in Oracle with Oracle JDBC 10.2.03
> ----------------------------------------------------------------------
>
>                 Key: LBCORE-15
>                 URL: http://jira.qos.ch/browse/LBCORE-15
>             Project: logback-core
>          Issue Type: Bug
>          Components: Appender
>    Affects Versions: unspecified
>         Environment: Operating System: Windows
> Platform: PC
>            Reporter: Klaus Unger
>            Assignee: Logback dev list
>            Priority: Blocker
>
> I've encountered troubles logging StackTrace/MDC entries with die DBAppender on our plattform! We are using Oracle 9.2.0.6.0 on a Solaris Box with Oracle JDBC Driver 10.2.0.3!
> I've debugged the processing of LogMessages into DBAppenderBase.append()!
> There I found a call "con.prepareStatement(getInsertSQL())" which is followed by a call "subAppend(...)"
> As far as I understand Oracle Drivers return generatedKeys only, if you use the right prepareStatement() methods, which is in my understanding the following:
> PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
>                                    throws SQLException
> @see http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20int)
> which is available since JDK 1.4

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.qos.ch/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira