[sadie]

This is slightly off topic, but I'm desperate. If you lot could help, it would make ALL the difference.

We have a SQL database, and a table of a column of it is sorted using intelligent English sorting (EN_UK, EN_US, whatever works best) rather than flat ASCII sorting.

I need, for reasons that would take months to explain and probably be a breach of company security, to duplicate this sort order in Java, preferably using a Comparator or Comparable interface. I can't simply be intelligent English sorting - it has to be exactly the same as our database is using.

There's lots of documentation about how to set the database to use specific character sets and sort orders (COLLATE orders, it calls them) but nothing that I can find that actually says what that sort order is.

Does anybody know where I can find the precise details? Please?

[sadie]

I pick the easy ones, don't I?

[larkost]

If you need the exact same behavior, why not simply connect to the database, and either re-fetch the data and store the order as the results come out, or create a temporary table, import your data, then re-export, collapsing the temporary table. How to actually do this is usually dependent on your database (some actually have special table types to do this sort of thing).

Your only other option is to get the actual code that your database uses for this purpose, and re-implement it in Java (it is probably in C). This is possible if you are using a open-source database such as MySQL or PostgreSQL, but next to impossible if you are using a commercial database (MS-SQL, OpenBase, Oracle, etc..).

[sadie]

larkost was of the opinion:
<STRONG>why not simply connect to the database, and either re-fetch the data and store the order as the results come out...</STRONG>

Because:

• The database is a long way away
• We're viewing a filtered and sorted version of the table rather than the real thing - the database doesn't bother to cache this
• The data is too smegging huge to cache
• If every client connects to the database to refetch data for every sort operation, the DB server will be flooded

Sorry if I sound ungrateful or anything, but I've been over this. I need to recalculate it the same - nothing else will work.

<STRONG>Your only other option is to get the actual code that your database uses for this purpose, and re-implement it in Java (it is probably in C). This is possible if you are using a open-source database such as MySQL or PostgreSQL, but next to impossible if you are using a commercial database (MS-SQL, OpenBase, Oracle, etc..).</STRONG>

It's Borland Interbase. Sounds proprietary to me.

Is there any standard on what these sort orders should be? Is there a central body that's responsible for saying "French Canadian sorting should be this"? If i use Java's existing Locale sorting, will that match up to what the database is using?

[int69h]

You are in luck. Borland provides the source for Interbase here.

[ 10-26-2001: Message edited by: int69h ]