Re: Fetch Spec Problem - using distict
Re: Fetch Spec Problem - using distict
- Subject: Re: Fetch Spec Problem - using distict
- From: "Jonathan Fleming" <email@hidden>
- Date: Tue, 12 Aug 2003 21:51:32 +0100
From: Jonathan Rochkind <email@hidden>
To: "Jonathan Fleming" <email@hidden>,
email@hidden
Subject: Re: Fetch Spec Problem - using distict
Date: Tue, 12 Aug 2003 13:20:32 -0500
You need to turn on SQL debugging---one way is to put
"--DEOAdaptorDebugEnabled=true" in the launch arguments.
I've got -EOAdaptorDebugEnabled=true already enabled but not
-DEOAdaptorDebugEnabled=true
What's the difference or is this a typo
Now you can see exactly what SQL EOF is generating. Then you need to
understand, perhaps by looking in the MS SQL Server 2000 reference,
I was hoping to avoid this as their manuals are pretty big and jumbled, but
looks as if i'll have to :^/
exactly why that SQL is being rejected by the DB, and what it would take
for the db to accept it. Then you need to figure out how to get EOF to
produce that SQL.
Or you could just chuck the whole thing, and forget the distinct. Instead,
fetch all the rows in, but then unique them in memory. One easy way to do
that is:
NSArray listOfObjects; //assume exists, has duplicates
NSMutableSet unique = new NSMutableSet( listOfObjects );
listOfObjects = unique.allObjects();
But you'll lose your sorting there, if you had any, and have to resort in
memory too.
Well, at least I have a workaround option if I get stuck.
In general, I haven't had too much luck with SQL 'distinct' either.
Glad to know it ain't just me
I don't think it quite does what one would want it to, but I haven't
educated myself to understand exactly what SQL distinct is, and how it
works in my db.
I'll look it up and see if I can find out and let you know, but I know you
use SQLServer 7.0, so if I do find a solution it may not work in your case.
Anyhow, I look it up and see if I can find out anyhow.
Jonathan F :^)
--Jonathan
At 06:03 PM 8/12/2003 +0100, Jonathan Fleming wrote:
I'm using MSSQLServer2000 for my database with the default jdbc adaptor
for my connection. Generally everything works fine but I've come to a
point where I need to use a fetch spec with distinct set to true, however,
whenever I do this I get the error listed below and I don't understand
what the ORDER BY bit is trying to tell me as I don't have an ORDER BY in
my model or in that particular component of the app.
This is the fetch spec I am using with disctinc set to false which works
but gives me multiples of one country as you would expect:
// variables used with the COUNTRY popup list
/** @TypeInfo TbAddressWork */
public NSKeyValueCoding aTbAddressWork;
/** @TypeInfo TbAddressWork */
public NSArray aTbAddressWorkList;
// method to return the COUNTRY popup box selection as selected in menu
public void awake() {
super.awake();
// method to return the CATEGORY popup box selection as selected
in menu
if ( aTbAddressWorkList == null ) {
EOEditingContext ec = new EOEditingContext();
EOSortOrdering countryOrder =
EOSortOrdering.sortOrderingWithKey("country",
EOSortOrdering.CompareAscending);
NSArray orderings = new NSArray( new Object [] { countryOrder
} );
EOFetchSpecification fetchSpec = new EOFetchSpecification(
"TbAddressWork", null, orderings );
fetchSpec.setFetchesRawRows( true );
fetchSpec.setUsesDistinct( false );
NSArray keyPaths = new NSArray( new String [] { "country" } );
fetchSpec.setRawRowKeyPaths( keyPaths );
aTbAddressWorkList = ec.objectsWithFetchSpecification(
fetchSpec );
}
}
Application: TMI
Error: com.webobjects.jdbcadaptor.JDBCAdaptorException:
EvaluateExpression failed: : Next exception:SQL State:HY000 -- error code:
145 -- msg: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]ORDER BY
items must appear in the select list if SELECT DISTINCT is specified. Next
exception:SQL State:HY000 -- error code: 16945 -- msg:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The cursor was not
declared. Next exception:SQL Warning: -- error code: 0 -- msg:
[Microsoft][SQLServer 2000 Driver for JDBC]Database changed to TMI Next
exception:SQL Warning: -- error code: 0 -- msg: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Changed database context to 'TMI'. Next
exception:SQL Warning: -- error code: 0 -- msg: [Microsoft][SQLServer 2000
Driver for JDBC]Language changed to us_english Next exception:SQL Warning:
-- error code: 0 -- msg: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Changed language setting to us_english.
Reason: EvaluateExpression failed:
<com.webobjects.jdbcadaptor.MicrosoftPlugIn$MicrosoftExpression: "SELECT
DISTINCT RTRIM(T1.COUNTRY), t0.TB_ADDRESS_ID FROM TB_ADDRESS_WORK t0,
TB_ADDRESS T1 WHERE T1.ADDRESS_TYPE = ? AND t0.TB_ADDRESS_ID =
T1.TB_ADDRESS_ID ORDER BY T1.COUNTRY ASC" withBindings: 1:2(addressType)>:
Next exception:SQL State:HY000 -- error code: 145 -- msg:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]ORDER BY items must
appear in the select list if SELECT DISTINCT is specified. Next
exception:SQL State:HY000 -- error code: 16945 -- msg:
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The cursor was not
declared. Next exception:SQL Warning: -- error code: 0 -- msg:
[Microsoft][SQLServer 2000 Driver for JDBC]Database changed to TMI Next
exception:SQL Warning: -- error code: 0 -- msg: [Microsoft][SQLServer 2000
Driver for JDBC][SQLServer]Changed database context to 'TMI'. Next
exception:SQL Warning: -- error code: 0 -- msg: [Microsoft][SQLServer 2000
Driver for JDBC]Language changed to us_english Next exception:SQL Warning:
-- error code: 0 -- msg: [Microsoft][SQLServer 2000 Driver for
JDBC][SQLServer]Changed language setting to us_english.
In open base this would work fine, so what is the big difference with
MSSQLServer2000?
Regards
Jonathan :^)
_________________________________________________________________
Use MSN Messenger to send music and pics to your friends
http://www.msn.co.uk/messenger
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.
_________________________________________________________________
Stay in touch with absent friends - get MSN Messenger
http://www.msn.co.uk/messenger
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.