14 Replies Latest reply on Feb 24, 2002 4:35 PM by Wellie Chao

    cmp & postgres

    Richard Bottoms Newbie

      Anyone have success using Postgres with the following jaws.xml setting:

      <create-table>true</create-table>
      <remove-table>true</remove-table>

      If I manually create the tables in Postgres before using the Bean it works just fine. If I set to options to true to allow the Container to create the tables, the bean fails.

      Are settings in jboss.jcml, or the postgres pg_hba.conf/postgresql.conf files that need to be altered to make this work?

      Thanks,
      r.b.


        • 1. Re: cmp & postgres
          Wellie Chao Newbie

          Make sure your database settings are the same for JBoss in jboss.jcml as they are when you manually create the tables. You should have section defined somewhat similarly to the following:


          PostgreSQLDS
          org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
          jdbc:postgresql://localhost/test
          wchao
          blah
          0
          3


          You should replace localhost, test, JDBCUser, and Password.

          • 2. Re: cmp & postgres
            Xiaomin Xu Newbie

            Is "org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl" mandatory?
            Or I can write as "org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl", because there is an example in my jboss.jcml file:

            InstantDB
            org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl

            jdbc:idb:../conf/default/instantdb.properties


            10
            0


            But no matter what I use, It still can't work and I've got an error like this:
            [PostgresDB] Starting
            [PostgresDB] XA Connection pool PostgresDB bound to java:/PostgresDB
            [PostgresDB] Stopped
            [PostgresDB] java.lang.NullPointerException:
            [PostgresDB] at org.opentools.minerva.jdbc.xa.XAPoolDataSource.getConnection(
            XAPoolDataSource.java:165)
            [PostgresDB] at org.jboss.jdbc.XADataSourceLoader.startService(XADataSourceLo
            ader.java:330)
            [PostgresDB] at org.jboss.util.ServiceMBeanSupport.start(ServiceMBeanSupport.
            java:93)
            [PostgresDB] at java.lang.reflect.Method.invoke(Native Method)
            [PostgresDB] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl
            .java:1628)
            [PostgresDB] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl
            .java:1523)
            [PostgresDB] at org.jboss.util.ServiceControl.start(ServiceControl.java:97)
            [PostgresDB] at java.lang.reflect.Method.invoke(Native Method)
            [PostgresDB] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl
            .java:1628)
            [PostgresDB] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanServerImpl
            .java:1523)
            [PostgresDB] at org.jboss.Main.(Main.java:217)
            [PostgresDB] at org.jboss.Main$1.run(Main.java:121)
            [PostgresDB] at java.security.AccessController.doPrivileged(Native Method)
            [PostgresDB] at org.jboss.Main.main(Main.java:117)
            [Service Control] Could not start DefaultDomain:service=XADataSource,name=Postgr
            esDB
            [Service Control] java.lang.NullPointerException:
            [Service Control] at org.opentools.minerva.jdbc.xa.XAPoolDataSource.getCon
            nection(XAPoolDataSource.java:165)
            [Service Control] at org.jboss.jdbc.XADataSourceLoader.startService(XAData
            SourceLoader.java:330)
            [Service Control] at org.jboss.util.ServiceMBeanSupport.start(ServiceMBean
            Support.java:93)
            [Service Control] at java.lang.reflect.Method.invoke(Native Method)
            [Service Control] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanSe
            rverImpl.java:1628)
            [Service Control] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanSe
            rverImpl.java:1523)
            [Service Control] at org.jboss.util.ServiceControl.start(ServiceControl.ja
            va:97)
            [Service Control] at java.lang.reflect.Method.invoke(Native Method)
            [Service Control] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanSe
            rverImpl.java:1628)
            [Service Control] at com.sun.management.jmx.MBeanServerImpl.invoke(MBeanSe
            rverImpl.java:1523)
            [Service Control] at org.jboss.Main.(Main.java:217)
            [Service Control] at org.jboss.Main$1.run(Main.java:121)
            [Service Control] at java.security.AccessController.doPrivileged(Native Me
            thod)
            [Service Control] at org.jboss.Main.main(Main.java:117)
            [DefaultDS] Starting
            [DefaultDS] XA Connection pool DefaultDS bound to java:/DefaultDS

            Do you know what's wrong? Or you need my entire configuration:
            <!-- JDBC -->

            org.hsql.jdbcDriver,org.enhydra.instantdb.jdbc.idbDriver,org.postgresql.Driver



            1476
            true
            default
            false



            InstantDB
            org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl

            jdbc:idb:../conf/default/instantdb.properties
            1200000

            10

            false
            false
            false
            true
            120000
            1800000
            false
            false
            1.0
            0


            org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl
            PostgresDB
            jdbc:postgresql://localhost/xiaomin
            xiaomin
            ""
            0
            10


            DefaultDS
            org.opentools.minerva.jdbc.xa.wrapper.XADataSourceImpl

            jdbc:postgresql://localhost/xiaomin
            1200000
            sa
            10

            false
            false
            false
            true
            120000
            1800000
            false
            false
            1.0
            0


            Thanks so much for your patience to finish reading my question. And would you kindly give me some suggestions about my problem? I am new in jboss and have no idea about this, I am using Jboss 2.2.2. Thanks again.

            • 3. Re: cmp & postgres
              Wellie Chao Newbie

              Can you upgrade to 2.4.4? I'm using 2.4.4 and PostgreSQL 7.1.2. It was pretty easy to set up JBoss with PostgreSQL using my configuration. I made three changes:

              [1] In the first mbean after the JDBC comment, I added postgresql like so:

              org.hsqldb.jdbcDriver, org.postgresql.Driver


              [2] After the other mbeans related to JDBC but before the comment that says "JBoss Server Management", I added the following:

              PostgreSQLDS
              org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl
              jdbc:postgresql://localhost/test
              wchao
              blah
              0
              3


              (You should of course change the URL, JDBCUser, and Password to appropriate values)

              [3] I made a symlink from postgresql.jar in the PostgreSQL distribution directory (at /usr/local/pgsql/share/java/postgresql.jar in my installation) to $JBOSS_DIST/lib/ext/.

              That was it, and now it works beautifully.

              • 4. Re: cmp & postgres
                Marcus Brito Novice

                By the way, username and password *should* be supplied even if your database doesn't need them. The JDBC Driver requires them.

                • 5. Re: cmp & postgres
                  Richard Bottoms Newbie

                  I have it just as you have indicated and it still fails:


                  org.postgresql.Driver



                  PostgresDB
                  org.jboss.pool.jdbc.xa.wrapper.XADataSourceImpl

                  jdbc:postgresql://localhost/javatest
                  xxx
                  xxxxx
                  0
                  3


                  Again to emphasize, I can connect to the data source and maually work with the database. If I pre-create the tables the bean works fine. The failure comes with these settings:

                  <create-table>true</create-table>
                  <remove-table>true</remove-table>

                  Is there something in Postgres itself I should check as well?

                  Thanks,
                  r.b.


                  • 6. Re: cmp & postgres
                    Xiaomin Xu Newbie

                    Hi,wchao,
                    I can't understand the point 3, can you explain in detail?

                    Thanks a lot.

                    Xiaomin

                    • 7. Re: cmp & postgres
                      Richard Bottoms Newbie

                      >Hi,wchao,
                      >I can't understand the point 3, can you explain in >detail?
                      > Thanks a lot.
                      > Xiaomin


                      I think that means you put a link to the orginal postgres.jar driver file into the /ext directory instead of the file itself.

                      You could just as well have two copies of the the file and actually put one in /ext.


                      r.b.


                      • 8. Re: cmp & postgres
                        Xiaomin Xu Newbie

                        Hi,chao,
                        Thanks so much for your answer. But now I have met another problem: when I first start run "run.sh", everything is OK(which means no error message in the running shell), but after I changed "postgreSQLDB" to "postgresDB" in my jboss.jcml file, then run, it shows me that some errors about "postgreSQLDB",like classNotFoundException, What's wrong? Why it still show "postgreSQLDB" rather than "postgreDB" and why it can't recognize the new jboss.jcml file? Do I need to restart something? Actually I have killed the session then run the run.sh again. Still can't work. Why?
                        I am so confused about all of this. Thanks so much for your help.

                        Best,
                        Xiaomin

                        • 9. Re: cmp & postgres
                          Xiaomin Xu Newbie

                          I have figured out that I need change jboss-auto.jcml when I change jboss.jcml.

                          • 10. Re: cmp & postgres
                            Xiaomin Xu Newbie

                            hi,wchao,
                            Are you sure you don't change the "DefaultDS"? Can you see the table that created under database "test" or all the data are saved in the hypersonic database(jboss/db/jboss.script)?

                            Thanks a lot!

                            • 11. Re: cmp & postgres
                              Wellie Chao Newbie

                              Are you sure the user who is connecting to the database has permissions to create tables? It could be that when you manually access the database as user X, you have permission to create/alter/remove tables. When you connect via your Java app in JBoss as user Y, you might not have the same permissions.

                              • 12. Re: cmp & postgres
                                Wellie Chao Newbie

                                Yes, I am certain the changes are made in the test database of PostgreSQL. I use psql and see the data in there.

                                • 13. Re: cmp & postgres
                                  Richard Bottoms Newbie

                                  >Are you sure the user who is connecting to the database has permissions to create tables?

                                  I am connecting as user postgres so I have full rights to the best of my knowledge. But, that's why I also asked whether there might be some setting or permission on the Postgres side I need to change.

                                  • 14. Re: cmp & postgres
                                    Wellie Chao Newbie

                                    > I am connecting as user postgres so I have full
                                    > rights to the best of my knowledge. But, that's why I
                                    > also asked whether there might be some setting or
                                    > permission on the Postgres side I need to change.

                                    If you are connecting as postgres (PostgreSQL superuser), the only thing that comes to mind immediately is that JBoss is issuing bad create commands to PostgreSQL. Have you tried looking in the postgresql.log file? If you don't have logging turned on, change the following two settings in /var/lib/pgsql/data/postgresql.conf (it may be located in a different place for your installation):

                                    log_connections = true
                                    log_timestamp = true

                                    Look at the commands that JBoss issues to create the tables. I am guessing it is either a bug in JBoss or that you did not properly set up the jaws.xml file. You may already know this, but in case you don't, the jaws.xml descriptor under META-INF should have the following two settings:

                                    PostgreSQLDS
                                    <type-mapping>PostgreSQL</type-mapping>