Crystal JRC not connecting

[alanmoor]

Summary: We have Crystal Java Reporting Component (JRC) embedded in our JSP-based JEE application, using Crystal's Report Viewer.  It works fine in 4.2.3 thru 6.x.  In 7, it deploys ok, but during run time, it gets a network timeout connecting to the database.  I believe this to be related to JNDI naming changes that have occurred in JBoss 7.

Details:

1) standalone-xxx.xml (just the driver definition)

        <subsystem xmlns="urn:jboss:domain:datasources:1.0">

            <datasources>

                <drivers>

                    <driver name="ojdbc6" module="oracle.jdbc">

                        <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

                    </driver>

                </drivers>

            </datasources>

        </subsystem>

2) xxx-ds.xml

            <datasources>

                <xa-datasource jndi-name="jdbc/conxons" pool-name="conxons" enabled="true" use-ccm="false" use-java="true">

                    <xa-datasource-property name="URL">

                        jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.46)(PORT = 1521))(CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = qat)    )  )

                    </xa-datasource-property>

                    <xa-datasource-property name="User">

                        ababababa

                    </xa-datasource-property>

                    <xa-datasource-property name="Password">

                        cdcdcdcdcdcd

                    </xa-datasource-property>

                    <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>

                    <driver>ojdbc6</driver>

                    <xa-pool>

                        <min-pool-size>1</min-pool-size>

                        <max-pool-size>20</max-pool-size>

                        <prefill>true</prefill>

                        <use-strict-min>false</use-strict-min>

                        <flush-strategy>FailingConnectionOnly</flush-strategy>

                        <no-tx-separate-pools>true</no-tx-separate-pools>

                        <pad-xid>false</pad-xid>

                        <wrap-xa-resource>true</wrap-xa-resource>

                    </xa-pool>

                    <validation>

                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>

                        <validate-on-match>false</validate-on-match>

                        <background-validation>false</background-validation>

                        <use-fast-fail>false</use-fast-fail>

                        <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>

                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>

                    </validation>

                </xa-datasource>

</datasources>

3) oracle jdbc driver module definition:

<module xmlns="urn:jboss:module:1.0" name="oracle.jdbc">

  <resources>

  <resource-root path="ojdbc6_g.jar"/>

  </resources>

    <dependencies>

        <module name="javax.api"/>

        <module name="javax.transaction.api"/>

    </dependencies>

</module>

4) Sequence of events:

• User inputs parameters, servlet i invoked
• In the servlet we switch the connection to use the JNDI name "java:/jdbc/conxons" instead of the connection used to build the report.  (I would just like to say at this point that this is just about the stupidest thing I've seen.  The connection to the database should NEVER be saved with the report).
• Crystal takes over and SHOULD use the JNDI name, however, I believe what's happening is that it can't find JNDI reference, so it is reverting to the internal connection definition and thus timing out trying to connect.

Previously, in 4.2.3, the JNDI name was "java:jdbc/conxons", not it's "java:/jdbc/conxons".  That's the only difference I can see.

Has anybody run into this?  Is there a better way to deploy Crystal reports? Is there any way to find out definitively what database it's trying to connect to?

[jaikiran]

Do you have any logs or exception stacktraces which can help understand what's going on?

[nickarls]

It might have log levels that can be cranked up to show the actual JNDI lookup being used.

Can you get it to work by hardcoding any JNDI URL in the reports builder tool?

[alanmoor]

the exception being produced is pretty straightforward:

2013-01-03 06:28:08

com.crystaldecisions.sdk.occa.report.lib.ReportSDKException: Logon Error: IO Error: The Network Adapter could not establish the connection---- Error code:-2147467259 Error code name:failed

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:2285)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:2305)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:737)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.a(SourceFile:167)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.a(SourceFile:529)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter$2.call(SourceFile:527)

at com.crystaldecisions.reports.common.ThreadGuard.syncExecute(SourceFile:102)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.for(SourceFile:525)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.int(SourceFile:424)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.request(SourceFile:352)

