5 Replies Latest reply on Nov 20, 2012 10:25 PM by harikris

    h2console.war and postgreSQL

    harikris Newbie

      Hi All,

       

      Installed AS 7.1.1.Final on my MacBook Pro.

      Downloaded the Quickstart examples. Was able to successfully deploy and test helloworld, jboss-as-greeter wars.

      Then i set up the postgresql database engine to try jboss-as-cmt example. I was able to compile and deploy it.

      Everything works great except for one thing - i am unable to connect to the postgresql database from the h2console web-app that is shipped with the application server.

       

      The jboss-as-cmt war works perfectly i.e i am able to add customers to the invoice. I can psql into the db engine and i can see all the records. My only problem is that i can't use h2console webapp to get into my jboss-as-cmt postgres database i created.

       

      I am using:

      "org.postgresql.Driver" for the Driver Class field.

      "jdbc:postgresql://127.0.0.1:5432/jboss-as-cmt" for the JDBC URL. This is the same as my connection-url value set in the standalone-full.xml config file.

      "sa" for username and password.

      When i press the "Test Connection" button on the h2console app, unfortunately i see -

       

      Class "org.postgresql.Driver" not found [90086-161]
      org.h2.message.DbException: Class "org.postgresql.Driver" not found [90086-161]
          at org.h2.message.DbException.get(DbException.java:158)
          at org.h2.util.Utils.loadUserClass(Utils.java:429)
          at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:146)
          at org.h2.server.web.WebServer.getConnection(WebServer.java:653)
          at org.h2.server.web.WebApp.test(WebApp.java:839)
          at org.h2.server.web.WebApp.process(WebApp.java:215)
          at org.h2.server.web.WebApp.processRequest(WebApp.java:164)
          at org.h2.server.web.WebServlet.doGet(WebServlet.java:118)
          at org.h2.server.web.WebServlet.doPost(WebServlet.java:153)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
          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:680)
      Caused by: org.h2.jdbc.JdbcSQLException: Class "org.postgresql.Driver" not found [90086-161]
          at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
          ... 24 more
      Caused by: java.lang.ClassNotFoundException: org.postgresql.Driver from [Module "com.h2database.h2:main" from local module loader @b4e29b (roots: /Users/avyayatek/Downloads/jboss-as-7.1.1.Final/modules)]
          at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:190)
          at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:468)
          at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:456)
          at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:423)
          at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:398)
          at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:120)
          at java.lang.Class.forName0(Native Method)
          at java.lang.Class.forName(Class.java:169)
          at org.h2.util.Utils.loadUserClass(Utils.java:424)
          ... 22 more

       

      I am able to establish a successful connection from the jboss admin management console (http://localhost:9990/console) to the datasource. On the datasources profile panel, i have the name of the datasource set to "postgresDS", jndi set to "java:jboss/postgresDS". Connection URL is same as what i am use on the h2console - "jdbc:postgresql://127.0.0.1:5432/jboss-as-cmt"

       

      I don't know why h2console is not connecting to the postgresql db engine. It makes a successful connection to the h2 in-memory database that is used in the jboss-as-greeter example.

       

      I have placed the JDBC driver in the $JBOSS_HOME/modules/org/postgresql/main folder. Created the module.xml there. There is a Driver.class file inside the jar in ~/org/postgresql/ folder. I have modified the $JBOSS_HOME/standalone/configuration/standalone-full.xml to add the postgresql related datasource and driver sections.

       

      As i said, i can build jboss-as-cmt and deploy and it works as expected. If i could make the h2console connect to this postgresql database, it will give me some peace.

       

      I am a newbie to JBoss and server side stuff in general.

       

      I have attached some pics for reference.

       

      Thanks for your time.

        • 1. Re: h2console.war and postgreSQL
          Wolf-Dieter Fink Master

          As the h2console is a developer tool to connect to the h2 database which is the default for simple tests, I suppose that it will be not easy or even not possible to get it to work.

          For the missing driver you need to set a dependency to the deployment-structure or MANIFEST.

          But I suppose that the tool is more or less hardcoded for H2.

          1 of 1 people found this helpful
          • 2. Re: h2console.war and postgreSQL
            harikris Newbie

            Hi Wolf-Dieter,

             

            Is the tool (h2console.war) that is shipped with jboss-quickstart always been like this - that we can only connect to the H2? I am just beginning to do server side development and never had any experience with app servers before (JBoss or otherwise).

             

            I have added sections in the standalone-full.xml config file for loading the postgres sql driver and have defined the postgres datasource.

             

            I can see that the driver getting loaded while the server starts:

             

            [org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-7) JBAS010404: Deploying non-JDBC-compliant driver class org.postgresql.Driver (version 9.1)

             

            Isn't that sufficient?

             

            Could you please point me to reading material on how to set the dependency in MANIFEST file?

            Thanks a lot.

            • 3. Re: h2console.war and postgreSQL
              harikris Newbie

              Hi Wolf-Dieter Fink,

               

              I unzipped the h2console.war, edited the META-INF/MANIFEST.MF such that i now have:

               

              Dependencies: org.postgresql,com.h2database.h2

               

              inside it.

              I then zipped it back up and deployed it.

               

              Looks like we made some progress - i see a new error now. I am now starting to wonder if h2console.war really can only be used with H2 database due to some hardcoding in it's java class files. Can you please confirm based on your experience? Thanks a lot.

               

              No suitable driver found for jdbc:postgresql://localhost:5432 08001/0
              java.sql.SQLException: No suitable driver found for jdbc:postgresql://localhost:5432
                  at java.sql.DriverManager.getConnection(DriverManager.java:602)
                  at java.sql.DriverManager.getConnection(DriverManager.java:154)
                  at org.h2.util.JdbcUtils.getConnection(JdbcUtils.java:148)
                  at org.h2.server.web.WebServer.getConnection(WebServer.java:653)
                  at org.h2.server.web.WebApp.test(WebApp.java:839)
                  at org.h2.server.web.WebApp.process(WebApp.java:215)
                  at org.h2.server.web.WebApp.processRequest(WebApp.java:164)
                  at org.h2.server.web.WebServlet.doGet(WebServlet.java:118)
                  at org.h2.server.web.WebServlet.doPost(WebServlet.java:153)
                  at javax.servlet.http.HttpServlet.service(HttpServlet.java:754)
                  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:680)

               

              Unfortunately, i don't have the source code for the h2console.war to actually see if JdbcUtils.java was actually loading the driver or not.

              Any input is highly appreciated.

               

              Thanks.

              • 4. Re: h2console.war and postgreSQL
                Wolf-Dieter Fink Master

                I suppose that the  h2console is hardcoded to H2.

                Maybe the sources can be found here.

                But why you did not use a SQL tool like SQUirrel?

                1 of 1 people found this helpful
                • 5. Re: h2console.war and postgreSQL
                  harikris Newbie

                  Hi Wold-Dieter,

                   

                  Thanks for the link to the source. I created the h2console from the soure and this time, i placed the postgres JDBC driver jar file in the WEB-INF/lib folder. Now, i am able to use the h2console to connect to postgresql database.

                   

                  The h2console app that ships with the quickstart does not have any library jars in the WEB-INF/lib folder. Instead it specifies the dependencies in the META-INF/MANIFEST.mf file. I am positive both the h2 and postgres drivers are loaded while the JBoss starts - i can see the logs. But for some reason, this app only recognizes the h2 driver and fails to recognize the postgres driver. Looking at the source file, i am fairly positive that there is no "hardcoding" anywhere in there. So, it's still a mystery to me.

                   

                  But thanks to the source, i am able to create a version that is functional.

                  And thanks also for suggesting an alternative to h2console tool - SQUirrel.

                   

                  Apppreciate your pointers and tips.

                   

                  Thanks.