Hello,
I am using JBoss 4.0.0 and JDK 1.5.0
I have this scenerio:
In a stateless session bean, I use a findAll() to retrieve all records from a table, and iterate the returned Collection as follows:
Collection c = localHome.findAll();
for (Iterator it = c.iterator(); it.hasNext(); ) {
LocalInterface lf = (LocalInterface)it.next();
ValueObject obj = lf.getValueObject();
// process on the value object
......
}
This works for small number of records. But it fails when I have 3975 records.
If I use Firebird as database, I got the following message:
GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Unexpected end of command
If I use MSSQL as database, I got the following message:
Prepared or callable statement has more than 2000 parameter markers.
In the JBoss server.log, I found JBoss generated the following SQL for in the first lf.getValueObject() call
SELECT ... FROM MYTABLE WHERE (KFLD1=? AND KFLD2=? AND KFLD3=? AND KFLD4=?) OR (KFLD1=? AND KFLD2=? AND KFLD3=? AND KFLD4=?) ......
the "OR (KFLD1=? AND KFLD2=? AND KFLD3=? AND KFLD4=?)" is repeated for (number of records - 1) times
That means if I have 3975 records, the "OR (KFLD1=? AND KFLD2=? AND KFLD3=? AND KFLD4=?)" is repeated 3974 times.
And this cause the problem.
The primary key of MYTABLE contains 4 fields.
I have further proved it with MSSQL.
If I have 500 records, it is OK (500 * 4 = 2000 parameter markers)
If I have 501 records, it failed (501 * 4 = 2004 > 2000 parameter markers)
All of my transaction attribute is Required.
Is there a way to set JBoss to issue a separate SELECT statement for each getValueObject() call, instead of a first bulk SELECT statement ?
Thanks
C. N.