Topic
  • 4 replies
  • Latest Post - ‏2013-04-05T14:32:42Z by SystemAdmin
Tom_McManus
Tom_McManus
8 Posts

Pinned topic Liberty and Stored Procedures

‏2013-04-04T17:10:37Z |
Hi We are using liberty profile for our development and plan for our product. We are unable to get the native vendor specific OracleConnection. Earlier we used to use native connection in the earlier versions of Websphere application server. I provide below the configuration details for Oracle database in server.xml.

###
WAS V7 we did the following:
Connection cf = dtpPODS.getConnection(); //logical connection
Connection vendorCn=com.ibm.websphere.rsadapter.WSCallHelper.getNativeConnection(cf);
dbCnMap.put(vendorCn, cf) ;
return vendorCn;
####

server.xml

<jdbcDriver id="OracleRef" libraryRef="OracleLib" javax.sql.DataSource="oracle.jdbc.pool.OracleDataSource" />

<dataSource jndiName="OracleDS" type="javax.sql.DataSource" jdbcDriverRef="OracleRef">
<connectionManager
maxPoolSize="20"
minPoolSize="5"
connectionTimeout="10s"
agedTimeout="30m"/>
<properties
user="pgintegration"
password="pgintegration"
portNumber="1521"
URL="jdbc:oracle:thin:@idealoradb01:1521:orcl"
databaseName="orcl"
serverName="idealoradb01" />
</dataSource>

We are able to get the wrapped connection ie "com.ibm.ws.rsadapter.jdbc.WSJdbcConnection@1f5f379" but whenever we are trying to convert into OracleConnection to for input table type for Oracle procedures and function, we are getting classCastException. Still we tried to use Spring jdbc api to get native connection, we are getting the following exception. Please provide suggestion.

err java.lang.IllegalStateException: Could not initialize WebSphereNativeJdbcExtractor because WebSphere API classes are not available: java.la
ng.ClassNotFoundException: com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
err at org.springframework.jdbc.support.nativejdbc.WebSphereNativeJdbcExtractor.<init>(WebSphereNativeJdbcExtractor.java:67)
err at com.iit.rnd.web.jndi.server.WebPageServlet.doPost(Unknown Source)
err at com.iit.rnd.web.jndi.server.WebPageServlet.doGet(Unknown Source)
err at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
err at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
err at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1221)
err at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:757)
err at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:440)
err at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1040)
err at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:4173)
err at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:302)
err at com.ibm.ws.webcontainer.osgi.DynamicVirtualHost.handleRequest(DynamicVirtualHost.java:296)
err at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1006)
err at com.ibm.ws.webcontainer.osgi.DynamicVirtualHost$1.run(DynamicVirtualHost.java:253)
err at com.ibm.ws.http.dispatcher.internal.channel.HttpDispatcherLink$TaskWrapper.run(HttpDispatcherLink.java:457)
err at com.ibm.ws.threading.internal.Worker.executeWork(Worker.java:398)
err at com.ibm.ws.threading.internal.Worker.run(Worker.java:380)
err at java.lang.Thread.run(Thread.java:662)

