Question about postgresql datasource
I'm trying to setup JBoss (4.0DR3) to use Postgresql (7.4) datasource on Linux (Debian with kernel 2.4.22).
Have no problem accessing PG with JDBC, and just couldn't get JBoss to use the datasource properly.
Ok, here's what I did.
Copy postgresql-jdbc3.jar into the directory
$JBOSS_HOME/server/default/lib
Tried with postgresql-jdbc2.jar too, with the same problem.
Put a file in $JBOSS_HOME/server/default/deploy/postgres-ds.xml with contents:
<?xml version="1.0" encoding="UTF-8"?>
<local-tx-datasource>
<jndi-name>PostgresDS</jndi-name>
<connection-url>jdbc:postgresql://cerberus:5432/xptest1</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>csp</user-name>
csp
<min-pool-size>0</min-pool-size>
<max-pool-size>50</max-pool-size>
<blocking-timeout-millis>20000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</local-tx-datasource>
(Note: the string "csp" in the middle is supposed to be between the password tag, but JBoss Nukes is eating up the tag, and curiously, only the password tag).
Changed the file $JBOSS_HOME/server/default/conf/standardjbosscmp-jdbc.xml:
<jbosscmp-jdbc>
java:/PostgresDS
<datasource-mapping>PostgreSQL 7.2</datasource-mapping>
...
Changed the file $JBOSS_HOME/server/default/conf/standardjaws.xml:
java:/PostgresDS
<type-mapping>PostgreSQL 7.2</type-mapping>
false
....
Add to the file $JBOSS_HOME/server/default/conf/login-config.xml:
<application-policy name = "PostgresDbRealm">
<login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
flag = "required">
<module-option name = "principal">csp</module-option>
<module-option name = "userName">csp</module-option>
<module-option name = "password">csp</module-option>
<module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=PostgresDS</module-option>
</login-module>
</application-policy>
Now, using the gangster sample provided by JBoss, I change the file
jbosscmp-jdbc.xml to contain:
<jbosscmp-jdbc>
java:/PostgresDS
<datasource-mapping>PostgreSQL 7.2</datasource-mapping>
<create-table>true</create-table>
<remove-table>true</remove-table>
<pk-constraint>true</pk-constraint>
<preferred-relation-mapping>foreign-key</preferred-relation-mapping>
...
(Note: here again, JBoss Nukes is eating up random tag. The string
"java:/PostgresDS" is in the tag datasource.)
When I deployed the beans in gangster, I get the following error messages:
15:15:14,642 INFO [EntityContainer] Started jboss.j2ee:jndiName=crimeportal/Gangster,service=EJB
15:15:14,754 ERROR [EntityContainer] Starting failed
org.jboss.deployment.DeploymentException: Error while creating table organization; - nested throwable: (org.postgresql.util.PSQLException: ERROR: syntax error at or near "desc"
)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:232)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:93)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:155)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:544)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy18.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:155)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:544)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:173)
at $Proxy56.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:155)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:544)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy18.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:155)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:544)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:173)
at $Proxy16.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:566)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.server.ReflectedDispatcher.dispatch(ReflectedDispatcher.java:72)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:45)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:70)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:155)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:544)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:173)
at $Proxy8.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:458)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "desc"
at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:154)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:101)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:515)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:271)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:257)
at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:233)
at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:219)
... 71 more
Could someone give a hint what does that mean? The PG user "csp" is the
owner of the database "xptest1", and can create/drop tables, that's not
a problem.
And yes, PG is configured to allow connection with tcp_socket. JDBC apps can connect to PG without any problem.
Is there anything I didn't do it correctly?
TIA.
csp