Sun's reference j2ee server (j2sdk1.2.1 and j2sdk1.3) will work with Postgresql, although it's not a supported configuration and therefore probably not a great idea for production systems. I like the combination, though, since my machines are resource constrained and I'm usually running Postgresql anyway - I'd rather not have another JVM running to handle database queries.

The steps to follow are:

• install the software, and get everything working with Cloudscape. This may seem like a waste of time but it's not - it establishes a baseline.
• hook the Postgresql jdbc driver in with the j2ee server
• change the user ID in the beans
• Go for it!
• Troubleshoot, then repeat previous step

Install, test

Beyond the scope of this doc - but note that I'm using Postgresql 7.0.2, Sun's jdk 1.3.0, Sun j2sdk1.2.1 on a Debian 2.1 (potato) i386 system. Your mileage may vary.

Follow all instructions - you can test that you've got the server (and Cloudscape) set up correctly by running some of the basic Sun examples, see

$J2EE_HOME/doc/j2sdkee1.2.1/doc/guides/ejb/html/DevGuideTOC.html

(you need to download the docs in a separate bundle from the sdk.) Good tests to start with are the Converter (to test basic client-server operation) and the Account (to test basic jdbc database operations).

You'll also want to test the Postgresql jdbc driver. There are some test programs in the postgresql source tree at src/interfaces/jdbc/example/. Try basic.java for a simple test. This tests that: you've got a database, you can describe it in a jdbc URL, you've got a user/password combo that can read and write the database.

Hook Postgresql jdbc Driver into J2EE

First find the postgresql.jar file (it's in /usr/share/java on my Debian box) and copy it to $J2EE_HOME/lib/system (according to the j2ee docs it needs to be there for security reasons). Then edit $J2EE_HOME/bin/userconfig.sh so that =$J2EE_CLASSPATH includes postgresql.jar.

1.2.1:

Edit $J2EE_HOME/config/default.properties. First look for the line that looks like:

jdbc.drivers=COM.cloudscape.core.RmiJdbcDriver

...and change it to read:

jdbc.drivers=COM.cloudscape.core.RmiJdbcDriver:org.postgresql.Driver

This tells j2ee how to load the drivers. Now you need to tell it how to map the database names that the application uses onto jdbc database names. Just below the jdbc.drivers line you'll find one that sets jdbc.datasources. I set up a database called account in postgresql so mine reads:

jdbc.datasources=jdbc/Cloudscape|jdbc:postgresql:account

I suppose this is somewhat misleading but it requires the fewest changes from the Cloudscape configuration.

1.3:

Edit $J2EE_HOME/config/resource.properties. Add a line:

jdbcDriver.1.name=org.postgresql.Driver

and then change

jdbcDataSource.0.url=jdbc:cloudscape:rmi:CloudscapeDB;create=true

to

jdbcDataSource.0.url=jdbc:postgresql:account

Change the user ID in the beans The Cloudscape server doesn't seem to use the jdbc user/password that postgresql does so you'll need to add it to your beans. In the deploytool you choose the bean (say "AccountBean") in the left pane then choose the "Resource Ref's" tab at the top of the right pane. Select the resource factory whose name is "jdbc/AccountDB" and you'll see that the "Deployment Settings" box at the bottom will now allow you to enter a JNDI name (which has to match the one in the jdbc.datasources, e.g. jdbc/Cloudscape, and a username and password. You can now save and deploy the application. (As an aside: if you're building the .ear by hand you'll need to put these values in the META-INF/sun-j2ee-ri.xml file).

Go for it!

Run the AccountClient client and bask in the glory that is postgresql and java. A few troubleshooting notes:

• You don't really get much feedback from the console that you run the client from. Take a look at $J2EE_HOME/logs/{hostname}/ejb/j2ee/error.log , there's probably a stack trace there that will help.
• You need to clean the account table out after every run or else you'll get a PK violation. In 1.2.1 delete from account; or in 1.3 delete from myaccount;.
• Cloudscape seems to be more forgiving in terms of casting rules than postgresql so you'll probably get an exception about needing to use an explicit cast. This patch to AccountEJB.java should work:

*** AccountEJB.java	  Mon Feb  5 16:40:23 2001
--- AccountEJB.java.old	  Wed May  3 23:07:56 2000
***************
*** 282,288 ****
  
		  String selectStatement =
				  "select id from account " +
!				 "where balance::float between ? and ?";
		  PreparedStatement prepStmt = 
				  con.prepareStatement(selectStatement);
  
--- 282,288 ----
  
		  String selectStatement =
				  "select id from account " +
!				 "where balance between  ? and ?";
		  PreparedStatement prepStmt = 
				  con.prepareStatement(selectStatement);

HTH.

-- TobyCabot - 05 Feb 2001