Re: Getting the SQL while running from eclipse
Re: Getting the SQL while running from eclipse
- Subject: Re: Getting the SQL while running from eclipse
- From: James Cicenia <email@hidden>
- Date: Tue, 9 Nov 2004 14:57:25 -0600
Colin -
Thank you. It was that bug. The work around is what I had implemented
to get around my problem.
As far as logging goes, I am sure it is project Wonder that is causing
the SQL not to be sent to the
console. Since, my query now works I kind of gave up trying to get the
SQL to log.
I will have to review the Wonder list next to fix that problem.
Thanks again,
James
On Nov 9, 2004, at 10:34 AM, Colin Clark wrote:
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:
This email sent to email@hidden