CODEWITHSUNDEEP
springoracle-databasetransactionswebsphere
Kai
Kai

Reputation: 708

Is there a way to overcome DSRA9010E "'setReadOnly' is not supported on the WebSphere" exception?

our application is using Spring for TX management and is marking certain transactions as readonly. When deploying our application on websphere (8.5.5.3) with a Oracle JDBC Connection we are getting exceptions like the following:

Caused by: java.sql.SQLException: DSRA9010E: 'setReadOnly' is not supported on the WebSphere java.sql.Connection implementation.
    at com.ibm.ws.rsadapter.spi.InternalOracleDataStoreHelper.setReadOnly(InternalOracleDataStoreHelper.java:371)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.setReadOnly(WSJdbcConnection.java:3646)
    at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.getTargetConnection(LazyConnectionDataSourceProxy.java:410)
    at org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy$LazyConnectionInvocationHandler.invoke(LazyConnectionDataSourceProxy.java:376)
    at com.sun.proxy.$Proxy476.getMetaData(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:55)
    at java.lang.reflect.Method.invoke(Method.java:619)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:239)
    at com.sun.proxy.$Proxy476.getMetaData(Unknown Source)

I know what websphere is trying to tell me but I was wondering if there is a way to disable this check so that Connection.setReadonly calls are just ignored instead of throwing an exception.

Of course I could also change the application to not use readonly transactions but that would be much more complicated.

Upvotes: 2

Views: 5589

Answers (2)

simonalexander2005
simonalexander2005

Reputation: 4577

Oracle JDBC (12c onwards; perhaps 11g as well?) can be tricky to use with readonly - according to https://marschall.github.io/2017/01/28/oracle-read-only.html:

Calling Connection.setReadOnly(true) with the 12c driver no longer establishes a read only transaction

This means that Spring (before version 4.3.7) also struggles to set read only transactions with Oracle JDBC (see previous link).

To overcome this, you need to manually include SET TRANSACTION READ ONLY in your SQL, instead of relying on Spring's @Transactional(readOnly=true).

However, from Spring 4.3.7, the transaction now acts correctly, and so you should no longer see this issue (https://github.com/spring-projects/spring-framework/issues/19774)

Upvotes: 0

Gas
Gas

Reputation: 18050

Try to unwrap the connection object like this:

Context ic = new InitialContext();
DataSource ds = (DataSource)ic.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();

if (conn.isWrapperFor(oracle.jdbc.OracleConnection.class)) {
     // Returns an object that implements the given interface to
     // allow access to non-standard methods, or standard methods
     // not exposed by the proxy.
     oracle.jdbc.OracleConnection oraCon = conn.unwrap(oracle.jdbc.OracleConnection.class);
     // Do some Oracle-specific work here.
     oraCon.setReadOnly(readOnly);
     ....

}
conn.close();

See WebSphere Application Server and the JDBC 4.0 Wrapper Pattern

Upvotes: 1

Related Questions