3 Replies Latest reply on Dec 16, 2004 12:32 PM by Pekka Vainio

    CMR and too many parameters in delete sql statement

    Pekka Vainio Newbie

      Hi,

      I'm sure this issue has been adressed somewhere, but I just could not find it.

      I have a problem with Sybase and CMR. Sybase does not like to have more than 300 parameters in one SQL statement. When I delete many CMR relations inside one transaction it fails because generated delete statement has too many parameters.

      For example code like this:

      userTransaction.begin();
      UserLocal ul = getUser();
      GroupLocal parent = getParentGroup();
      Iterator gIter=parent.getChildGroups().iterator();
      while (gIter.hasNext()) {
       GroupLocal gl=(GroupLocal) gIter.next();
       ul.getGroups().remove(gl);
      }
      userTransaction.commit();
      


      Generated SQL statement is something like "DELETE FROM user_group_member WHERE (fk_user=? AND fk_group=?) OR (fk_user=? AND fk_group=?) OR (fk_user=? AND fk_group=?) OR ..." and so on.

      Is there a way to limit the amount of parameters in one DELETE statement and run multiple smaller statements instead of one big statement?

      Br,
      Pekka