cmp & postgres

[rbottoms]

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.

[wchao]

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.

[xuxiaomin]

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.

[wchao]

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.

[pazu]

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

[rbottoms]

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.

[xuxiaomin]

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

Thanks a lot.

Xiaomin

[rbottoms]

>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.

[xuxiaomin]

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

[xuxiaomin]

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

[xuxiaomin]

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!

[wchao]

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.

[wchao]

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

[rbottoms]

>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.

[wchao]

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