Oracle User Type Mappings

[Bob Frapples]

I have inherited an application system where all of the business logic takes place in the database.  All of the application calls to the database interface with Functions and Stored Procedures and use Oracle User Data Types as both IN and OUT parameters.  I'm working on converting the front end to use Spring w/ Hibernate and I am extending the org.springframework.jdbc.object.StoredProcedure class to interact with the database.

If I define the datasource in the Spring configuration file I am able to call getConnection().setTypeMap(Map<String, Class<?>> on the datasource and everything works fine.

However I would like to configure the datasource in JBoss and use a JNDI lookup to get a connection from the pool.  When I do this, the .setTypeMap(Map<String, Class<?>> method no longer seems to work.  I keep getting the exception:

java.lang.ClassCastException: oracle.sql.STRUCT cannot be cast to ............

I tried editing the standardjbosscmp-jdbc.xml file and adding the mappings there but that did not work.

So how can I set the Type Map for the JBoss DataSource?

JBoss 5.1

Spring 3.0

Hibernate 3.3

JDK 6

Thanks

[Bob Frapples]

Anyone?  Anyone?  Bueller?  Bueller?

The object I am getting back from the JNDI call is a org.jboss.resource.adapter.jdbc.jdk5.WrappedconnectionJDK5 object.  It looks like it supports the setTypeMap(Map<String, Object>) method but it doesn't seem to be working.

I really need to get this problem solved so I welcome any and all helpful suggestions.

Thanks.

[jaikiran pai]

Please post the entire exception stacktrace and the relevant code.

Bob Frapples wrote:

The object I am getting back from the JNDI call is a org.jboss.resource.adapter.jdbc.jdk5.WrappedconnectionJDK5 object. 

I'm not completely sure what the issue is, but if you trying to get the real connection to the DB, then you'll first have to call getUnderlyingConnection() on that object, something like this http://community.jboss.org/message/225667#225667

By the way, the type-mapping come into picture only for EJB2.x Container Managed Persistence.

[Bob Frapples]

Here is the relevant code:

From myDS-ds.xml

{code:xml}

<datasources>

  <local-tx-datasource>

    <jndi-name>jdbc/MyDS</jndi-name>

    <use-java-context>false</use-java-context>

    <connection-url>jdbc:oracle:thin:@1.1.1.1:1521:orcl</connection-url>

    <driver-class>oracle.jdbc.OracleDriver</driver-class>

    <user-name>user</user-name>

    <password>pass</password>

    <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->

    <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name-->

    <!-- Checks the Oracle error codes and messages for fatal errors -->

    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

        <!-- sql to call when connection is created

        <new-connection-sql>some arbitrary sql</new-connection-sql>

        -->

        <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered -->

    <check-valid-connection-sql>SELECT * FROM DUAL</check-valid-connection-sql>

    <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

    <metadata>

       <type-mapping>Oracle9i</type-mapping>

    </metadata>

  </local-tx-datasource>

</datasources>

{code}

From application-context.xml

{code:xml}

<bean id="dataSource">

  <property name="jndiName" value="jdbc/MyDS" />

  <property name="expectedType" value="javax.sql.DataSource"/>

</bean>

{code}

From AppContext.java

{code}

((DataSource)ctx.getBean("dataSource")).getConnection.setTypeMap(MyMappings.TYPE_MAP);

{code}

From MyMappings.java

{code}

public

static final Map<String, Class<?>> TYPE_MAP = new

HashMap<String, Class<?>>();

static

{

TYPE_MAP

.put(User.SQLTypeName, User.class

);

{code}

Here is the stack trace:

{code}

2010-07-20 08:29:52,715 ERROR [STDERR] (http-127.0.0.1-8080-1) java.lang.ClassCastException: oracle.sql.STRUCT cannot be cast to editor.hibernate.model.User

2010-07-20 08:29:52,715 ERROR [STDERR] (http-127.0.0.1-8080-1)  at editor.hibernate.dao.function.FnGetUserByName.execute(FnGetUserByName.java:33)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at editor.service.UserService.getUser(UserService.java:55)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at editor.service.UserService.userLogin(UserService.java:66)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at editor.controller.LoginController.login(LoginController.java:42)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at java.lang.reflect.Method.invoke(Method.java:597)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:471)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:408)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:771)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:716)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:647)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:563)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:190)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:92)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.process(SecurityContextEstablishmentValve.java:126)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:70)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:158)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:330)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:829)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:598)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)

