resultset contains too many rows!

I have a bizarre problem-

I'm using a prepared statement to select from a view. If I do the select in
psql, I get one row. If I do the same select using a prepared statement in
JDBC, I get 4 rows. The 4:1 ratio is consistent- If I loosen the select
criterion & get 25 rows in psql, I'll get 100 out of JDBC.

This application displays data via the web & uses Tomcat, so to take the
guesswork out of where the problem was coming from, I stuck a debug
statement in my method that takes data out of the resultset & puts it in a
data bean. The code looks like this:

private CaseBean makeCaseBean(ResultSet results)
throws DBException
{
try
{
System.out.println("In makeCseBean, Processing row
"+results.getRow());
CaseBean caseB = new CaseBean();
caseB.setId(results.getString("case_id"));
caseB.setCourt_ori(results.getString("court_ori"));

caseB.setLocal_case_type_desc(results.getString("local_case_type_desc"));

caseB.setGlobal_case_type_desc(results.getString("global_case_type_desc"));

caseB.setCase_disp_global_desc(results.getString("case_disp_global_desc"));
caseB.setTitle(results.getString("case_title"));

caseB.setCase_filed_date((results.getString("case_filed_date").substring(5,7
)+'/'+

results.getString("case_filed_date").substring(8,10)+'/'+

results.getString("case_filed_date").substring(0,4)));

caseB.setGlobal_case_type_code(results.getString("global_case_type_code"));

return caseB;
}
catch (SQLException e)
{
throw new DBException(e.getMessage());
}
}

here is what I get on stdout when I run this using a criterion that returns
one row in psql:

In makeCseBean, Processing row 1
In makeCseBean, Processing row 2
In makeCseBean, Processing row 3
In makeCseBean, Processing row 4

Here is the code that loops through the resultSet:

public CaseBean[] getCases(String caseId)
throws DBException, NoRowsException {
try {
ResultSet results;
Collection cases = new ArrayList();
synchronized (getCasesStmt) {
getCasesStmt.clearParameters();
getCasesStmt.setString(1, caseId+"%");
results = getCasesStmt.executeQuery();
}
int rows = 0;
while (results.next()) {
cases.add(makeCaseBean(results));
rows++;
}
if (rows>0) {
return (CaseBean[])cases.toArray(new CaseBean[0]);
}
else {
throw new NoRowsException("Resulted from Case Query using ID like
"+caseId);
}
}
catch (SQLException e) {
throw new DBException("SQL problem in getCases: "+e.getMessage());
}
}

Since I'm using getRow()to come up with these numbers, I know that I'm
really getting 4 rows in the resultset, not just erroneously processing the
same row 4 times. Further support for the problem being in the returned
resultset is that when I return a large number of rows with the "order by"
removed, the duplicates do not always appear next to one another in the
output.

Here is the string that the prepared statement is created from:

final String getCases="SELECT * FROM CASE_TYPE_DISP "+
"WHERE UPPER(CASE_ID) LIKE UPPER(?) "+
"ORDER BY CASE_ID "+
"LIMIT 1000";

One more clue: The *same code* works correctly (no duplications) on a
smaller test database with about 3000 rows in the view. It fails on a large
database with about 19,000 rows in the view. That's what makes this so
bizzare- I can make the error go away simply by connecting to a smaller
database.

My environment is:

Debian Linux 2.4.14
PostgreSQL 7.1.3
jdbc7.1-2.1.jar (precompiled version)
IBM Java2

Any Ideas??

Thanks Everyone!

-Nick

--------------------------------------------------------------------------
Nick Fankhauser nickf(at)ontko(dot)com Phone 1.765.935.4283 Fax 1.765.962.9788
Ray Ontko & Co. Software Consulting Services http://www.ontko.com/