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.
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3864094#3864094
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3864094
-------------------------------------------------------
This SF.Net email is sponsored by: IntelliVIEW -- Interactive Reporting
Tool for open source databases. Create drag-&-drop reports. Save time
by over 75%! Publish reports on the web. Export to DOC, XLS, RTF, etc.
Download a FREE copy at http://www.intelliview.com/go/osdn_nl
_______________________________________________
JBoss-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/jboss-user
