I'm writing a small webservice that will persist data in a database. I'm using WSO2 Application Server 5.2.1 and Oracle Database XE 11g and developing/testing on Windows 7.
When I create a Resource in [AppServerHome]/repository/conf/tomcat/context.xml, the code works. When I instead create a Data Source under the Management Console and expose it as a JNDI Data Source, the getConnection call fails with a SQLException.
If I create a Data Service through the Management Console using the Data Source that was also created in the Management Console, the Data Service also works. So that should indicate that the Data Source is valid and working.
I have tried different variations of the jdbc url:
jdbc:oracle:thin:@localhost:1521:xe jdbc:oracle:thin:@localhost:1521/xe jdbc:oracle:thin:@localhost:1521:XE jdbc:oracle:thin:@localhost:1521/XE
I have also tried checking the 'Use Data Source Factory' option, not really understanding what it does. It also has no apparent effect.
I tried following the instructions at https://docs.wso2.com/display/AS520/Exposing+Datasources+as+JNDI+Resources but it also had no effect.
Can anyone help explain why the Data Source is not working for the webapp but the context.xml Resource is? I need to have the Data Source working so that I don't have plain text passwords in the context.xml file, and so that management of the Data Sources and passwords is done strictly through the WSO2 Management Console.
The Resource in context.xml that works is:
<Resource name="jdbc/db1" auth="Container"
type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
maxActive="100" maxIdle="30" maxWait="10000"
url="jdbc:oracle:thin:@localhost:1521:xe"
username="user" password="aPasswordGoesHere"/>
The Data Source that I create under Management Console contains the following info:
Data Source Type: RDBMS Name: MyDB Data Source Provider: default Driver: oracle.jdbc.OracleDriver URL: jdbc:oracle:thin:@localhost:1521:xe Username: user Password: aPasswordGoesHere Expose as a JNDI Data Source: Name: jdbc/db1 Use Data Source Factory: unchecked JNDI Properties: none set Data Source Configuration Parameters: (No changes made)
And a matching ResourceLink in my META-INF/context.xml
<ResourceLink name="jdbc/db1" global="jdbc/db1" type="javax.sql.DataSource" />
The code used to get the DataSource is:
Connection conn;
try
{
DataSource ds;
Context ctx;
Object obj;
ctx = new InitialContext();
obj = ctx.lookup(jndiName);
ds = (DataSource) PortableRemoteObject.narrow(obj, DataSource.class);
conn = ds.getConnection();
}
catch (NamingException e)
{
throw new DBException("Failed to get data source from JNDI directory: " + e.getMessage(), e);
}
catch (SQLException e)
{
throw new DBException("Unable to get connection from connection pool: " + e.getMessage(), e);
}
The error i get in wso2carbon.xml is:
TID: [0] [AS] [2016-02-04 15:14:57,007] ERROR {POCDAO} - A DBException occurred {POCDAO}
DBException: Unable to get connection from connection pool: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
)
at JNDIConnectionDescriptor.getConnection(JNDIConnectionDescriptor.java:159)
at JNDIConnectionDescriptor.getConnectionRO(JNDIConnectionDescriptor.java:49)
at ConnectionManager.getConnectionRO(ConnectionManager.java:179)
at BaseDAO.getConnection(BaseDAO.java:66)
at server.POCServer.savePocs(POCServer.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
at org.apache.cxf.jaxws.JAXWSMethodInvoker.performInvocation(JAXWSMethodInvoker.java:66)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
at org.apache.cxf.jaxws.AbstractJAXWSMethodInvoker.invoke(AbstractJAXWSMethodInvoker.java:232)
at org.apache.cxf.jaxws.JAXWSMethodInvoker.invoke(JAXWSMethodInvoker.java:85)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:74)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$2.run(ServiceInvokerInterceptor.java:126)
at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:131)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:308)
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:251)
at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:180)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:293)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:212)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:755)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:268)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.wso2.carbon.statistics.webapp.RequestIntercepterValve.invoke(RequestIntercepterValve.java:43)
at org.wso2.carbon.bam.webapp.stat.publisher.WebAppStatisticPublisherValve.invoke(WebAppStatisticPublisherValve.java:104)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:178)
at org.wso2.carbon.tomcat.ext.valves.CarbonTomcatValve$1.invoke(CarbonTomcatValve.java:47)
at org.wso2.carbon.webapp.mgt.TenantLazyLoaderValve.invoke(TenantLazyLoaderValve.java:56)
at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:47)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:141)
at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:156)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936)
at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:52)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1653)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at JNDIConnectionDescriptor.getConnection(JNDIConnectionDescriptor.java:97)
... 58 more
Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:743)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:666)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:566)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 61 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
at oracle.net.ns.NSProtocolStream.negotiateConnection(NSProtocolStream.java:275)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:264)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1452)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:496)
... 68 more