Forum Stats

  • 3,816,299 Users
  • 2,259,166 Discussions
  • 7,893,446 Comments

Discussions

Jasper reports integration with oracle Autonomous DB

Mohamed Heba
Mohamed Heba Member Posts: 10 Green Ribbon

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

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,189 Silver Crown

    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
    Mohamed Heba Member Posts: 10 Green Ribbon

    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.

    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]" 

    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:25.253 SEVERE [http-nio-8080-exec-4] org.apache.catalina.core.StandardWrapperValve.invoke Servlet.service() for servlet [Test] in context with path [/JasperReportsIntegration] threw exception

    java.lang.NullPointerException

    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:28.471 SEVERE [http-nio-8080-exec-7] 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: Connection reset

    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:31.965 SEVERE [http-nio-8080-exec-8] 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: Unknown host specified 

    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)


    Thanks again for your time.

  • Mike Kutz
    Mike Kutz Member Posts: 6,189 Silver Crown

    What Version of Jasper Reports are you using?

    Did you copy over odbci8 like the person did in the bug report?

    I'd have to research the correct jdbc string to use, but the ones you have posted seem too short.

  • Mohamed Heba
    Mohamed Heba Member Posts: 10 Green Ribbon

    JasperReportsIntegration-2.4.0.0

    Yes I am using ojdbc8 

    Sorry i didn't know what you mean by this "I'd have to research the correct jdbc string to use, but the ones you have posted seem too short."


    I used same setting to connect to autonomous DB from my windows VM and it worked just yesterday

    [datasource:TRIALPOS1]

    type=jdbc

    name=default

    url=jdbc:oracle:thin:@apexdb_high?TNS_ADMIN=C:/Source/PCW/

    username=username

    password=password


    Local windows VM


    Cloud unix VM


  • Client_321
    Client_321 Member Posts: 32 Red Ribbon

    Hi i managed to connect with both versions, old and new, i mean on jasperreportsintegration. The key is if you use old version then you have to use keystore to conect. With new version you can use standard aproach with edit sqlnet ora with corect path. Can you conect with jasperstudio on autonomus db? il provide more info tommorow with details

  • Mohamed Heba
    Mohamed Heba Member Posts: 10 Green Ribbon

    Thanks @Client_321. I never used jasperstudio, Waiting for the details.

  • Client_321
    Client_321 Member Posts: 32 Red Ribbon

    Can you provide connection string and driver version for jasperreport connection with autonomus database?.

    If you use old version of Jasperreportsintegration 2.4 than modify ojdbc.properties file like this:

    __

    # Connection property while using Oracle wallets.

    #oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

    # FOLLOW THESE STEPS FOR USING JKS

    # (1) Uncomment the following properties to use JKS.

    # (2) Comment out the oracle.net.wallet_location property above

    # (3) Set the correct password for both trustStorePassword and keyStorePassword.

    # It's the password you specified when downloading the wallet from OCI Console or the Service Console.

    javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks

    javax.net.ssl.trustStorePassword=test

    javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks

    javax.net.ssl.keyStorePassword=test

    __

    Be sure to uncomment these lines. Modify sqlnet.ora like this, be sure to make path to your wallet and tns , you write your own path to wallet:

    WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/u01/tomcat/apache-tomcat-8.5.63/webapps/JasperReportsIntegration/WEB-INF/Wall")))

    After these changes restart tomcat and it should connect. And You have to use odbci8 like u mention in previous post.

    And i notice u put wallet in tmp folder, you should not do that, make another path.


    Hope it helps

    Haris