Workaround for using Oracle JDBC extension with WLS pooling
Eugene Voytitsky
with ClassCastException when tried to use Oracle JDBC extension
with WLS pooling. I also had.
In this case BEA recommends to use *dangerous*
method getVendorConnection() which exposes
the physical connection object to your code.
Yes it's really dangerous because of unsafe usage may breaks
WLS pooled connection(s).
Moreover, this practice will make your JDBC code
unportable (your JDBC code in addition to Oracle dependence
became Weblogic dependent):
void doSmth() {
Connection con = ...;
Connection vCon = ((WLConnection)con).getVendorConnection();
// + mess of usage con in one places and vCon in others
// (where Oracle extensions are needed)
// !Don't forget to don't close vCon!
}
Sux.
I found the workaround.
Introduction
============
Yes the real cause of ClassCastException is that
in depth of Oracle driver the casting
to class oracle.jdbc.driver.OracleConnection
(not to interface oracle.jdbc.OracleConnection)
is performed.
Someone can say that this is bug or pure desing.
Weblogic pooled connection provide dynamic
implementation for all public interfaces
which real physical (wrapped) connection object implements.
Great feature!
But I guess that all interface methods implemented
by simple call-delegation to physical (wrapped) connection object.
In case of oracle.jdbc.OracleConnection interface
this approach doesn't work for at least one its method:
public OracleConnection unwrap()
WLS pooled connection shoudn't implement this method by
delegation to physical connection object BUT should
return physical connection object itself!
// Wrong implementation of unwrap()
// delegation is used
public OracleConnection unwrap() {
return physicalConnection.unwrap();
}
// Right implementation of unwrap()
// physical connection returned
public OracleConnection unwrap() {
return physicalConnection;
}
Workaround
==========
1. Develop your own OracleConnection wrapper class:
import oracle.jdbc.OracleConnection;
import weblogic.jdbc.extensions.WLConnection;
public class MyOracleConnectionImpl implements OracleConnection {
private OracleConnection con;
public MyOracleConnectionImpl(OracleConnection connection)
throws SQLException
{
this.con = connection;
}
public OracleConnection unwrap() {
return (OracleConnection)
((WLConnection)con).getVendorConnection();
}
/* Implement all other methods by delegation to con object */
}
2. Don't get Connections directly from DataSource --
develop your own simple (may be static) utility
class which retrives Connections from dataSource
and returns them wrapped into your MyOracleConnectionImpl
to your code from some method:
puclic abstract class MyConnectionSource {
public static Connection getConnection() {
Connection con = // get it from DataSource
return new MyOracleConnectionImpl((OracleConnection)con);
}
}
3. Add attribute RemoveInfectedConnectionsEnabled="false"
to definition of your JDBCConnectionPool within config.xml
You may do it because of you `safely` use vendorConnection --
you don't expose it to application code.
4. Enjoy the Oracle JDBC extensions in your code!
Example:
Connection con = MyConnectionSource.getConnection;
ArrayDescriptor add =
ArrayDescriptor.createDescriptor("your_type", con);
Hope it helps to someone.
--
Best regards,
Eugene Voytitsky
Joseph Weinstein
thanks,
Joe
Eric Ma
Does it make sense for BEA to incorporate this code in WLS 8.1 SP2?
Eric Ma
Joseph Weinstein
Eric Ma wrote:
> Does it make sense for BEA to incorporate this code in WLS 8.1 SP2?
>
> Eric Ma
Yes, we're on it.
Joe
Eugene Voytitsky
>>Does it make sense for BEA to incorporate this code in WLS 8.1 SP2?
>>
> Yes, we're on it.
> Joe
Great news, thanks.
Ram Kishore
Hello Eugene Voytitsky,
Thanks Eugene Voytitsky for your idea
I have tried the solution suggested by You, but it did not work.
It still throws ClassCastException.
I am sorry for posting the whole code of two classes below.
I did this to give you more clarity.
I am also indicating the place where the exception was thrown..
Please let me know if I am doing something wrong.
OracleConnection Wrapper class
******************************************
package ejbTesting;
// sql imports
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
// util imports
import java.util.Map;
import java.util.Properties;
// imports from Oracle Driver Classes
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleOCIFailover;
import oracle.jdbc.OracleSavepoint;
// import from Weblogic extensions
import weblogic.jdbc.extensions.WLConnection;
public class WeblogicConnectionWrapper implements OracleConnection
{
// oracle connection object
private OracleConnection connection;
public WeblogicConnectionWrapper (OracleConnection orclConnection)
{
try
{
this.connection = orclConnection;
}
catch(Exception unexpected )
{
unexpected.printStackTrace();
}
}
public OracleConnection unwrap()
{
try
{
// The datasource returns a weblogic.jdbc.pool.Connection
// This needs to be type casted to weblogic.jdbc.extensions.WLConnection
// Only this weblogic.jdbc.extensions.WLConnection CAN BE type casted
// to OracleConnection
return (OracleConnection) ((WLConnection) connection).getVendorConnection();
}
catch(Exception sqlException )
{
sqlException.printStackTrace ();
return null;
}
}
/* Implement all other methods by delegation to connection object */
public Connection _getPC()
{
return connection._getPC();
}
public void archive(int i, int j, String s)
throws SQLException
{
connection.archive(i, j, s);
}
public void assertComplete()
throws SQLException
{
connection.assertComplete();
}
public void clearWarnings()
throws SQLException
{
connection.clearWarnings();
}
public void close()
throws SQLException
{
connection.close();
}
public void commit()
throws SQLException
{
connection.commit();
}
public Statement createStatement()
throws SQLException
{
return connection.createStatement();
}
public Statement createStatement(int i, int j)
throws SQLException
{
return connection.createStatement(i, j);
}
public boolean getAutoClose()
throws SQLException
{
return connection.getAutoClose();
}
public boolean getAutoCommit()
throws SQLException
{
return connection.getAutoCommit();
}
public CallableStatement getCallWithKey(String s)
throws SQLException
{
return connection.getCallWithKey(s);
}
public String getCatalog()
throws SQLException
{
return connection.getCatalog();
}
public boolean getCreateStatementAsRefCursor()
{
return connection.getCreateStatementAsRefCursor();
}
public int getDefaultExecuteBatch()
{
return connection.getDefaultExecuteBatch();
}
public int getDefaultRowPrefetch()
{
return connection.getDefaultRowPrefetch();
}
public Object getDescriptor(String s)
{
return connection.getDescriptor(s);
}
public boolean getExplicitCachingEnabled()
throws SQLException
{
return connection.getExplicitCachingEnabled();
}
public boolean getImplicitCachingEnabled()
throws SQLException
{
return connection.getImplicitCachingEnabled();
}
public boolean getIncludeSynonyms()
{
return connection.getIncludeSynonyms();
}
public Object getJavaObject(String s)
throws SQLException
{
return connection.getJavaObject(s);
}
public DatabaseMetaData getMetaData()
throws SQLException
{
return connection.getMetaData();
}
public Properties getProperties()
{
return connection.getProperties();
}
public boolean getRemarksReporting()
{
return connection.getRemarksReporting();
}
public boolean getRestrictGetTables()
{
return connection.getRestrictGetTables();
}
public String getSQLType(Object obj)
throws SQLException
{
return connection.getSQLType(obj);
}
public String getSessionTimeZone()
{
return connection.getSessionTimeZone();
}
public int getStatementCacheSize()
throws SQLException
{
return connection.getStatementCacheSize();
}
public PreparedStatement getStatementWithKey(String s)
throws SQLException
{
return connection.getStatementWithKey(s);
}
public int getStmtCacheSize()
{
return connection.getStmtCacheSize();
}
public short getStructAttrCsId()
throws SQLException
{
return connection.getStructAttrCsId();
}
public boolean getSynchronousMode()
{
return connection.getSynchronousMode();
}
public int getTransactionIsolation()
throws SQLException
{
return connection.getTransactionIsolation();
}
public Map getTypeMap()
throws SQLException
{
return connection.getTypeMap();
}
public String getUserName()
throws SQLException
{
return connection.getUserName();
}
public boolean getUsingXAFlag()
{
return connection.getUsingXAFlag();
}
public SQLWarning getWarnings()
throws SQLException
{
return connection.getWarnings();
}
public boolean getXAErrorFlag()
{
return connection.getXAErrorFlag();
}
public boolean isClosed()
throws SQLException
{
return connection.isClosed();
}
public boolean isLogicalConnection()
{
return connection.isLogicalConnection();
}
public boolean isReadOnly()
throws SQLException
{
return connection.isReadOnly();
}
public String nativeSQL(String s)
throws SQLException
{
return connection.nativeSQL(s);
}
public Object openJoltConnection(String s, short word0, short word1)
{
return connection.openJoltConnection(s, word0, word1);
}
public void oracleReleaseSavepoint(OracleSavepoint oraclesavepoint)
throws SQLException
{
connection.oracleReleaseSavepoint(oraclesavepoint);
}
public void oracleRollback(OracleSavepoint oraclesavepoint)
throws SQLException
{
connection.oracleRollback(oraclesavepoint);
}
public OracleSavepoint oracleSetSavepoint()
throws SQLException
{
return connection.oracleSetSavepoint();
}
public OracleSavepoint oracleSetSavepoint(String s)
throws SQLException
{
return connection.oracleSetSavepoint(s);
}
public int pingDatabase(int i)
throws SQLException
{
return connection.pingDatabase(i);
}
public CallableStatement prepareCall(String s)
throws SQLException
{
return connection.prepareCall(s);
}
public CallableStatement prepareCall(String s, int i, int j)
throws SQLException
{
return connection.prepareCall(s, i, j);
}
public CallableStatement prepareCallWithKey(String s)
throws SQLException
{
return connection.prepareCallWithKey(s);
}
public PreparedStatement prepareStatement(String s)
throws SQLException
{
return connection.prepareStatement(s);
}
public PreparedStatement prepareStatement(String s, int i, int j)
throws SQLException
{
return connection.prepareStatement(s, i, j);
}
public PreparedStatement prepareStatementWithKey(String s)
throws SQLException
{
return connection.prepareStatementWithKey(s);
}
public void purgeExplicitCache()
throws SQLException
{
connection.purgeExplicitCache();
}
public void purgeImplicitCache()
throws SQLException
{
connection.purgeImplicitCache();
}
public void putDescriptor(String s, Object obj)
throws SQLException
{
connection.putDescriptor(s, obj);
}
public void registerApiDescription(String s, short word0, short word1, String
s1)
{
connection.registerApiDescription(s, word0, word1, s1);
}
public void registerSQLType(String s, Class class1)
throws SQLException
{
connection.registerSQLType(s, class1);
}
public void registerSQLType(String s, String s1)
throws SQLException
{
connection.registerSQLType(s, s1);
}
public void registerTAFCallback(OracleOCIFailover oracleocifailover, Object
obj)
throws SQLException
{
connection.registerTAFCallback(oracleocifailover, obj);
}
public void rollback()
throws SQLException
{
connection.rollback();
}
public void setAutoClose(boolean flag)
throws SQLException
{
connection.setAutoClose(flag);
}
public void setAutoCommit(boolean flag)
throws SQLException
{
connection.setAutoCommit(flag);
}
public void setCatalog(String s)
throws SQLException
{
connection.setCatalog(s);
}
public void setCreateStatementAsRefCursor(boolean flag)
{
connection.setCreateStatementAsRefCursor(flag);
}
public void setDefaultExecuteBatch(int i)
throws SQLException
{
connection.setDefaultExecuteBatch(i);
}
public void setDefaultRowPrefetch(int i)
throws SQLException
{
connection.setDefaultRowPrefetch(i);
}
public void setExplicitCachingEnabled(boolean flag)
throws SQLException
{
connection.setExplicitCachingEnabled(flag);
}
public void setImplicitCachingEnabled(boolean flag)
throws SQLException
{
connection.setImplicitCachingEnabled(flag);
}
public void setIncludeSynonyms(boolean flag)
{
connection.setIncludeSynonyms(flag);
}
public void setReadOnly(boolean flag)
throws SQLException
{
connection.setReadOnly(flag);
}
public void setRemarksReporting(boolean flag)
{
connection.setRemarksReporting(flag);
}
public void setRestrictGetTables(boolean flag)
{
connection.setRestrictGetTables(flag);
}
public void setSessionTimeZone(String s)
throws SQLException
{
connection.setSessionTimeZone(s);
}
public void setStatementCacheSize(int i)
throws SQLException
{
connection.setStatementCacheSize(i);
}
public void setStmtCacheSize(int i)
throws SQLException
{
connection.setStmtCacheSize(i);
}
public void setStmtCacheSize(int i, boolean flag)
throws SQLException
{
connection.setStmtCacheSize(i, flag);
}
public void setSynchronousMode(boolean flag)
{
connection.setSynchronousMode(flag);
}
public void setTransactionIsolation(int i)
throws SQLException
{
connection.setTransactionIsolation(i);
}
public void setTypeMap(Map map)
throws SQLException
{
connection.setTypeMap(map);
}
public void setUsingXAFlag(boolean flag)
{
connection.setUsingXAFlag(flag);
}
public void setWrapper(OracleConnection oracleconnection)
{
connection.setWrapper(oracleconnection);
}
public void setXAErrorFlag(boolean flag)
{
connection.setXAErrorFlag(flag);
}
public void shutdown(int i)
throws SQLException
{
connection.shutdown(i);
}
public void startup(String s, int i)
throws SQLException
{
connection.startup(s, i);
}
}
******************************************
Util class to get Wrapped Connections from
datasource
******************************************
package ejbTesting;
// j2ee imports
import javax.naming.InitialContext;
import javax.sql.DataSource;
// sql imports
import java.sql.Connection;
// imports from Oracle Driver Classes
import oracle.jdbc.OracleConnection;
/*
* Wrapper class for the DataSource Connection from Weblogic pool
*
*/
public class DataSourceConnectionWrapper
{
// datasource variable
private static transient DataSource datasource = null;
private static String dbName = "jdbc/workbench";
/*
* Method that returns the database connection
*
*/
public static Connection getConnection()
{
try
{
// initialsing the datasource object
initialiseDataSource ();
// Getting a connection from the datasource
Connection con = datasource.getConnection( );
// wrapping it custom wrapper class and
// returning the connection object
return new WeblogicConnectionWrapper((OracleConnection)con);
}
catch(Exception exception )
{
exception.printStackTrace();
return null;
}
}
private static void initialiseDataSource( ) throws Exception
{
if ( datasource == null )
{
try
{
InitialContext ic = new InitialContext( );
datasource = (DataSource) ic.lookup( dbName );
}
catch (Exception ne )
{
throw new Exception( "NamingException while looking up DataSource with
JNDI name" +
dbName + ": \n" + ne.getMessage( ) );
}
}
}
}
******************************************
Exception Stack Trace
The line 46 in DataSourceConnectionWrapper
corresponds to
return new WeblogicConnectionWrapper((OracleConnection)con);
Which I feel is logical as the connection which we get from Weblogic
datasource cannot be type casted to OracleConnection
******************************************
java.lang.ClassCastException: weblogic.jdbc.pool.Connection
at ejbTesting.DataSourceConnectionWrapper.getConnection(DataSourceConnectionWrapper.java:46)
Eugene Voytitsky
At first, sorry to All that I didn't specify in my original post
that I tested my solution only under WLS 8.1 SP1 and
I have no possibility to test it under other WLS versions.
So I don't know whether it will/should work under WLS 7.x or 6.x or not?
Under WLS 8.1 SP1 it works.
Secondly, Ram my solution will work only in cases when casting
to OracleConnection class (not interface!) performed in depth
of Oracle JDBC driver. If you should `manually` cast to
oracle.jdbc.driver.OracleConnection class in your code then
my solution won't help you -- no one wrapper-class can simulate
that it's a oracle.jdbc.driver.OracleConnection class!
> I have tried the solution suggested by You, but it did not work.
> It still throws ClassCastException.
> ******************************************
> The line 46 in DataSourceConnectionWrapper
> corresponds to
>
> return new WeblogicConnectionWrapper((OracleConnection)con);
>
> Which I feel is logical as the connection which we get from Weblogic
> datasource cannot be type casted to OracleConnection
Under WLS 8.1 SP1 Weblogic pooled connection dynamically
implements all public interfaces of physical JDBC connection object,
so this casting is valid under 8.1 SP1.
I don't know whether it's valid for WLS 7.0.
>
> ******************************************
>
> java.lang.ClassCastException: weblogic.jdbc.pool.Connection
> at ejbTesting.DataSourceConnectionWrapper.getConnection(DataSourceConnectionWrapper.java:46)
I reviewed your code. As for me it's ok.
It seems that Weblogic pooled connection doesn't implement
oracle.jdbc.OracleConnection interface under WLS 7.0.
But I'm not sure.
BEA specialist should clarify this.
Nicolas DUMINIL
I'm using WebLogic Server 7.0 SP4 and looking in the Release Notes I understodd
that this is incorporated in the release. I tried Eugene's solution but since
I couldn't set to false RemoveInfectedConnectionsEnabled (it doesn't exist in
version 7) it didn't work of course. What can I do since using the 8.x version
is not an option for my current project ?
Kind regards,
Nicolas DUMINIL
Joseph Weinstein
Nicolas DUMINIL wrote:
> I'm using WebLogic Server 7.0 SP4 and looking in the Release Notes I understodd
> that this is incorporated in the release. I tried Eugene's solution but since
> I couldn't set to false RemoveInfectedConnectionsEnabled (it doesn't exist in
> version 7) it didn't work of course. What can I do since using the 8.x version
> is not an option for my current project ?
>
> Kind regards,
>
> Nicolas DUMINIL
Hi. I'm not sure what doesn't work. You should be able to get the direct reference
to the driver connection. The option to not replace the connection afterwards is
not user-configurable in 7.0, but the code should work.
Nicolas DUMINIL
Many thanks for your answer and for your help. What doesn't work is that it raises
ClassCastException when trying to access the datasource via the connection created
as per Eugene's solution. I'm happy that you let me know that "it should work",
I'm less happy that it doesn't.
Kind regards,
Nicolas DUMINIL
Joseph Weinstein <big...@bea.com> wrote:
>
>
Joseph Weinstein
Nicolas DUMINIL wrote:
> Many thanks for your answer and for your help. What doesn't work is that it raises
> ClassCastException when trying to access the datasource via the connection created
> as per Eugene's solution. I'm happy that you let me know that "it should work",
> I'm less happy that it doesn't.
I'm less happy that I'm not rich either ;-)
Show me the code and classcast exception (full trace)
thanks
joe
Nicolas DUMINIL
Thank you Joe for your help. I just posted another message giving more details
and including also the source code. This source code, which is nothing than an
Oracle sample, works when using as such, ie with a DriverManager connection. When
using with WebLogic pool connection it gives SerialException. The code which doesn't
work is exactly the same, but the connection is obtained either with a DataSource.getConnection()
or with a getVendorConnection as explained by Eugene. The reason I posted this
second topic is that now I'm less sure that my problem has anything to do with
the Oracle extensions one. It doesn't seem to be an Oracle extension (since it
is an SQL-99 standard) and the exception is not ClassCastExcep^tion but SerialException.
I'd be very gratefull if someone could help me solving that. Using the SQLData
interface is a very elegant solution but it would be even better if it worked.
Joseph Weinstein
> Thank you Joe for your help. I just posted another message giving more details
> and including also the source code. This source code, which is nothing than an
> Oracle sample, works when using as such, ie with a DriverManager connection.
I'm looking at the code, but I do want to se the exact classcast exception.
I do notice this example is bad JDBC. For instance it calls executeQuery()
(which by spec must return a result set) for an update. It then calls commit()
on a conneciton that is still in the default autoCommit(true) mode.
Then, it's not standard JDBC. It casts a resultset to an OracleResultSet.
this is probably where your classcast problem occurs. I don't see how you
would have tried the getVendorConnection() option, but here's how to do it
with much better, safer code:
public static void main(String args []) throws Exception
{
Connection conn = null; // always define connection as method variable
try {
// do all jdbc for the method in one try block
// establish connection inside try block
conn = myDataSource.getConnection(); ... // get connection from weblogic pool
OracleConnection oconn =
((weblogic.jdbc.extensions.WLConnection))conn).getVendorConnection();
Map map = oconn.getTypeMap();
map.put("EMPLOYEE", Class.forName("com.duminil.jdbc.EmployeeObj"));
map.put("ADDRESS", Class.forName("com.duminil.jdbc.Address"));
Statement stmt = oconn.createStatement();
stmt.executeUpdate("INSERT INTO EMPLOYEE_TABLE VALUES (EMPLOYEE('Susan Smith', 123,
ADDRESS('Allee des Sapins', 26)))");
EmployeeObj e = new EmployeeObj("EMPLOYEE", "Vasile Explozie", 456,
new Address ("ADDRESS", "rue La Bruyere", 27));
PreparedStatement pstmt
= oconn.prepareStatement ("INSERT INTO employee_table VALUES (?)");
pstmt.setObject(1, e, OracleTypes.STRUCT);
pstmt.executeUpdate();
pstmt.close();
System.out.println ("inserts done");
OracleResultSet rs = (OracleResultSet)
stmt.executeQuery("SELECT * FROM employee_table");
while(rs.next())
{
EmployeeObj ee = (EmployeeObj) rs.getObject(1);
System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo +
"Street: " + ee.address.street +
"No: " + ee.address.no);
}
rs.close();
stmt.close();
conn.close();
conn = null;
// NOTE THAT OCONN IS *NOT* CLOSED. IT SHOULD BE SIMPLY NULLIFIED
// AND NEVER PASSED OUT OF THE METHOD.
oconn = null;
}
catch (Exception e)
{
// do whatever you need to do
}
finally
{
// make sure pooled connection is closed regardless of any exit path.
if (conn != null) try {conn.close();} catch (Exception ignore){}
Joseph Weinstein
Nicolas DUMINIL wrote:
> Joe,
>
> Thanks again for your help but, as said in my previous post ... it doesn't work.
> The stack trace is
>
> java.lang.ClassCastException: weblogic.jdbc.rmi.SerialConnection
Wait a minute... Is this code running in a serverside class? If this code is running
externally to weblogic, then it will never work using our rmi proxy for pooled
connections. The actual oracle connection in that case, is in the weblogic JVM,
and is not transferable over the wire to an external application. As such, an
external application using our rmi proxy for that connection will never be able
to use any oracle classes that require direct access to the specific oracle
class.
> I mention that this is an Oracle example, extracted from the Oracle documentation.
> It works correctly while used with a DriverManager connection. I only modified
> it in order to:
> i) use a WebLogic connection pool
> ii) to obtain a vendor connection
Understood. I didn't claim you wrote the bad code. I understand it is oracle's
issue with sloppy code. As an example of their flakeyness, try altering the
example to obtain the 'result set' from the executeQuery() call that is doing
an update. Try processing that result set.
> If you think it works, please try to run it (you need a datasouece which name
> is SsnDS in this case).
No, now I know why it doesn't. getVendorConnection() can only work
in the JVM where the vendor connection actually is.
Joe
> ----------------------------------------------------------------------------------------------------
> Name: SQLDataExample.zip
> SQLDataExample.zip Type: Zip Compressed Data (application/x-zip-compressed)
> Encoding: base64
Dirk Gabler
I just run into the same problem using BEA 8.1 SP4.
Is there any news about it or is the workaround still the way to go ?
Cheers,
Dirk