How do I get an OraclePreparedStatement from a DataSource?

[Former Member]

I'm trying to parse XML data types from an Oracle 10g database. The Oracle recommended way of doing this is as follows:

Connection conn = ....
OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareStatement( "..." );
OracleResultSet rs = (OracleResultSet)stmt.execute();
while( rs.next() ) {
	OPAQUE opaque = rs.getOPAQUE(1);
	XMLType xml = opaque.createXML( opaque );
	...
}

and then you go from there. This all works great when I manually create the connection using the non-j2ee method of directly connecting to the database using the jdbc:oracle:thin driver. However, when I try to allocate a connection using the J2EE defined data source, I can't do the above since the classes returned to me are SAP types, and don't take kindly to being cast to Oracle types.

Is there a way to do this? I've done some digging, but I haven't found the end of the tunnel yet I can always connect directly to the database, but I would rather use the data source management service of the J2EE engine.

Thanks!

[Former Member]

Hi Ken,

Why don't you specify the same parameter in the connection along with the Oracle driver in the J2EE connection pool. I hope you are doing that.

In that case J2ee engine will manage the connections but the connections that you will get will be created from the oracle driver and hence now you will have oraclepreparedstatements.

Hope this helps.

Ankur

[Former Member]

Well, I do. Here's how the data source is defined in the VA:

Driver: oracle.jdbc.OracleDriver

Url: jdbc:oracle:thin:@cis800:1526:xxxx

The returned datasource and/or connection is <b>not</b> an Oracle datatype; rather, it's a SAP wrapper class, and therein lies the issue.

[Former Member]

For example, the datasource is of type

com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl@f0bca

and the connection is of type:

com.sap.engine.services.dbpool.cci.ConnectionHandle@17efa91

[detlev_beutner]

Hi Ken,

but your problem is not the datasource object or the connection, but the PreparedStatement, isn't it?! So the question is if that casting works.

In addition, have you set the SQL to "vendor"?

Hope it helps

Detlev

[Former Member]

Hi Ken,

Just do a getPhysicalConnection() from that connectionHandle object that you get. This will give you a Oracle connection, and then use this to get the statement, so now you will not have wrappers. Hope that Helps.

Message was edited by: Ankur Jain

[Former Member]

Yup, it's Vendor, and casting does not work.

[Former Member]

Did you try doing a getPhysicalConnection() and getting a prepared statement from the returned connection. You should be able to typecast it to OraclePreparedStatement.

I wonder if you even tried that.

[Former Member]

I did try it, and I got a cast exception....read on:

I've added the SAP-J2E/dbpool DC to my project, and tried the following code:

import oracle.jdbc.OracleResultSet;
import oracle.jdbc.driver.OraclePreparedStatement;
import com.sap.engine.services.dbpool.cci.ConnectionHandle;
...
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/XILOG");
System.err.println("Datasource is " + ds);
ConnectionHandle handle = (ConnectionHandle)ds.getConnection();
System.err.println("Connection Handle is " + handle);
Connection conn = handle.getPhysicalConnection();
if (Class.forName("oracle.jdbc.driver.OracleConnection").isInstance(conn)) {
	System.err.println("INSTANCE OF ORACLE DERIVER");
}
System.err.println("Connection is " + conn);
PreparedStatement stmt = conn.prepareStatement("select 1 from dual");
System.err.println("Prepared Statement is " + stmt);

// !!! causes exception below !!!
OraclePreparedStatement ostmt = (OraclePreparedStatement)stmt;    

System.err.println("Oracle Prepared Statement is " + ostmt);

I get this exception, and the messages displayed afterward:

Date , Time , Message , Severity , Category , Location , Application , User

01/30/2006 , 8:56:58:967 ,  
[EXCEPTION]
 com.sap.engine.services.ejb.exceptions.BaseEJBException: Exception in method sendKey.
	at ca.example.dxi.ejb.TestServiceEJBLocalLocalObjectImpl0.sendKey(TestServiceEJBLocalLocalObjectImpl0.java:119)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at com.sap.engine.services.webservices.runtime.EJBImplementationContainer.invokeMethod(EJBImplementationContainer.java:126)
	at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:157)
	at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:79)
	at com.sap.engine.services.webservices.runtime.servlet.ServletDispatcherImpl.doPost(ServletDispatcherImpl.java:92)
	at SoapServlet.doPost(SoapServlet.java:51)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:390)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:264)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:347)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:325)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:887)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:241)
	at com.sap.engine.services.httpserver.server.Client.handle(Client.java:92)
	at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:148)
	at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)
	at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)
	at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)
	at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)
Caused by: java.lang.ClassCastException
	at ca.example.dxi.ejb.TestServiceEJBBean.sendKey(TestServiceEJBBean.java:68)
	at ca.example.dxi.ejb.TestServiceEJBLocalLocalObjectImpl0.sendKey(TestServiceEJBLocalLocalObjectImpl0.java:103)
	... 25 more
