Hi,
We’re trying to use a PostGres database for an external datasource but that option doesn’t exist in the DataSources tab of the Server Administrator.
Does anyone know how we go about adding additional datasources?
Thanks,
Nick.
You actually need to add the driver first. Download the JDBC driver from the Postgres site and save it to AppServer/server/rx/lib
In System Adminstrator on the Datasources tab. Click “Add…” (all of the fields on this form are editable)
Driver: “postgres”
Class: “org.postgresql.Driver”
Container Type Mapping: “Postgres”
Hibernate Dialect: “org.hibernate.dialect.PostgreSQLDialect”
Save. You can then add a JNDI entry and a Connection entry for the datasource.
You can find the various dialects support here: http://docs.jboss.org/hibernate/orm/3.5/javadoc/org/hibernate/dialect/package-summary.html (although need to check the hibernate version for accurate list).
-n
[QUOTE=NickK;20806]Hi,
We’re trying to use a PostGres database for an external datasource but that option doesn’t exist in the DataSources tab of the Server Administrator.
Does anyone know how we go about adding additional datasources?
Thanks,
Nick.[/QUOTE]
Thanks. I’ll give that a go.
We managed to configure this so that the jnditest.jsp page returns an OK for our connection. However, trying to actually use the connection throws an error. It seems reasonable to assume a PostGres database might fail a Oracle validation check. Do you know how to resolve this?
2013-09-09 15:58:58,926 WARN [org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker] Unexpected error in pingDatabase
java.lang.IllegalArgumentException: object is not an instance of declaring class
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker.isValidConnection(OracleValidConnectionChecker.java:68)
What does the AppServer/server/rx/deploy/rx-ds.xml file look like? It sounds like the connection checker may have been copied into your pg data source from your main rx datasource.
-n
[QUOTE=NickK;20889]We managed to configure this so that the jnditest.jsp page returns an OK for our connection. However, trying to actually use the connection throws an error. It seems reasonable to assume a PostGres database might fail a Oracle validation check. Do you know how to resolve this?
2013-09-09 15:58:58,926 WARN [org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker] Unexpected error in pingDatabase
java.lang.IllegalArgumentException: object is not an instance of declaring class
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker.isValidConnection(OracleValidConnectionChecker.java:68)[/QUOTE]
Good spot. Looks like this is where Percussion’s getting that information from.
Do you know the correct config for PostGres?
<check-valid-connection-sql>/* ping */ SELECT 1</check-valid-connection-sql>
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker<
/valid-connection-checker-class-name>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sort
er-class-name>
The check-valid-connection-sql just has to be a valid SQL statement on the connection. Just the sql connection checker, SELECT 1 will work. Based on what I saw on the Google, it looks like there is a Postgres connection checker shipped with JBOSS 4.2.3, that should have better performance:
And:
https://issues.jboss.org/browse/JBAS-3133?_sscc=t
Based on that you should be able to use:
<check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.PostgreSQLValidConnectionChecker</valid-connection-checker-class-name>
There doesn’t appear to be a specialized exception sorter for pg, so I’d just cut that element from the config.
-n
[QUOTE=NickK;20898]Good spot. Looks like this is where Percussion’s getting that information from.
Do you know the correct config for PostGres?
<check-valid-connection-sql>/* ping */ SELECT 1</check-valid-connection-sql>
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker<
/valid-connection-checker-class-name>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sort
er-class-name>[/QUOTE]