java.lang.ClassCastException when invoking jdbc stored procedure call

[Shane Lee]

java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcCallableStatement incompatible with oracle.jdbc.OracleCallableStatement

Websphere 6.1.0.17 ND
JEE1.4
Oracle 10G

Setup jdbc provider and datasource for oracle correctly, tested connection and its working fine on admin console.

In my application, lookup datasource to get connection, then have a call to the database connection on a stored function in oracle. It returns a REFCURSOR so my java line to return the result set is:
result = ((OracleCallableStatement)cs).getCursor(1);

This gives me the exception above.

i have been searching for solutions on this problem for nearly a day now and found one:

Error on datasource:
You can't count on the implmentation of the CallableStatement interface being the Oracle version, even though your underlying database is Oracle. When you use a WebSphere Data Source, neither the connection nor the objects it creates will cast to Oracle objects.

Because of these issues, IBM has provided a way to get at the "native" (in this case, Oracle) DataBase connection, and consequently at the native objects it creates. The help is in the form of the com.ibm.ws.rsadapter.jdbc.WSJdbcUtil class, which is in the WebSphere class path. Just use it's getNativeConnection method to get the underlying Oracle connection, and then the CallableStatement you produce from it will cast correctly. Example:


connection = (Connection) WSJdbcUtil.getNativeConnection((WSJdbcConnection)dataSource.getConnection());
OracleCallableStatement ocstmt = (OracleCallableStatement) connection.prepareCall(sql);

i do not want to have WAS libraries in my project though.

This jdbc call works fine locally, only a problem when i deploy my application on websphere.

is this a websphere configuration issue i am missing where i can change the implementation name for my jdbc provider or can i not use oracle calls whatsoever?

Thanks,
Shane.

[Shane Lee]

Removed any oracle jbc callable statments and used java jdbc api classes instead, could not see any other way around it!

[Anu Sathuluri]

Shane,

How did you change your code to use jdbc APIs alone ?

What is the JDBC api equivalent of the code below ?

ResultSet crs = ((OracleCallableStatement) cstmt).getCursor(4);

thanks.

[Shane Lee]

Hi Unu ,
Ha thats a while back since i worked on that! Looked at my existing code that i have on my laptop and this is a code snippet of how i called a stored procedure.

private static final String mappingTemplateItem = "begin ? := iGET_TEMPLATE_ITEM(?,?); end;";
	 private CallableStatement cs = null;
	 private ResultSet result = null;

cs = (CallableStatement)conn.prepareCall(mappingTemplateItem);
			// register the type of the out param - an Oracle specific type
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			cs.setString(2, mappingTemplate.getMappingTemplateCode());
			cs.setString(3, templateType);
			cs.execute();
			//result = ((OracleCallableStatement)cs).getCursor(1);
			result = (ResultSet) cs.getObject(1);

As you can see from the above comment, i use java.sql.CallableStatement now instead of OracleCallableStatement.

[Anu Sathuluri]

Thanks Shane. Change worked! You saved me hrs of investigation on the topic.

For other readers, i now used
ResultSet crs = (ResultSet) cstmt.getObject(4); // instead of ResultSet crs = ((OracleCallableStatement) cstmt).getCursor(4);

[Shane Lee]

No worries Anu glad to be of help

[Darrell Rials]

Shane Lee wrote:Hi Unu ,
Ha thats a while back since i worked on that! Looked at my existing code that i have on my laptop and this is a code snippet of how i called a stored procedure.

private static final String mappingTemplateItem = "begin ? := iGET_TEMPLATE_ITEM(?,?); end;";
	 private CallableStatement cs = null;
	 private ResultSet result = null;

cs = (CallableStatement)conn.prepareCall(mappingTemplateItem);
			// register the type of the out param - an Oracle specific type
			cs.registerOutParameter(1, OracleTypes.CURSOR);
			cs.setString(2, mappingTemplate.getMappingTemplateCode());
			cs.setString(3, templateType);
			cs.execute();
			//result = ((OracleCallableStatement)cs).getCursor(1);
			result = (ResultSet) cs.getObject(1);

As you can see from the above comment, i use java.sql.CallableStatement now instead of OracleCallableStatement.

Brilliant! Not only can I avoid using yet another Oracle-specific type, the code is cleaner and easier to understand. I was looking at some rather nasty changes to setting up the connection pool, which I had no assurance would work. Now if we could only get Oracle to (a) change their documentation on how to use a Cursor, and (b) allow returning pure result sets instead of having to return a cursor object.

[Marcos Maia]

Hi,

Just tried the more generic approach using:

result = (ResultSet) cs.getObject(1); 

but does not seem to have worked for me.I will still run some more tests and let you know about the results.


It worked for me this more generic approach in the end. I was having problems with duplicated entry in the classpath but it is fixed now and it is working with the generic approach I have mentioned here.

regards.