Using Postgresql under IBM WebSphere

Hi,

I have recently tried to implement a J2EE/EJB entity bean
working in BMP mode against a Postgresql data base under
WebSphere, specifically IBM WebSphere Single Server 4.0.1
running under Red Hat Linux 7.1. This was just a test,
part of a study/learning project of mine.

I am been only (very) partly successful. In fact,
IMHO WebSphere currently won't work with Postgresql,
unfortunately. Anyway I must postpone this project for now.

Nevertheless, I have a few issues that I think are worth reporting,
** including one bug for sure **.

I used the JDBC driver of Postgresql 7.1.3 (which I compiled
from the sources, as explained elsewhere on this list).
But as far as I can see, my findings still apply in 7.2b3.

1) In org.postgresql.xa.XADataSourceImpl.getTxRecover(),
the return statement:

return (Xid[]) list.toArray();

always throws a ClassCastException, so that's the *bug*
that should be fixed. This alternative works:

Xid [] array = new Xid [list.size()];
return (Xid[]) list.toArray(array);

The WebSphere server often (but not always) calls
this method during its initialization. When it does,
it does not even initialize, because of this exception.

2) Properties files lack the 'postgresql.ds.userpswd' entry.

In 7.2b3, only 'error_de.properties' has it. Suggested:

(English) postgresql.ds.userpswd:The user and/or the password is null.
(French) postgresql.ds.userpswd:Le code d'usager et/ou le mot de passe
est nul.

3) Websphere frequently logs this warning:

PortabilityLa W CONM0002W: Unrecognized database or driver
org.postgresql.PostgresqlDataSource; using generic settings

So is seems to me that Websphere expects something from data sources
that it does not find with the Postgresql one.

4) Configuring a Postgresql data source in WebSphere.

I used the web-based WebSphere Admin tool to carry out this
prerequisite. I want to mention a few things here:

a) First, the Postgresql JDBC driver itself must be defined.
Expand 'Resources', then click on 'JDBC Drivers', and 'New'.

- Field 'Implementation Classname' must be
'org.postgresql.PostgresqlDataSource'.

- It is sufficient to place the jar file of the Postgresql
JDBC driver into the 'lib' subdirectory where WebSphere
is installed. I am not sure about the need/use of
field 'Server Class Path', which can contain anything
as long as directory 'lib' contains the needed jar.
Anyway, if class 'org.postgresql.PostgresqlDataSource'
cannot be found when requested, that will be clear
enough in the WebSphere logs.

b) Once the Postgresql JDBC driver is defined to WebSphere,
an actual data source must be defined under it.

- I used field 'Database Name' to specify the name of the
target Postgresql data base.
- I have specified values for fields 'Default User ID'
and 'Default Password', but I am not sure this was
needed or used (see below).
- I have not played with the many fields under
'Connection Pool Settings', I have only specified
the same values as other data sources already
defined in WebSphere (for the WebSphere samples).
- I have played with 'Driver Specific Settings', though,
and this is my main point here. After you click
'Properties', you may define these 4 properties
that the Postgresql data source actually supports:

Name Java type
serverName java.lang.String
portNumber java.lang.Integer
loginTimeout java.lang.Integer
transactionTimeout java.lang.Integer

I figured them using the Java source of
'org.postgresql.PostgresqlDataSource' class.
Names are case-sensitive. Be sure to specify
the correct data type.

So by specifying an IP address in 'serverName',
I successfully had WebSphere connect to
my Postgresql server on a separate machine of
my private LAN.

If you specify a property name that is not
supported, say XYZ, WebSphere will warn you
in its logs:

PortabilityLa W CONM7002W: Could not find the property XYZ on class
org.postgresql.PostgresqlDataSource

Now in order to get my limited success (inserted one row)
I had to do a couple of patches that are *certainly* not solutions.
In fact, those patches are the main reason why I pretend
that Postgresql currently won't work under WebSphere.
But they let me go ahead a bit.

5) In org.postgresql.xa.XADataSourceImpl.getXAConnection(String, String),
I replaced the throw statement with 'return getXAConnection();'.

6) In org.postgresql.PostgresqlDataSource.getConnection(String, String)
I supplied default user/password values when one is null,
like this (that's about in the middle of the method):

if ( user == null || password == null )
{
// Supply defaults if needed.
if (user == null) user = "mf";
if (password == null) password = "x";
// throw new PSQLException( "postgresql.ds.userpswd" );
}

(Now you know how I found about the missing 'postgresql.ds.userpswd').

In short, I have problems passing the user/password, regardless of where
I specify them in my EJB code or in the data source definition in the
WebSphere admin tool. It seems WebSphere always calls
getXAConnection(String, String) and
getConnection(String, String), perhaps with null values?

And I have problems with transactions too, btw.

Regards,

Marcel Frechette