++++++++++++++++++++++++++++++++++++++++
java.lang.ClassCastException
+++++++++++++++++++++++++++++++++++++++++
	at ca.example.dxi.ejb.TestServiceEJBBean.sendKey(TestServiceEJBBean.java:68)
	at ca.example.dxi.ejb.TestServiceEJBLocalLocalObjectImpl0.sendKey(TestServiceEJBLocalLocalObjectImpl0.java:103)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:324)
	at com.sap.engine.services.webservices.runtime.EJBImplementationContainer.invokeMethod(EJBImplementationContainer.java:126)
	at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:157)
	at com.sap.engine.services.webservices.runtime.RuntimeProcessor.process(RuntimeProcessor.java:79)
	at com.sap.engine.services.webservices.runtime.servlet.ServletDispatcherImpl.doPost(ServletDispatcherImpl.java:92)
	at SoapServlet.doPost(SoapServlet.java:51)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.runServlet(HttpHandlerImpl.java:390)
	at com.sap.engine.services.servlets_jsp.server.HttpHandlerImpl.handleRequest(HttpHandlerImpl.java:264)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:347)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.startServlet(RequestAnalizer.java:325)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.invokeWebContainer(RequestAnalizer.java:887)
	at com.sap.engine.services.httpserver.server.RequestAnalizer.handle(RequestAnalizer.java:241)
	at com.sap.engine.services.httpserver.server.Client.handle(Client.java:92)
	at com.sap.engine.services.httpserver.server.Processor.request(Processor.java:148)
	at com.sap.engine.core.service630.context.cluster.session.ApplicationSessionMessageListener.process(ApplicationSessionMessageListener.java:33)
	at com.sap.engine.core.cluster.impl6.session.MessageRunner.run(MessageRunner.java:41)
	at com.sap.engine.core.thread.impl3.ActionObject.run(ActionObject.java:37)
	at java.security.AccessController.doPrivileged(Native Method)
	at com.sap.engine.core.thread.impl3.SingleThread.execute(SingleThread.java:100)
	at com.sap.engine.core.thread.impl3.SingleThread.run(SingleThread.java:170)

 , Error ,  , com.sap.engine.services.ejb , example.ca/dxi~ear~testsvc , Guest
01/30/2006 , 8:56:58:951 , Prepared Statement is oracle.jdbc.driver.OraclePreparedStatement@cff2b9 , Error ,  , System.err , example.ca/dxi~ear~testsvc , Guest
01/30/2006 , 8:56:58:951 , Connection is oracle.jdbc.driver.OracleConnection@58d06b , Error ,  , System.err , example.ca/dxi~ear~testsvc , Guest
01/30/2006 , 8:56:58:951 , Connection Handle is com.sap.engine.services.dbpool.cci.ConnectionHandle@8f198d , Error ,  , System.err , example.ca/dxi~ear~testsvc , Guest
01/30/2006 , 8:56:58:951 , Datasource is com.sap.engine.services.dbpool.cci.ConnectionFactoryImpl@761e7d , Error ,  , System.err , example.ca/dxi~ear~testsvc , Guest

I'm not sure what's going on, since the returned type is an OraclePreparedStatement. Also, you'll notice that the 'instanceof' check fails. I'm wondering if I'm not having a class loader issue, where the classes used by the datasource application (which come from classes12.zip which I uploaded) are clashing with the classes pointed to via a hard runtime reference to a j2ee library project that also contains classes12.zip and two other oracle-specific jar files used for dealing with xml and the XMLTYPE column type.

I've tried a few variations, but every time I try to cast the returned object to the corresponding Oracle type, I get a casting exception.

Suggestions?

[Former Member]

I think I've got this figured out. The database source was using a different classes12.zip (same physical file, located in multiple places) than the one that was stored in a dependant DC. I removed classes12.zip from the DC, and instead placed a reference in the ear project to the one used by the datasource.

I was then able to cast the returned PreparedStatement to an OraclePreparedStatement, as well as cast a ResultSet to OracleResultSet. I now have access to the backend Oracle types.

I have some further testing to do - I'll report back here what I find....

[Former Member]

Well, I ran into some other issues with class loading and the Oracle libraries, but I finally got everything resolved. It all works now.

What it came down to was this:

Adding the 'dbpool' DC to my project to get access to the ConnectionHandle object, and adding all the required Oracle libraries to the database driver definition (including xdb.jar and xmlparserv2.jar), and only using a compilation DC to access classes12.jar, and the two other jar files. Setup a runtime reference between the ear and the Oracle driver definition, and I was off to the races.

Thanks for all the suggestions.

[Former Member]

Hello Ken,

I am having kind of the same issue you have, I get a ClassCastException with this

OracleDataSource ods = (OracleDataSource)(new InitialContext()).lookup("jdbc/<DATASOURCENAME>");

I created an abstract portal componente in a portal application, I added the dpool.jar and copy classes12.jar in the lib folder of the project, added the including xdb.jar and xmlparserv2.jar to the datasource in VA but I keep getting the ClassCastException. Any suggestions?

Regards,

Juan