at com.businessobjects.sdk.erom.jrc.a.a(SourceFile:54)

at com.businessobjects.sdk.erom.jrc.a.execute(SourceFile:67)

at com.crystaldecisions.proxy.remoteagent.RemoteAgent$a.execute(SourceFile:716)

at com.crystaldecisions.proxy.remoteagent.CommunicationChannel.a(SourceFile:125)

at com.crystaldecisions.proxy.remoteagent.RemoteAgent.a(SourceFile:537)

at com.crystaldecisions.sdk.occa.report.application.ds.a(SourceFile:186)

at com.crystaldecisions.sdk.occa.report.application.ReportSource.a(SourceFile:878)

at com.crystaldecisions.sdk.occa.report.application.ReportSource.getPromptDatabaseLogOnInfos(SourceFile:815)

at com.crystaldecisions.sdk.occa.report.application.AdvancedReportSource.getPromptDatabaseLogOnInfos(SourceFile:338)

at com.businessobjects.report.web.a.e.a(SourceFile:174)

at com.businessobjects.report.web.a.e.a(SourceFile:97)

at com.businessobjects.report.web.a.e.a(SourceFile:343)

at com.businessobjects.report.web.a.t.a(SourceFile:1726)

at com.businessobjects.report.web.event.bw.broadcast(SourceFile:97)

at com.businessobjects.report.web.event.am.a(SourceFile:53)

at com.businessobjects.report.web.a.t.if(SourceFile:2104)

at com.businessobjects.report.web.e.a(SourceFile:300)

at com.businessobjects.report.web.e.a(SourceFile:202)

at com.businessobjects.report.web.e.a(SourceFile:135)

at com.crystaldecisions.report.web.ServerControl.a(SourceFile:607)

at com.crystaldecisions.report.web.viewer.ReportExportControl.a(SourceFile:124)

at com.crystaldecisions.report.web.ServerControl.processHttpRequest(SourceFile:342)

at com.rossgroupinc.process.reports.CrystalReport.exportReportDirectly(CrystalReport.java:453)

at com.rossgroupinc.process.reports.CrystalReport.doPost(CrystalReport.java:245)

at com.rossgroupinc.conxons.process.ProcessServletTS.doGet(ProcessServletTS.java:118)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:734)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161)

at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671)

at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930)

at java.lang.Thread.run(Thread.java:722)

Caused by: com.crystaldecisions.reports.common.QueryEngineException: Logon Error: IO Error: The Network Adapter could not establish the connection

at com.crystaldecisions.reports.queryengine.Connection.a(SourceFile:1861)

at com.crystaldecisions.reports.queryengine.Connection.br(SourceFile:1800)

at com.crystaldecisions.reports.queryengine.Connection.bs(SourceFile:491)

at com.crystaldecisions.reports.queryengine.Connection.t1(SourceFile:2978)

at com.crystaldecisions.reports.dataengine.dfadapter.DFAdapter.a(SourceFile:696)

at com.businessobjects.reports.sdk.requesthandler.DatabaseRequestHandler.a(SourceFile:309)

at com.businessobjects.reports.sdk.requesthandler.DatabaseRequestHandler.long(SourceFile:270)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.do(SourceFile:1151)

at com.businessobjects.reports.sdk.JRCCommunicationAdapter.if(SourceFile:661)

... 47 more

Caused by: com.businessobjects.reports.jdbinterface.common.DBException: Logon Error: IO Error: The Network Adapter could not establish the connection

at com.crystaldecisions.reports.queryengine.driverImpl.jdbc.JDBCConnection.Open(Unknown Source)

at com.crystaldecisions.reports.queryengine.JDBConnectionWrapper.Open(SourceFile:123)

at com.crystaldecisions.reports.queryengine.Connection.br(SourceFile:1771)

... 54 more

Caused by: java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:517)

