0 Replies Latest reply on Jan 28, 2005 8:57 AM by tsangcn

    How to change the findAll behavior ?

    tsangcn

      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.