Re: Fetch Spec Problem - using distict
Re: Fetch Spec Problem - using distict
- Subject: Re: Fetch Spec Problem - using distict
- From: Jonathan Rochkind <email@hidden>
- 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. 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, 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.
In general, I haven't had too much luck with SQL 'distinct' either. 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.
--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.
_______________________________________________
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.