1 Reply Latest reply on Feb 29, 2004 3:34 AM by csp

    Question about postgresql datasource

    csp Newbie

      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