Jasper reports integration with oracle Autonomous DB

Mohamed Heba · Jan 11, 2022 3:28AM

Hi,

I am trying to use JasperReports with Oracle cloud autonomous Database in oracle cloud.

I already provisioned the Autonomous DB and I successfully connected to it from VM created in the same subnet using SQL. After installing Tomcat on the same VM I spent days trying to configure JasperReports data source to connect to the same database with no success.

Can anyone guide me through this?

OS: Oracle Linux 5.4.17-2136.301.1.3.el7uek.x86_64

Tomcat version: 9.0.56

JasperReportsIntegration-2.4.0.0

Mike Kutz · Jan 11, 2022 1:37PM

The ability for your Compute node (VM) to access your ADB seems to indicate that you do not have a firewall issue when talking in that direction. (I suspect you will need tell OCI to allow the Compute Node to be accessed by the ADB node [on specific ports])

Right off, it sounds like Tomcat needs the tnsnames.ora file found within the .ZIP file (that you download from the OCI Web page) in addition to the specific Wallet (also in .ZIP file).

Which documents/sites are you following?

What have you tried?

What were the results? (Especially error codes. Maybe log files)

note I haven't used Jasper Reports myself.

Mohamed Heba · Jan 13, 2022 1:25AM

Thank you, Mike, for your reply.

Yes, i can't see any network or firewall between the 2 servers. I eliminated this by connecting to the database from the same server using SQL.

I couldn't find a proper documentation i can follow to resolve this issue. only few links below with no success.

https://github.com/daust/JasperReportsIntegration/issues/74

and

Connecting to Autonomous Transaction Processing with Tomcat - YouTube

I downloaded the wallet and modified the DataSource as mentioned in these links as below.

[datasource:JasperDS]

type=jdbc

name=default

url=jdbc:oracle:thin:@adb.us-ashburn-1.oraclecloud.com:1521/APEXDB

username=dbusername

password=DB_Password

[datasource:JasperDS1]

type=jdbc

name=default

url="jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=/tmp/dbwalet"

username=dbusername

password=DB_Password

[datasource:JasperDS2]

type=jdbc

name=default

url=jdbc:oracle:thin:@apexdb_high

username=dbusername

password=DB_Password

[datasource:JasperDS3]

type=jdbc

name=default

url=jdbc:oracle:thin:@adb.eu-frankfurt-1.oraclecloud.com:1521/APEXDB

username=dbusername

password=DB_Password

[datasource:JasperDS4]

type=oracle.ucp.jdbc.PoolDataSource

name=default

url="jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=/tmp/dbwalet"

username=dbusername

password=DB_Password

-- Errors Received

13-Jan-2022 01:17:09.898 SEVERE [http-nio-8080-exec-9] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception

java.lang.RuntimeException: Could not connect via JDBC: IO Error: The Network Adapter could not establish the connection

at de.oc.utils.Utils.throwRuntimeException(Unknown Source)

at de.oc.db.ConnectionUtility.getConnection(Unknown Source)

at de.oc.integration.jasper.webapp.AppConfig.getConnection(Unknown Source)

at de.oc.integration.jasper.webapp.Test.doGet(Unknown Source)

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

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

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

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

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)

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

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

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

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

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)

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

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

at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687)

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

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

at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)

at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)

at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)

at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)

at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)

at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)

at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

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

13-Jan-2022 01:17:21.362 SEVERE [http-nio-8080-exec-6] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception

java.lang.RuntimeException: Could not connect via JDBC: IO Error: Invalid connection string format, a valid format is: "//host[:port][/service_name]"