hbaxmann: Thank you for posting the message with kinder English and approaching it technically without commenting on a person's skill levels. I apologize if I was harsh on your choice of words, or use of English.
I'll respond to the technical notions in your post now as best I can. anonymous wrote : EJBQL: SELECT DISTINCT OBJECT(t) FROM TheTable t WHERE t.Zip IS NULL OR t.Zip =?1 | | The ?table.col = ?1 OR (table.col IS NULL AND null=?1)? does not work, because there will occur a short-circuit to ?table.col = ?1?, because of ?NULL=anything? is always false. | The problem with that EBJ-QL query isn't that ?NULL=anything? is always false, but that it will return all nulls (t.Zip IS NULL OR ...) even when ?1 isn't null. What would be ideal is: | EJBQL: SELECT DISTINCT OBJECT(t) FROM TheTable t | WHERE (t.Zip IS NULL AND ?1 IS NULL) OR t.Zip =?1 | but ?1 IS NULL is not an option in EJB-QL today. The problem currently with EJB-QL is that you cannot test if the parameter itself is null. The original question I had is when will it or an equivilant be an option, since testing for null parameters has never been an issue with SQL development before? Although EJB-QL chose to adhere largely to SQL syntax, I think that to solve the problem where it is clearly deficient in contrast to SQL, and particularly dynamic SQL where you combine code logic with resulting SQL output, it could improve by doing things differently. In Java, variable=null does return true if the variable is null. Why not permit it in EJB-QL? The requirements for this entity are: - Reusable by all applications - Works with all database vendors - Works with all J2EE vendors - Very high performance - Extremely simple Views are ruled out for two reasons. One, they are an unacceptable hit on performance. Two, they are not supported by all database vendors. Now, let's say that views were acceptable. Using your example, 16 views would be necessary in this case. The reality is there are 4 optional columns in the business key. Any of them can be null. The keys are not related to each other. Any conceptual relationships that might occur inthe applications is arbitrary. Using the samle you posted, meaning that views didn't have unions, you would have to have 16 individual views just for this one query to handle every possible combination of null / not-null values. Contrast this to my current solution of using dummy null values. It's working today. It doesn't have the high performance cost of views, and works with all database vendors. It is seemless to the applications, since they can still use nulls, and will still get back nulls, never knowing that a dummy null value was substituted. Thus, this entity became one of a growing list of examples since EJB-QL came out of problems with nulls in the queries. But, using views clearly are not in line with the requirements. The question I originally asked is what was the progress of JBoss or J2EE on this issue? The question was partially answered when someone said you could do it today with DynamicSQL. That last time I check, DynamicSQL couldn't do it, ironbird essentially answered my question as to where JBoss has progressed on it today. The only question I still have is where is J2EE and the JSR's on this? Thank you hbaxmann for helping. Your proposal to use views to denormalize certain can work and has merit. It's just not the option for the problem I have. And thank you ironbird for the DynamicSQL info. Although this entity needs to work with all J2EE vendors since it's in a foundation class library, I'll probably use DynamicSQL until a JSR fixees this for entities that can afford some vendor dependency. Happy JBoss development! View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836731#3836731 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836731 ------------------------------------------------------- This SF.Net email is sponsored by: Oracle 10g Get certified on the hottest thing ever to hit the market... Oracle 10g. Take an Oracle 10g class now, and we'll give you the exam FREE. http://ads.osdn.com/?ad_id=3149&alloc_id=8166&op=click _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