Thanks,
Tom
  • Jacek_Laskowski
    Jacek_Laskowski
    133 Posts

    Re: Liberty and Stored Procedures

    ‏2013-04-05T07:48:20Z  
    Hi,

    Could it be related to Classloading on startup and apiTypeVisibility in library in server.xml? I've seen your response to the thread so you may have given it a try, but in case you didn't, I'd go for it.

    Jacek
    Japila :: verba docent, exempla trahunt
  • Tom_McManus
    Tom_McManus
    8 Posts

    Re: Liberty and Stored Procedures

    ‏2013-04-05T12:35:44Z  
    Thanks. I crossed up my post information. This was a mix of two very close issues surrounding native connections.

    WebSphere 7.0 we need to perform the following in the code which we are also doing for 8.5

    Connection cf = dtpPODS.getConnection(); //logical connection
    Connection vendorCn=com.ibm.websphere.rsadapter.WSCallHelper.getNativeConnection(cf);
    dbCnMap.put(vendorCn, cf) ;
    return vendorCn;

    java.lang.NoClassDefFoundError: com.ibm.websphere.rsadapter.WSCallHelper
    at com.omrsystems.dtp.DTPPostOffice.getConnectionDSWebsphere(DTPPostOffice.java:444)
    at com.omrsystems.dtp.DTPPostOffice.getConnectionFromPool(DTPPostOffice.java:332)
    at com.omrsystems.jta.classes.common.GenericPLSQLProcess.getInfo(GenericPLSQLProcess.java:113)
    at com.omrsystems.jta.classes.common.AccountType.getAccountType(AccountType.java:33)
    at com.omrsystems.jta.servlets.TAInitialization.init(TAInitialization.java:340)
    at javax.servlet.GenericServlet.init(GenericServlet.java:161)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.init(ServletWrapper.java:320)
    at com.ibm.ws.webcontainer.osgi.servlet.ServletWrapper.init(ServletWrapper.java:83)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.loadOnStartupCheck(ServletWrapper.java:1366)
    at com.ibm.ws.webcontainer.webapp.WebApp.doLoadOnStartupActions(WebApp.java:878)
    at com.ibm.ws.webcontainer.webapp.WebApp.commonInitializationFinally(WebApp.java:846)
    at com.ibm.ws.webcontainer.webapp.WebApp.initialize(WebApp.java:758)
    at com.ibm.ws.webcontainer.webapp.WebApp.initialize(WebApp.java:5779)
    ...
    Caused by: java.lang.ClassNotFoundException: com.ibm.websphere.rsadapter.WSCallHelper
    at com.ibm.ws.classloading.internal.AppClassLoader.findClassCommonLibraryClassLoaders(AppClassLoader.java:409)
    at com.ibm.ws.classloading.internal.AppClassLoader.findClass(AppClassLoader.java:253)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:653)
    at com.ibm.ws.classloading.internal.AppClassLoader.loadClass(AppClassLoader.java:385)
    ... 69 more

    Server.xml: Library and Datasource entry:

    <library id="OracleLib">
    <fileset dir="/oracle/product/11.2.0.3/client_64/jdbc/lib" includes="ojdbc6.jar"/>
    </library>

    <jdbcDriver id="OracleRef" libraryRef="OracleLib" javax.sql.DataSource="oracle.jdbc.pool.OracleDataSource"/>

    <dataSource id="TTDATASOURCE" name="TTDATASOURCE" jndiName="TTDATASOURCE" type="javax.sql.DataSource" jdbcDriverRef="OracleRef" >
    <properties.oracle URL="jdbc:oracle:thin:@ibm:1521:s09db1" user="scott" password="xxxxxx" />
    <connectionManager maxActive="8" maxIdle="4" maxWait="-1" connectionCachingEnabled="true"/>
    </dataSource>
    Messages.log
    4/4/13 11:53:06:530 EDT 00000016 com.ibm.ejs.j2c.CMConfig A J2CA8000I: The connectionManager dataSource-TTDATASOURCE/connectionManagerRef-0 is available.
    4/4/13 11:53:06:532 EDT 00000016 com.ibm.ejs.j2c.CMConfig A J2CA8000I: The connectionManager dataSource-TTDATASOURCE/connectionManager-0 is available.
    4/4/13 11:53:06:580 EDT 00000016 com.ibm.ws.jdbc.internal.DataSourceService A J2CA8004I: The dataSource TTDATASOURCE is available as TTDATASOURCE.
    4/4/13 11:53:06:641 EDT 00000016 com.ibm.ws.jdbc.internal.JDBCDriverService A J2CA8000I: The jdbcDriver OracleRef is available.
    4/4/13 11:53:06:704 EDT 00000016 com.ibm.ws.app.manager.internal.monitor.DropinMonitor A CWWKZ0058I: Monitoring dropins for applications.
    ..

    4/4/13 11:53:44:953 EDT 00000017 SystemOut O Datasource Initialized in Postoffice
    4/4/13 11:53:45:326 EDT 00000017 com.ibm.ws.rsadapter.DSConfig W DSRA8020E: Warning: The property 'name' does not exist on the DataSource class TTDATASOURCE.
    4/4/13 11:53:45:439 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: connectionCachingEnabled
    4/4/13 11:53:45:440 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxActive
    4/4/13 11:53:45:441 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxIdle
    4/4/13 11:53:45:442 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxWait
    ..
    4/4/13 11:53:46:936 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8203I: Database product name : Oracle
    4/4/13 11:53:46:937 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8204I: Database product version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    4/4/13 11:53:46:938 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8205I: JDBC driver name : Oracle JDBC driver
    4/4/13 11:53:46:938 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8206I: JDBC driver version : 11.2.0.3.0
  • njr11
    njr11
    14 Posts

    Re: Liberty and Stored Procedures

    ‏2013-04-05T14:30:46Z  
    Thanks. I crossed up my post information. This was a mix of two very close issues surrounding native connections.

    WebSphere 7.0 we need to perform the following in the code which we are also doing for 8.5

    Connection cf = dtpPODS.getConnection(); //logical connection
    Connection vendorCn=com.ibm.websphere.rsadapter.WSCallHelper.getNativeConnection(cf);
    dbCnMap.put(vendorCn, cf) ;
    return vendorCn;

    java.lang.NoClassDefFoundError: com.ibm.websphere.rsadapter.WSCallHelper
    at com.omrsystems.dtp.DTPPostOffice.getConnectionDSWebsphere(DTPPostOffice.java:444)
    at com.omrsystems.dtp.DTPPostOffice.getConnectionFromPool(DTPPostOffice.java:332)
    at com.omrsystems.jta.classes.common.GenericPLSQLProcess.getInfo(GenericPLSQLProcess.java:113)
    at com.omrsystems.jta.classes.common.AccountType.getAccountType(AccountType.java:33)
    at com.omrsystems.jta.servlets.TAInitialization.init(TAInitialization.java:340)
    at javax.servlet.GenericServlet.init(GenericServlet.java:161)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.init(ServletWrapper.java:320)
    at com.ibm.ws.webcontainer.osgi.servlet.ServletWrapper.init(ServletWrapper.java:83)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.loadOnStartupCheck(ServletWrapper.java:1366)
    at com.ibm.ws.webcontainer.webapp.WebApp.doLoadOnStartupActions(WebApp.java:878)
    at com.ibm.ws.webcontainer.webapp.WebApp.commonInitializationFinally(WebApp.java:846)
    at com.ibm.ws.webcontainer.webapp.WebApp.initialize(WebApp.java:758)
    at com.ibm.ws.webcontainer.webapp.WebApp.initialize(WebApp.java:5779)
    ...
    Caused by: java.lang.ClassNotFoundException: com.ibm.websphere.rsadapter.WSCallHelper
    at com.ibm.ws.classloading.internal.AppClassLoader.findClassCommonLibraryClassLoaders(AppClassLoader.java:409)
    at com.ibm.ws.classloading.internal.AppClassLoader.findClass(AppClassLoader.java:253)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:653)
    at com.ibm.ws.classloading.internal.AppClassLoader.loadClass(AppClassLoader.java:385)
    ... 69 more

    Server.xml: Library and Datasource entry:

    <library id="OracleLib">
    <fileset dir="/oracle/product/11.2.0.3/client_64/jdbc/lib" includes="ojdbc6.jar"/>
    </library>

    <jdbcDriver id="OracleRef" libraryRef="OracleLib" javax.sql.DataSource="oracle.jdbc.pool.OracleDataSource"/>

    <dataSource id="TTDATASOURCE" name="TTDATASOURCE" jndiName="TTDATASOURCE" type="javax.sql.DataSource" jdbcDriverRef="OracleRef" >
    <properties.oracle URL="jdbc:oracle:thin:@ibm:1521:s09db1" user="scott" password="xxxxxx" />
    <connectionManager maxActive="8" maxIdle="4" maxWait="-1" connectionCachingEnabled="true"/>
    </dataSource>
    Messages.log
    4/4/13 11:53:06:530 EDT 00000016 com.ibm.ejs.j2c.CMConfig A J2CA8000I: The connectionManager dataSource-TTDATASOURCE/connectionManagerRef-0 is available.
    4/4/13 11:53:06:532 EDT 00000016 com.ibm.ejs.j2c.CMConfig A J2CA8000I: The connectionManager dataSource-TTDATASOURCE/connectionManager-0 is available.
    4/4/13 11:53:06:580 EDT 00000016 com.ibm.ws.jdbc.internal.DataSourceService A J2CA8004I: The dataSource TTDATASOURCE is available as TTDATASOURCE.
    4/4/13 11:53:06:641 EDT 00000016 com.ibm.ws.jdbc.internal.JDBCDriverService A J2CA8000I: The jdbcDriver OracleRef is available.
    4/4/13 11:53:06:704 EDT 00000016 com.ibm.ws.app.manager.internal.monitor.DropinMonitor A CWWKZ0058I: Monitoring dropins for applications.
    ..

    4/4/13 11:53:44:953 EDT 00000017 SystemOut O Datasource Initialized in Postoffice
    4/4/13 11:53:45:326 EDT 00000017 com.ibm.ws.rsadapter.DSConfig W DSRA8020E: Warning: The property 'name' does not exist on the DataSource class TTDATASOURCE.
    4/4/13 11:53:45:439 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: connectionCachingEnabled
    4/4/13 11:53:45:440 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxActive
    4/4/13 11:53:45:441 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxIdle
    4/4/13 11:53:45:442 EDT 00000017 com.ibm.ejs.j2c.CMConfig W J2CA8010W: Unrecognized connectionManager property: maxWait
    ..
    4/4/13 11:53:46:936 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8203I: Database product name : Oracle
    4/4/13 11:53:46:937 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8204I: Database product version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    4/4/13 11:53:46:938 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8205I: JDBC driver name : Oracle JDBC driver
    4/4/13 11:53:46:938 EDT 00000017 com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper I DSRA8206I: JDBC driver version : 11.2.0.3.0
    The API "com.ibm.websphere.rsadapter.WSCallHelper" is not available in the Liberty profile. It is only available in the full profile. Accessing native connections in an unmanaged way can be very dangerous because the application server's transaction management and connection pooling are completely unaware of what is being done to the connection. The recommended way of accessing JDBC vendor-specific interfaces (in both the Liberty profile and the full profile) is to use the JDBC 4.0 spec-defined APIs for wrapper pattern, which allows the application server to at least have some visibility of what is happening to the native connection. For example,

    OracleConnection oracleConnection = con.unwrap(OracleConnection.class);

    Also, I would like to point out that several of the attributes in your configuration do not exist.
    dataSource does not have a "name" attribute.
    connectionManager does not have "maxActive", "maxIdle=", "maxWait", "connectionCachingEnabled"

    A list of configuration elements and configurable attributes can be found here:
    http://publib.boulder.ibm.com/infocenter/radhelp/v8r5/topic/com.ibm.websphere.wlp.nd.multiplatform.doc/autodita/rwlp_metatype_4ic.html
  • SystemAdmin
    SystemAdmin
    590 Posts

    Re: Liberty and Stored Procedures

    ‏2013-04-05T14:32:42Z  
    Hi,

    Use "unwrap" to get access to the native connection, instead of WSCallHelper.

    http://docs.oracle.com/javase/6/docs/api/java/sql/Wrapper.html

    • Soloman