uh, uh, little updtate to my previous post.
I tried to patch OJB to support Sybase IN limitations, the problem is that sybase considers an IN statement as a sequence of ORs and there is a maximum of 250 ORs/ANDs for a single statement. This means that the sum of the elements in all INs plus all ORs/ANDs must be lower or equal than 250. So my idea was to split the main query in several queries so that the total number of logical operators per query will remain lower than (SQLInLimit) 250, however if there is only one IN statement or several OR 'connected' INs this is not a problem. But what about AND 'connected' INs or several INs in joined tables? I see no solution...
Maybe it is not a bad idea to put a little commentary in the doc about this issue...
Has somebody an idea if it is possible to increment this limit in Sybase (how?)? Or a workaround other than using temporary tables?
thx bye danilo
Hi all,
I noticed a problem with the IN clause...
As shown in the 'Query' documentation if the SqlInLimit property is exceeded a Criteria will be split in a OR concatenated list of INs. That's really cool, however this works in Oracle (however with a limitiation of 1000 ORs :) ), but in the (@[EMAIL PROTECTED]) Sybase this will not work, because independently if the INs are split or not, the total limit will remain 250.
I got a look to the code (RC4) and it is really done so for every db.
Prefetched ralationships also split IN criterias, however they generate different Queries and put all results together before returning them.
Now the first (Criteria) variant offers better performance, however is useless on Sybase.
The second one (prefetched relationship) offers less performance but would also allow Sybase to handle more than 250 IN elements, however managing the StartAtIndex and EndAtIndex will need some more effort, involving the use of COUNT() operations on each query that has been split furtherly reducing performance :(
It could be useful to introduce a property flag like 'SplitQueriesOnSqlInLimit' to force OJB splitting IN Criterias in several queries for databases that need it.
Or do you have a better idea?
I'd like to patch it myself, however I am already under heavy time pressure on our current project, so it would be great if somebody of the good guys could get a look at it. ;)
let me know, bye danilo
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
