Startups : Rajesh Ramchandani's Weblog

PostgreSQL with Sun Java System Application Server 8.1

Recently we tested out Open source databases (Postgresql and MySQL) for their JDBC Compliance and their capabilities to work with J2EE compliant Application servers such as Sun Java System Application server 8.1. Dave Cramer, the lead JDBC developer for PostgreSQL and Lance Obermeyer of Pervasive Software Inc. offered their engineering support and implemented the missing functions, data conversion routines and also fixed several bugs to be able to pass the JDBC compliance and also other tests (CMP/EJB tests) that any backend must pass in order to be Compliant to work with J2EE certified Application servers. All the changes and fixes that were done in the driver aren't part of the community release yet, but will be soon in near future. Also, we needed support for Stored procedures, so we had to use Postgresql 8.1-dev development release.

In order to make PostgreSQL work with Sun Java System Application server 8.1, you need to create a .tpersistence.properties file under $J2EE_HOME/domains//config directory and add the following:
database.postgresql.QUOTE_CHAR_END=
database.postgresql.QUOTE_CHAR_START=
database.postgresql.RTRIM=
database.postgresql.RTRIM_POST=
database.postgresql.SQRT=SQRT
database.postgresql.ABS=ABS

Creating Connection Pool

To create Connection Pools for Postgresql, under Admin server console:

1. Under Resources, select JDBC and on right hand side frame, select Connection Pools

2. Click on New to add create a new connection pool

3. Provide some name such as postgresql-pool and select Datasource type as javax.sql.ConnectionPoolDataSource

4. Click Next and enter the Datasource Classname as org.postgresql.ds.PGSimpleDataSource

5. Add the following properties to the connection pool:

DatabaseName <your-db-name>

port <your-selected-port-number> default port is 5432

user <DB-User-Name>

Password <DB-User-Password>

ServerName <DB-Server-Name>

Save the settings and click on Ping. If DB server is running and connection can be made, Ping will succeed. Please make sure you have copied the Postgresql jdbc driver in $J2EE_HOME/domains/<domain-name>/lib/ext directory.

Here's an sample domain.xml from an working installation:

<jdbc-connection-pool connection-validation-method="auto-commit" datasource-lassname="org.postgresql.ds.PGSimpleDataSource" fail-all-connections="false"  idle-timeout-in-seconds="300" is-connection-validation-required="false" is-isolation-level-guaranteed="false"  max-pool-size="96" max-wait-time-in-millis="60000"  name="cts-postgresql-pool" pool-resize-quantity="2" res-type="javax.sql.DataSource  "  steady-pool-size="32">
        <property name="DatabaseName" value="ctsinout"/>       
        <property name="port" value="5432"/>
        <property name="user" value="postgres"/>
        <property name="ServerName" value="magnolia"/>
        <property name="password" value="postgres"/>
        <property name="prepareThreshold" value="3"/>                Use Server Side Stored procedures        
        <property name="loglevel" value="2"/>                                DEBUG Level log
        <property name="protocolVersion" value="2"/>    
        <property name="ssl" value="true"/>  
</jdbc-connection-pool>

Restart the domains using "asadmin stop-domain and asadmin start-domain " or use Web based Admin server for the application server. Please note that this list of entries in the .tpersistence.properties file was sufficient for our work with the testsuite, however there may be more entries required depending on the sql usage.

You can read Lance Andersen's blogs for using Derby with Sun java System Application server at:
Derby with Sun Java Application Server

and Jonathan Bruce's blog for using MySQL with Sun Java System Application Server at:
MySQL with Sun Java Application Server

If you use Postgresql or are planning to use in near future and have questions about PostgreSQl on Solaris platform or questions, issues with Sun Java System application server with PostgreSQL (or MySQL), please feel free to contact me.

Posted at 07:24PM Jun 27, 2005 by rrajesh in Open Source  |  Comments[17]