Websphere 3.5.3 prepared statement with LIKE predicate and connection pool

["Patrick L Archibald" <Patrick.Archibald@xxxxxxxxxxxxx>]

Hi All

I am getting inconsistent results from a prepared statement with
the LIKE predicate.  I'm wondering if the system is caching
the results of a previous statement or something.

The servlet is using a DataSource for connection pooling.
If I set the selection field values to all underscores I expect
to get all of the records back.  Sometimes I do and sometimes
I don't. It seems like if I send the servlet some values that is not
in the file the next time I send all underscores it doesn't return
any records.

I've got the basic same servlet running under Tomcat on
the AS/400 and it works consistently. The Tomcat version
is not using connection pooling.

Below are two pertinent snippets:  (Please don't yell at me
for using A.*. The file has 72 fields in it and I didn't have the energy
to specify all of them.)

1.  Here is the statement logic:

      PreparedStatement ps3 = connection.prepareStatement(SQL_SELECT);
      int recordCount = 0;
      String hlcUsec = request.getParameter("hlcUsec");
      String hldUsec = request.getParameter("hldUsec");
      if (hlcUsec == null) hlcUsec = "______";
      if (hldUsec == null) hldUsec = "______";
      pw.println("<!-- HLC USEC: " + hlcUsec + " HLD USEC: " + hldUsec +
"-->\n");
      ps3.setString(1,userProfile);
      ps3.setString(2,hlcUsec);
      ps3.setString(3,hldUsec);
      ResultSet rs = ps3.executeQuery();
      while (rs.next() && (recordCount < 96))
        {
//         format the records and put in a HTML table row
        }

2. Here is the statement:

private final static String SQL_SELECT =
    "SELECT A.*, " +
    "B.NOMEN, C.DIRCNAME " +
    "FROM TMS.TMSPFM19 AS A " +
    "JOIN TMS.TMSPFM19A AS B " +
    "ON A.TOLLTYPE = B.TOLLTYPE " +
    "JOIN SOM.SOMPFLM000 AS C " +
    "ON A.PHONENUM = C.PHONENUM AND " +
    "   A.ORDRNMBR = C.ORDRNMBR " +
    "WHERE A.TOLLTYPE IN " +
    "(SELECT TOLLTYPE FROM TMS.TMSPFM19B WHERE USRPRF = ?) AND " +
    "A.HLCUSEC LIKE ? AND A.HLDUSEC LIKE ? " +
    "ORDER BY A.MINTOTAL DESC " +
    "FOR READ ONLY " +
    "OPTIMIZE FOR 96 ROWS";

This is running on an AS/400 9406-2175-832MB OS/400 V4R5M0 Websphere 3.5.3

Thanx, PLA
/
/ Patrick L Archibald
/ http://HomeTelco.com/pla/
/