[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
Uhu. Aha. bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836876#3836876 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836876 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
Thanks, hbaxmann, for the offer to further analyze the issue. Since the issue is resolved, I consider its relevance reduced to being an example of the greater problem. I'd love to spend time using examples to go over the theoretical possibilities, but really do have a lot on my plate today. For those following this, the current status on the JSR follows. JSR 220 (http://jcp.org/en/jsr/detail?id=220) is developing EJB 3.0, which includes improvements to EJB-QL. The JSR is scheduled to release either an EARLY or PUBLIC DRAFT in June 2004, so will hopefully be releasing it soon. Due to the confidentiallity agreemant JCP members sign (JSPA para 9), any current discussions and draft copy is "hush hush" until they officially release an EARLY or PUBLIC DRAFT. Although this spec promises to focus on usability and productivity of developers, the spec description does say it is not limitted to that, and even appears to emphasize the need to improve EJB-QL: anonymous wrote : "Enhancements to container-managed persistence and EJB QL to provide greater usability and to facilitate the development of frameworks based on container-managed persistence." | I believe their creation of the early draft could use even more public transparancy than JCP 2.6 provides (http://jcp.org/en/procedures/jcp2). Since JSR 220 was started with JCP 2.5, its not clear if an EARLY DRAFT was already completed that might have been public if it began under 2.6. It's under 2.6 now, which is fairly new (http://java.sun.com/developer/technicalArticles/jcp26/). Yet, I think for important JSRs like this one, even JCP 2.6 is too restrictive since the non-member public at-large cannot review or discuss drafts until they are stamped ready for public review. This is why you don't see anyone from JBoss posting on this thread. Let's hope we can publicly discuss it in June. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836850#3836850 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836850 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
Thanks for your kind reply :))) Mhhhm, i do not see an end here either. The Java vs. DB NULL problem is an chicken-and-egg problem. The NULL in the DB is not the Java NULL, so it must be converted in both directions. That for you have to test if it is NULL. Circle closed. Do you have the full control over the Table and Relation layout? If you provide more detailed information about what you are modeling, I am sure there is a way like the address splitting approach. Not the one-size-fits-it-all, but here we have a special case, which should be solved in a general manner. Have done it the last 25 years ;-) bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836800#3836800 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836800 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
Thanks. Then I apologize for sounding revendicative. Never want to draw upon the objective truth, know that this is not existing. My truth is simply my truth, not more - but not less. bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836628#3836628 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836628 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
revendicative means "claiming your truth at the expense of those of others" This forum is for helping each other to make everyone find its truth (means the way where its application works). Blaming J2EE, JBoss, ER designers or ER models got nothing to do with this forum. So, finallly, make a choice between a DynamicSQL statement or adding views, or whatever belongs to Erik. Nothing we can do more than that. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836612#3836612 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836612 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
I apologize. Let's bring this social NPE to an end: I apologize too for sounding anything else than helping. I neither know what "revendicative" in German is [babelfish and dict.leo.org doesn't help here] nor does I know _how_ to write or say something that it is sounding condescending in english - in German, you could be very, very sure, i?ll know. I want never ever accusing people - IMNSHO I accusing the design of RDBMSes, because I, like many other out here, make money with healing this sucking designs by implementing J2EE Apps on top of it, instead of repair this crap - but it is definitely no fun. And even I will have fun with my day-to-day work, seriously. So at last I have to apologize to not be an native english speaker. Nice. And, yes, here I am still a newbeee. As shown. My fault. OTOH it makes me wondering, that at most young people are afraid of beeing criticised. Often there is no understanding for the difference of saying: "Your ER model sucks" and "You sucks". How less could one stand on his own if he feels attacked if one attacks his ER model? And how arrogant and ignorant ? Ok, let's take this case straight: Precondition: We have one table in a relational DB with four colums: ID NameCity Street Zip ID is the PK Name is a NOT NULL column City is a nullable column Street is a nullable column Zip is a nullable column I understand the problem as follows: ID NameCity Street Zip 1 Miller Frisco 2 Frank Way 1 3 PaulFrisco 1 4 Smith Ave. Simple case Query: Give me all Name, where the Zip is 1 (Zip is $1) or Zip is not existing ($1 is null) This is a self-outer-join on the table. 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 ?business key? - a concept which does not exist on DB level - consists now on City, Street and Zip. The query should deliver all three columns, regardless if they are NULL or not. Right so far ? Problem Query: Give me all Name where Zip is 1 or not existent and give me all Name where City is Frisco or not existent. So expected result should show: Name Miller Frank Paul What I would suggest in this case is to build a Frisco_view and Zip1_view: Beforehand: This is a workaround for not changing the underlying DB-model, otherwise there should be a ID_CityTable and a ID_ZipTable - there should than performed outer joins with the TheTable, but this is DB and DB-version (oracle I know here) dependend, because the outer joins (left, right, full) are implemented differently in syntax and function by the vendors. Now the ugly workaround: Create View Frisco_view as Select ID, City from TheTable where City=Frisco or City IS NULL; Create View Zip1_view as Select ID, Zip from TheTable where Zip=1 or Zip IS NULL; Create View MultipleSelfOuterJoin_Frisco_1_Query as Select tt.ID, tt.Name, zv.Zip, fv.City from TheTable tt, Frisco_view fv, Zip1_view zv where (tt.ID = fv.ID) and (tt.ID = zv) and (fv.ID = zv.ID) Select Name from MultipleSelfOuterJoin_Frisco_1_Query; Depending on the RDBMS, it might happen that the views beeing materialized by the optimizer. ... and, yes, there have DDL statements dynamically, at runtime, to be performed. It is a workaround. Ref: Joe Celko?s SQL Puzzles & Answers, Morgan Kaufmann 1997, ISBN1-55860-453-7; Puzzle 14: Telephone BTW: the Subject itself is a oxymoron: CMR should provide results where something is existent _and_ not existent according to SQL. Most OR tools are struggling with this structural weakness of the relational world. bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836604#3836604 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836604 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
ironbird: You're right. And I do apologize for coming off as condescening. Believe me, though, it has nothing to do with his being a newbie. I am normally very supportive of people learning something new. His tone was condescending, so I felt he needed to hear himself in context. There's a difference between someone that is new, and someone that just learned something in a class and think they know it all and can log onto a forum to insult everyone that think exactly like he thinks. I figured a gentle correction on a JBoss forum was a better place for him to learn than on a job. Unfortunately, I didn't know how to do that without coming off as condescending. I would have probably dismissed what he said outright, if it wasn't for his display of lack of ettiquette: - "your ER model sucks" - "because in this kind of usage your Entity design simply lacks" - "dont blame the J2EE for not resolving bad RDBMS design" This is in addition to outright illogical statements he made, his assumption that he's completely right without even understanding the problem, his inability to listen to why I chose certain options, like avoiding dynamic SQL or direct JDBC, and the lack of inquiry into the actual table design and purpose. By the way, the table which he thinks needs to be normalized with views only consists of a primary key (generated sequence number), a composite business key, and a single column for data. It's ok if his logic is incomplete, but he should try not to condescendingly insult people when he displays his lack of understanding of other people's problems. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836551#3836551 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836551 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
You are too much revendicative to exchange with both of you. When you want to justify yourself so much, it's suspect for me. Continue to consider everyone as newbie while you seems to stand yourself on some cloud, nobody's care. Nice to meet you too, but not too much ! View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836538#3836538 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836538 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
nice to meet you, too bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836534#3836534 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836534 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
hbaxmann: You are clearly learning relational theory in an academic setting. What school are you attending? What is your major? For one, no one completely normalizes data in the real world, and by real world, I mean business applications. When someone does, it's clear it's a college student or grad that hasn't had the experience beyond what he learned in school. Null columns exist because the user is not always required to provide them. Like you said, it gives us the ability to detect that there is no data for it. It's a very nice concept and works really well in relational databases. There's no normalization problem because you have optional columns. If I wasn't using EJB-QL, but DynamicSQL, as someone suggested, or direct JDBC, I wouldn't have a problem at all. Using the example of customer registration, here are columns I might make optional because I wanted customers to register even if they didn't have or want to provide the information: - All address information. Definately second address line if I chose to require address information. - Second email address. - Additional phone numbers. Now, all this info could be normalized, but with an OO front-end, the benefits are near nill. Take the phone number, for instance. Phone numbers can be handled in a standard way via application objects. This mutes the benefits of normalization. However, even if I normalized it, I would still have optional foreign keys... and thus nulls. This would not resolve the problem EJB-QL has, as it applies to relationships as well as regular columns. Optional is optional, whether they are regular columns or relationships. Optionality is defined by the business requirements. If the phone number is not required information, then either a column for phone number data or a foreign key can be null. As for ER modeling, I've been developing relational applications since the early 80s prior to SQL's definition in 89. I've read quite a few books over those years, including the adademic books you're reading now, as I have a degree in IS. I've used ERwin, Oracle Designer, Rational Rose and other tools for ER modeling. Last year, I lead the effort to create a 150+ table data model for a large commercial payroll company for DB2. We used Rational Rose to create an ER model. When that was complete, we generated the data model, and then revamped it some more. Then we generated it in DB2 migrated an entire commercial payroll system to use it. It was successful. We did a lot of normalization of the data coming in, which came from the mainframe in over 100 formats. Creating a practical normalized model was one of our goals. But knowing when to normalize and when not to normalize isn't something you can learn in an academic book on relational theory. It's something you learn by repeatedly doing it in the real world for many years. Building data models for large businesses over and over again is the real world. Learning relational theory in a class is not. Using views to try to normalize a single table is rediculous in this case, both in terms of practability and the end result's ability to actually solve the EJB-QL issue. Even if it did somehow miraculously help, it would run completely counter to why I'm using CMP, and the database vendor independence required by the application. Please read comments I made earlier on why I am using CMP. It's exciting to see you learn relational theory. Clearly, you are grasping the concepts your class is teaching you. However, I highly recommend you try to learn from others who have applied it in business environments, and give them the benefit of the doubt until you have a chance to learn the practicality of some of the academic ideas when developing large business applications. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836518#3836518 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836518 --- 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_id149&alloc_id66&op=click ___ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
anonymous wrote : This isn't a relational issue. Right, because in this kind of usage your Entity design simply lacks. This solution is a workaround. In most cases one is not able to tidy up, normalize an already implemented ER model. The one and only solution is to have a 3rd NF ER model - most times impractical. Here comes the DB views for select statements on _denormalized_entities_ (ER entities, not EJB ;) in the mind. In the case of your NULL columns, your ER model is not even in the 1st NF. NULL means 'column does not exist here'. NULL is is a 'workaround' in RDBMSes for not to force to normalize till the 5th NF everywhere. Ok, 3rd NF ;) anonymous wrote : Huh?!? More than half a table's columns are often optional. Reading may be of help: I wrote '_significant_ '. anonymous wrote : Any column can be filtered via a parameterized where clause in the real world. I know, but you are not working in a real world, you are talking about a RDBMS. Exactly the fields in a where clause are kind of keys, if you are happy with this or not. Even in a single table query. anonymous wrote : How is having multiple optional columns a bad model? | See above regarding _significant_. This is not what I am trying to explain: Building relations, (if clauses like where statements) even between the same table based on unnormalized data is not possible. Real world: ... in most cases. What i suggest is to normalize your model for your queries via DB views and than use EJB. Dont blame the J2EE for not resolving bad RDBMS design _and_usage_. Buy a book about ER modelling and implementing this model in RDBMS and a pump gun for the DB designer (most often the noise is sufficient). bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836395#3836395 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836395 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
hbaxmann: anonymous wrote : solution: build a db view per optional field, null-'key' rows will vanish, not the denormalized table This isn't a relational issue. The query I mentioned with 4 columns ironically has no relationships whatsoever. However, it does have 4 optional columns I need to filter on. They happen to be parts of a business key, but that's arbitrary. They could be any optional column that I need to filter on. Remember, this issue is I'm passing a parameter and pulling values that match that paramter. This isn't complext, and has nothing to do with normalization because it has nothing to do with joins or relationships. anonymous wrote : if your app rely on more then one _significant_ optional field - your ER model sucks Huh?!? More than half a table's columns are often optional. Any column can be filtered via a parameterized where clause in the real world. These are common business requirements. Just because a customer table does require an email address doesn't mean the user shouldn't be able to query based on the email address. Just because an email address is optional doesn't mean that every other piece of customer information has to be mandatory. How is having multiple optional columns a bad model? hbaxmann: I think you understand where I'm coming from. J2EE has some great enterprise quality standards. But other standards, such as JAAS, don't fit the bill for "enterprise" readiness, IMHO. While I know CMP has is a bit short on some critical areas, such as its support for optional columns in a query where the parameter can be null, I'm investing in CMP for the long run vision. Thus, I'm taking the time to try to find the best-of-breed solutions that others are using to deal with the bump along the way; and, in the absense of such, trying to sense the priorities of others and how it's driving the improvement of the standard. In other words, I need to understand the priorities the CMP developers are sensing so I can bet on likely improvements. anonymous wrote : And your query, even implemented in SQL, is 100% compatible with all the databases because SQL address the database compatibility before java and J2EE. Wouldn't that be nice if it were true. To name a few differences: the formatting of a date in the where clause, unique identifier generators and the syntax of outter joins. I wrote one system that ran in both SQL Server and Oracle. Last year, I lead the migration of a J2EE application to use a new DB2 data model with 150+ tables while still working 100% with its Oracle 90+ table predecessor. To complicate things, we had to use stored procedures for all data communication for DB2 (dumb corporate "security" standard.) The calculation code (business logic,) which never stopped being fully functional during the migration, was 100% independent of the database vendor, but it wasn't SQL that made it possible. We used a Data Accessor pattern. There are two problems with SQL: The ANSI 89 and 92 standards that became popular did not address all the needs (e.g., dates in the WHERE clause). The second problem is that it is relational, making it difficult to migrate to object database technologies. Thus... CMP, and the current trend of using object-relational to implement it. CMP offers a migration path if you believe it will one day support object database implementation. If Sun's Java certification has anything that requires CMP to use a relational database, then they are not testing the standard, because nothing in the standard requires a relational database for implementation. If the relational database requirements have drifted into it, then it's probably just because every vendor out there implemented it with a relational model. Thus, when vendors start to implement it with object databases, Sun will be obligated to remove any relational specific requirements that slipped into their test suite. However, this is all conjecture. Do we even know that Sun's test suite has relational database requirements unwittingly embedded into it? It's Sun's own website that states that CMP does not need to be implemented using a relational database. Here's one instance: anonymous wrote : "It is unlikely that the EJB expert group will accept any form of Java transparent persistence as an EJB requirement; however, some J2EE vendors may well use JDO as the underlying implementation of CMP." - http://java.sun.com/products/jdo/JDOCMPFAQ.html View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836353#3836353 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836353 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
hbaxmann: anonymous wrote : solution: build a db view per optional field, null-'key' rows will vanish, not the denormalized table This isn't a relational issue. The query I mentioned with 4 columns ironically has no relationships whatsoever. However, it does have 4 optional columns I need to filter on. They happen to be parts of a business key, but that's arbitrary. They could be any optional column that I need to filter on. Remember, this issue is I'm passing a parameter and pulling values that match that paramter. This isn't complext, and has nothing to do with normalization because it has nothing to do with joins or relationships. anonymous wrote : if your app rely on more then one _significant_ optional field - your ER model sucks Huh?!? More than half a table's columns are often optional. Any column can be filtered via a parameterized where clause in the real world. These are common business requirements. Just because a customer table does require an email address doesn't mean the user shouldn't be able to query based on the email address. Just because an email address is optional doesn't mean that every other piece of customer information has to be mandatory. How is having multiple optional columns a bad model? anonymous wrote : And your query, even implemented in SQL, is 100% compatible with all the databases because SQL address the database compatibility before java and J2EE. Wouldn't that be nice if it were true. To name a few differences: the formatting of a date in the where clause, unique identifier generators and the syntax of outter joins. I wrote one system that ran in both SQL Server and Oracle. Last year, I lead the migration of a J2EE application to use a new DB2 data model with 150+ tables while still working 100% with its Oracle 90+ table predecessor. To complicate things, we had to use stored procedures for all data communication for DB2 (dumb "security" standard .) The calculation code, which never stopped being fully functional during the entire migration, was 100% independent of the database vendor, but it it wasn't SQL that made it possible. We used a Data Accessor pattern. There are two two problems with SQL: The ANSI 89 and 92 standards that became popular did not address all the needs (e.g., dates in the WHERE clause). The second problem is that it is relational, making it difficult to migrate to object database technologies. Thus... CMP, and the current trend of using object-relational to implement it. If Sun's Java certification has anything that requires CMP to use a relational database, then they are not testing the standard, because nothing in the standard requires a relational database for implementation. If they relational database requirements have drifted into it, then it's probably just because every vendor out there implemented it with a relational model. Thus, when vendors start to implement it with object databases as well, Sun will be obligated to remove any relational specific requirements that slipped into their test suite. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836352#3836352 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836352 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
IMHO there is no big, but fundamental problem: - if your app rely on more then one _significant_ optional field - your ER model sucks - NULL compared to anything gives false, even NULL=NULL solution: build a db view per optional field, null-'key' rows will vanish, not the denormalized table what you are trying to accomplish is a case clause in SQL, as we know: every case statement could be outfactored better by working on your model ;-) bax View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836342#3836342 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836342 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
anonymous wrote : | Because I am using container managed persistence (CMP) and EJB-QL is a the Query Language? | EJB-QL query is for persistence with additional features, not complex queries. I agree with you that the spec is not adapted with professional applications. I work in industry and many times I cannot rely on the spec OR THE CONTAINER to satisfy my customers. anonymous wrote : | To obtain database vendor indepedence. The application is 100% compatible today with MySQL, DB2, Oracle, SQL Server and any other database that provides a compliant JDBC driver and a configuration in the J2EE server. The differences between them that that JDBC doesn't handle, JBoss handles through CMP configurations. | Just for persistence, you means. And your query, even implemented in SQL, is 100% compatible with all the databases because SQL address the database compatibility before java and J2EE. anonymous wrote : | To develop using a Java database interface that's independent of the underying database technology. JBoss and other vendors just happened to be using JDBC/relational to implement CMP today because relational databases are still popular. But there's nothing in the spec to prevent them from adding an object database engine for CMP down the road. I won't have to change a single line of code to be able to use object databases when this happens, since I never use SQL in the application. | No its false, because SUN impose its certification and any vendor must comply if he want to keep its customers. But do you experiment vendor compatibility ? I do. Normally, the "Container Managed" means that you can migrate from one vendor to another. And it's often difficult, and I can tell you that you spend more time to be 100% vendor compatible that 100% database compatible, even if the containers and EJB-QL are limited. You have always to separate the system level (the container) from the business level (you). The frontier is not always where you want ! View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836328#3836328 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836328 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
anonymous wrote : | Why do you want the container do the job for you ? | Because I am using container managed persistence (CMP) and EJB-QL is a the Query Language? Are you asking why I'm using container managed persistence? | - To obtain database vendor indepedence. The application is 100% compatible today with MySQL, DB2, Oracle, SQL Server and any other database that provides a compliant JDBC driver and a configuration in the J2EE server. The differences between them that that JDBC doesn't handle, JBoss handles through CMP configurations. | | | - To develop using a Java database interface that's independent of the underying database technology. JBoss and other vendors just happened to be using JDBC/relational to implement CMP today because relational databases are still popular. But there's nothing in the spec to prevent them from adding an object database engine for CMP down the road. I won't have to change a single line of code to be able to use object databases when this happens, since I never use SQL in the application. | View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836324#3836324 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836324 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
You can do what you want with DynamicSQL, which is specific to JBoss. But the J2EE spec do not address your needs. Even in any language (PL-SQL, T-SQL, C++ or whatever), you need to build dynamically your SQL string to do what you want. Why do you want the container do the job for you ? View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836294#3836294 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836294 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
anonymous wrote : Path expression navigability is composed using ?inner join?
semantics. That is, if the value of a non-terminal cmr-field in the path expression is
null, the path is considered to have no value, and does not participate in the
determination of the result.
|
If that's the standard, then that is just assinine. Why should I have to choose
between 16 EJB-QL queries or "fake" nulls?!? Imagine 8 optional parameters. That
would require 256 EJB-QL queries, rendering "fake" nulls virtually inevitable!
If I were generating the SQL myself, I would do this to generate the component of the
WHERE clause for one comparison (in psuedo code):
| if ({the parameter is null})
| whereComponent = field + " IS NULL ";
| else
| whereComponent = field + " = " + parameter;
|
If this violates the standard, then the standard is overdue for a change. I heard
that UNIONS were coming out in a future release. That's a cheesy solution to me.
There's no reason why it can't be a simple and fast query. UNIONS are slow.
I'm not blaming JBoss at all for this. I blaim the JCP. I love J2EE. It has a lot
of good well thought out standards. But this is not one of them.
View the original post :
http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836286#3836286
Reply to the post :
http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836286
---
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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
The problem with that query is that it will return rows where c.customer IS NULL but the parameter ?1 is not null. You only want rows where c.customer is null and ?1 is also null. The equals doesn't work, and IS NULL only tests one side of the equation. You can't apply it to the parameter to be sure both the parameter and c.customer are null at the same time. I've worked around this by creating multiple queries to test for the various scenarios, and then aggregating the results. That's extremely inefficient, but the runtime inefficiency is not the biggest problem. The problem is that the number of queries you need is the number of optional parameters you have to the power of 2. For instance, if you have two optional parameters, then you need 4 queries to cover all possible combinantions of null and not null values. However, I'm asking this question today because I now had a case where the business key has 4 optional columns. This would require 16 queries, and I'm not going there. To get around this for now, I created a "dummy null" value, and have a lot at points to convert it back and forth between a real null. This was at least better than creating 16 EJB-QL queries and creating a lot of Java code to tie them together. View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836279#3836279 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836279 --- 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
[JBoss-user] [Persistence & CMP/JBoss] - Re: Nulls in CMR where clause
??? What do you say ? You can get null values rows in EJB-QL testing by IS NULL. For example the EJB-QL query | * @ejb.finder | * view-type="remote" | * signature="java.util.Collection findCustomers(Pharma_EJB.interfaces.Pharma_Customer customer)" | * query="SELECT DISTINCT OBJECT(c) FROM Pharma_User c WHERE c.customer IS NULL OR c.customer=?1" | generates the following SQL | Executing SQL: SELECT DSITINCT t0_c.IND_ID FROM TD_USER t0_c, TD_CUSTOMER t1_c_customer WHERE (t0_c.CLI_ID IS NULL) OR ((t1_c_customer.CLI_ID=?) AND t0_c.CLI_ID=t1_c_customerCLI_ID) | Where is the problem ? The spec says explicitly that the null values do not participate in the relation, so you must test it with IS NULL explicitly (snippet from the spec): anonymous wrote : | Path expression navigability is composed using ?inner join? semantics. That is, if the value of a non-terminal | cmr-field in the path expression is null, the path is considered to have no value, and does not participate | in the determination of the result. | View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3836249#3836249 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3836249 --- 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