2010-07-20 08:29:52,730 ERROR [STDERR] (http-127.0.0.1-8080-1)  at java.lang.Thread.run(Thread.java:619)

{code}

Sorry for the bad formatting, I'm not used to this forum interface.  Hope it helps.

Thanks.

[jaikiran pai]

Bob Frapples wrote:

{code}

((DataSource)ctx.getBean("dataSource")).getConnection.setTypeMap(MyMappings.TYPE_MAP);

{code}

Try:

Connection c = dataSource.getConnection();
org.jboss.resource.adapter.jdbc.WrappedConnection wc = (WrappedConnection) c;
wc.getUnderlyingConnection().setTypeMap(MyMappings.TYPE_MAP);

[Bob Frapples]

I gave that a try and got this exception:

12:58:06,979 ERROR [STDERR]

java.lang.ClassCastException: $Proxy339 cannot be cast to org.jboss.resource.adapter.jdbc.WrappedConnection

[jaikiran pai]

It works fine for me. Are you sure, you used the right code? What does the following print:

     DataSource ds = (DataSource) new InitialContext().lookup("java:DefaultDS"); // replace this with your datasource deployed in JBoss
      con = ds.getConnection(); 
      System.out.println("Connection is " + con); 
      org.jboss.resource.adapter.jdbc.WrappedConnection wc = (WrappedConnection) con; 
      Connection uc = wc.getUnderlyingConnection(); 
      System.out.println("Underlying Connection is " + uc);

[Bob Frapples]

I implemented this code:

DataSource ds = (DataSource)

new InitialContext().lookup("jdbc/MyDS"); // replace this with your datasource deployed in JBoss

Connection con = ds.getConnection();

System.out.println("Connection is " + con.getClass().getName());

org.jboss.resource.adapter.jdbc.WrappedConnection wc = (WrappedConnection) con;

Connection uc = wc.getUnderlyingConnection();

System.out.println("Underlying Connection is " + uc);

And I get this error:

13:48:50,276 INFO [STDOUT] Connection is $Proxy339

13:48:50,276 ERROR [STDERR]

java.lang.ClassCastException: $Proxy339 cannot be cast to org.jboss.resource.adapter.jdbc.WrappedConnection

[jaikiran pai]

Bob Frapples wrote:

I implemented this code:

DataSource ds = (DataSource)

new InitialContext().lookup("jdbc/MyDS"); // replace this with your datasource deployed in JBoss

Looks like you are using the datasource configured through Spring beans. I'm not sure how Spring works, but it looks like it creates a proxy to the original datasource. Why not remove that spring configuration and just use the *-ds.xml?

[Bob Frapples]

I'm pretty sure:

new InitialContext().lookup("jdbc/MyDS");

gets the data source from the JNDI context as defined in JBoss in the myDS-ds.xml file.

To get the Spiring data source bean I use:

org.springframework.context.ApplicationContext.getBean("dataSource");

Ultimately I need the Spring configuration because I am defining a whole pile of StoredProcedure beans that need the dataSource as a constructor argument.

[David Jimenez]

Hi Bob,

I have to do exactly the same that you in my company, I have to access stored procedures returning oracle user data types and I'm going to use Spring as well

I didn' know that I could extend StoredProcedure class to achieve that, please could you give me a clue about how to begin subclassing it?

I suppose yo have written the execute method, haven't you?

Some help will be appreciated.

Thanks in advance

David

[Bob Frapples]

The Spring documentation is a good place to start:

http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/htmlsingle/spring-framework-reference.html#jdbc-StoredProcedure

[David Jimenez]

Thanks Bob,

I´ll do some tests and if I get the same error you are getting and I'm able to solve it I´ll let you know

Regards