Using Postgresql under IBM WebSphere - Mailing list pgsql-jdbc

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