CMR and too many parameters in delete sql statement

[pvainio]

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

[pvainio]

Looks like this is a real problem.

http://cvs.sourceforge.net/viewcvs.py/jboss/jboss/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCDeleteRelationsCommand.java?rev=1.10.2.7&view=markup

 //
 // This command needs to be changed to chunk delete commands, because
 // some database have a limit on the number of parameters in a statement.
 //
 public void execute(RelationData relationData)
 {

[aloubyansky]

I saw your patch. Thanks. If you have time to fix it in a proper way, i.e. expose a configuration parameter that would indicate whether to use limit on the number of rows deleted in a single DELETE statement and what the limit is or do not apply any limit, please, do. Maybe you'll also commit it yourself.

[pvainio]

Committed a patch.

Since I have no real experience in JBoss internals (or patching it) I hope it is something like you were asking for.

Br,
Pekka