12 Replies Latest reply on Dec 2, 2010 9:33 AM by David Jimenez

    Oracle User Type Mappings

    Bob Frapples Newbie

      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

       

       

        • 1. Re: Oracle User Type Mappings
          Bob Frapples Newbie

          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.

          • 2. Re: Oracle User Type Mappings
            jaikiran pai Master

            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.

            • 3. Re: Oracle User Type Mappings
              Bob Frapples Newbie

              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.

              • 4. Re: Oracle User Type Mappings
                jaikiran pai Master

                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);
                
                
                • 5. Re: Oracle User Type Mappings
                  Bob Frapples Newbie

                  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

                   

                  • 6. Re: Oracle User Type Mappings
                    jaikiran pai Master

                    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);
                    • 7. Re: Oracle User Type Mappings
                      Bob Frapples Newbie

                      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

                       

                       

                      • 8. Re: Oracle User Type Mappings
                        jaikiran pai Master

                        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?

                        • 9. Re: Oracle User Type Mappings
                          Bob Frapples Newbie

                          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.

                          • 10. Re: Oracle User Type Mappings
                            David Jimenez Newbie

                            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

                            • 12. Re: Oracle User Type Mappings
                              David Jimenez Newbie

                              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