Colin,
wouldn't setting the join type of your optional relationship to 'left
outer' solve your problem?
I always have my optional to-one relationships configured as 'left
outer' joins. Actually I have code that run occasionally on my models
to ensure relationships are set this way.
P.S. I am under the impression that the SQL you suggest would create
an unqualified join between the two tables.
Pierre
-----Original Message-----
From: webobjects-dev-bounces+pierre.bernard=email@hidden
[mailto:webobjects-dev-bounces+pierre.bernard=email@hidden]On
Behalf Of Colin Clark
Sent: Tuesday, November 09, 2004 5:34 PM
To: James Cicenia
Cc: WebObjects (Group)
Subject: Re: Getting the SQL while running from eclipse
Hi James,
If the other techniques for turning on SQL logging aren't working for
you (they should), you can try setting these debug levels in the
appropriate place... perhaps your Application constructor.
NSLog.debug.setIsEnabled(true);
NSLog.setAllowedDebugLevel(NSLog.DebugLevelInformational);
NSLog.allowDebugLoggingForGroups(NSLog.DebugGroupDatabaseAccess);
NSLog.allowDebugLoggingForGroups(NSLog.DebugGroupSQLGeneration);
NSLog.allowDebugLoggingForGroups(NSLog.DebugGroupEnterpriseObjects);
This will turn on a number of database-related log groups.
It's not really easily possible to manually log out the SQL that a
particular fetch specification will generate. You've got to understand
how the layers of EOF work. Fetch specifications are an object-oriented
representation of a query. At least in principle, they're database and
query-language neutral. In other words, a fetch specification doesn't
know anything about SQL at all, thus you can't simply log the
statements that will ultimately be generated from a particular
specification. The EOAdaptor layer is responsible for converting your
specifications into database-specific SQL queries.
As for your issue of OR qualifiers not returning the results you're
expecting, perhaps the problem involves the fact that you may be
querying across optional to-many relationships? This is, as far as I
know, a bug in EOF. Here are the details:
Reference
2329635
Problem
EOF can generate incorrect SQL for disjunctions of qualifiers where one
of the qualifiers involves an optional relationship.
Description
The form of the SQL generated for an EOOrQualifier is similar to the
SQL generated for an EOAndQualifier, but with "OR" substituted for
"AND". For example, the qualifier "attr1 = v1 OR rel.attr2 = v2" would
generate a SQL WHERE clause something like...
WHERE (ENTITY.ATTR1 = v1 or REL.ATTR2 = v2) AND
REL.PK
= ENTITY.PK
For a mandatory relationship 'rel', there will always
be a match in the REL table and the above SQL WHERE clause will end up
giving the correct result. However, for an optional relationship with
a NULL value, the WHERE clause fails to return any rows even if there
were rows in the ENTITY table that matched the value v1.
The appropriate SQL would be
WHERE ENTITY.ATTR1 = v1 or (REL.ATTR2 = v2 AND REL.PK
= ENTITY.PK)
which would give the correct results for all cases.
Workaround
Separate your original fetch specification into independent fetch
specifications that do not have disjunctions involving optional
relationships, and concatenate the multiple results. Naturally, the
same EO might be returned by multiple fetch specifications so you may
need to filter out duplicates from the concatenated results. For
complicated fetch specifications, it may be more effecient to use a
custom SQL query (see documentation on EOCustomQueryExpressionHintKey
in EODatabaseContext.h). If you choose to use custom SQL and you have
multiple qualifiers across relationships, you should consider using
DISTINCT since there may be multiple ways for one EO to satisfy the
qualifier.
I hope that clarifies things,
Colin
On Monday, November 8, 2004, at 09:23 PM, James Cicenia wrote:
Sorry it doesn't... that was a second attempt and I see its typo, the
myQualifier1 twice..
However, I am certainly not getting back what I can visually see in
my > data.
I can definitely debug sql, if I can only see it. :(
-James
On Nov 8, 2004, at 8:15 PM, Chuck Hill wrote:
Its not your qualifiers, you've just typed it in wrong. Take them
all out.
Add back one. Run. I just tried it. It works, trust me.
Chuck
At 07:56 PM 08/11/2004 -0600, James Cicenia wrote:
nothing... it's set in both places..
Is there a way I can just get the sql my one fetch spec is creating?
I am nesting a large or with an and and the results are not what
I am expecting
EOQualifier myQualifier1;
NSMutableArray args1 = new NSMutableArray();
args1.addObject("projectState.lifeCycleState");
args1.addObject(pLifeCycle);
myQualifier1 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args1);
EOQualifier myQualifier2;
NSMutableArray args2 = new NSMutableArray();
args2.addObject("portfolio");
args2.addObject(portfolio);
myQualifier2 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args2);
EOQualifier myQualifier3;
NSMutableArray args3 = new NSMutableArray();
args3.addObject("rosterMembers.resource.tosuser");
args3.addObject( this);
myQualifier3 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args3);
EOQualifier myQualifier4;
NSMutableArray args4 = new NSMutableArray();
args4.addObject("assignedProjectManager.tosuser");
args4.addObject( this);
myQualifier4 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args4);
EOQualifier myQualifier5;
NSMutableArray args5 = new NSMutableArray();
args5.addObject("sponsor.tosuser");
args5.addObject( this);
myQualifier5 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args5);
EOQualifier myQualifier6;
NSMutableArray args6 = new NSMutableArray();
args6.addObject("submitter.tosuser");
args6.addObject( this);
myQualifier6 = EOQualifier.qualifierWithQualifierFormat("%@ = %@",
args6);
EOOrQualifier orQualifier =
new EOOrQualifier(
new NSArray(
new Object[] {
myQualifier3,
myQualifier4,
myQualifier5,
myQualifier6}));
EOAndQualifier andQualifier =
new EOAndQualifier(
new NSArray(
new Object[] {
myQualifier1,
myQualifier1}));
EOAndQualifier andQualifier1 =
new EOAndQualifier(
new NSArray(
new Object[] {
andQualifier,
orQualifier}));
EOFetchSpecification fs = new
EOFetchSpecification("PortfolioProject",andQualifier1,null);
On Nov 8, 2004, at 7:35 PM, Chuck Hill wrote:
Same way as ever. In the run config add
-EOAdaptorDebugEnabled=true on
either the Arguments or WO tab.
Chuck
At 07:00 PM 08/11/2004 -0600, James Cicenia wrote:
Hello -
I can't for the life of me figure out how to see the generated
SQL >>>>> for
a fetch specification I wrote.
My qualifiers all look good so now I need to see the sql. Is
there >>>>> an
easy way to System.out it?
I have set all the debugs to true in Eclipse's WO and EO
Arguments.
Nothing is being displayed to the console.
-James
--
Chuck Hill email@hidden
Global Village Consulting Inc.
http://www.global-village.net
--
Chuck Hill email@hidden
Global Village Consulting Inc.
http://www.global-village.net
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
This email sent to email@hidden
---
Colin Clark
Dynamic Web and Database Development Lead,
Resource Centre for Academic Technology,
University of Toronto
(416) 946-7592 / email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
email@hidden
This email sent to email@hidden
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************