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


      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:

      UserLocal ul = getUser();
      GroupLocal parent = getParentGroup();
      Iterator gIter=parent.getChildGroups().iterator();
      while (gIter.hasNext()) {
       GroupLocal gl=(GroupLocal) gIter.next();

      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?