at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:557)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:233)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:29)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:556)
... 57 more
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:389)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:431)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:882)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:267)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1625)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:365)
... 61 more
Caused by: java.net.ConnectException: Connection timed out: connect
at java.net.TwoStacksPlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:157)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:391)
at java.net.Socket.connect(Socket.java:579)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:147)
at oracle.net.nt.ConnOption.connect(ConnOption.java:130)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:367)
... 66 more

[alanmoor]

"

It might have log levels that can be cranked up to show the actual JNDI lookup being used.

Can you get it to work by hardcoding any JNDI URL in the reports builder tool?"

I have put the Oracle driver in debug mode, and it logs everything down to the internal call, EXCEPT in the connect method.  It doesn't output any information about what it's trying to connect to.  If you want to know every SQL and bind variable, it's great, but nothing on the actual connection.

The program logs the existing connection information, and then overrides it all by setting the "JNDI Datasource Name" property

06:38:05,437 DEBUG [com.rossgroupinc.process.reports.CrystalReport] Procedure Verified MZP4_007.MZ_CRPT_BATCH_PMT_POST_SUMMARY

06:38:07,882 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server Type' = 'JDBC (JNDI)'

06:38:07,882 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'PreQEServerName' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'

06:38:07,883 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Trusted_Connection' = 'false'

06:38:07,883 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Database DLL' = 'crdb_jdbc.dll'

06:38:07,883 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Use JDBC' = 'true'

06:38:07,884 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server Name' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'

06:38:07,884 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Connection URL' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'

06:38:07,884 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'JNDI Datasource Name' = 'java:jdbc/crystal'

06:38:07,884 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server' = '//172.16.1.31'

06:38:07,885 DEBUG [com.rossgroupinc.process.reports.CrystalReport] Setting JNDI Datasource Name to java:/jdbc/mz_plus/007

Note that in JBoss 4.2.3, the datasource name was getting set to "java:jdbc/mz_plus/007 and it works fine.

I'm also wondering if maybe deep in the bowels of the Crystal engine there's a dependency I've missed...

[nickarls]

At least it appears to succeed enough in the lookup so it ends up with something (that doesn't respond)

[alanmoor]

More Information:

  I was mistaken earlier, the same JNDI name is being used.  Everything is exactly the same in the calls.  I ran the same report (individual report doesn't matter, they all behave the same) on JBoss 4.2.3.GA, also running on Java 7, and it worked fine:

2013-01-03 09:13:59,506 DEBUG [com.rossgroupinc.process.reports.CrystalReport] Procedure Verified MZP4_007.MZ_CRPT_BATCH_PMT_POST_SUMMARY
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server Type' = 'JDBC (JNDI)'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'PreQEServerName' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Trusted_Connection' = 'false'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Database DLL' = 'crdb_jdbc.dll'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Use JDBC' = 'true'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Database Class Name' = 'oracle.jdbc.OracleDriver'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server Name' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Connection URL' = 'jdbc:oracle:thin:@//172.16.1.31:1521/mzdev'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] connection property: 'Server' = '//172.16.1.31'
2013-01-03 09:13:59,508 DEBUG [com.rossgroupinc.process.reports.CrystalReport] Setting JNDI Datasource Name to java:/jdbc/mz_plus/007
2013-01-03 09:13:59,583 INFO  [com.rossgroupinc.process.reports.CrystalReport] Finished BATCHPAYMENTPOSTINGSUMMARY - Batch Payment Posting Summary: user=ADMIN batch:false  Total time: 964ms
2013-01-03 09:13:59,631 DEBUG [com.rossgroupinc.process.reports.CrystalReport] reporting SUCCESS

So the only thing I can think of that may be getting in the way would be how Crystal is using JNDI to look up the Datasource, or maybe some dependency that is in the original classpath that is now missing in JBoss 7.  I created the Crystal module with all the jars from the original path and added it as a dependency in my jboss-deployment-structure.xml.  I'm not receiving any errors related to module dependencies or missing classes.  My gut feeling is that it is trying to connect using the JNDI lookup, failing, swallowing the error, then reverting to the connection that was used to create the report.  That connection, of course, is on a server that no longer exists, so it times out trying to connect to the database.