Just a nit comment on Jason's IBO simple-solution:

This works well for US English, but not international solutions.

For instance, in German, the "single character" <s-set> (looks like a Greek
Beta).
The language correct uppercase of this is SS (two characters).
I know of no database that correctly uppercases this character (as the
length of
the output string would be longer than the input string, which is a general
assumption
people make about UPPER(x)). Generally, the result of upper(<s-set>) is
<s-set>.
(and, specifically, that's how all the German drivers supported by InterBase
handle it).

This means that UPPER(<s-set>) != UPPER(SS)
However <s-set> = SS can be expected to be true, using a case-insensitive
meaning for =.

Similarly, in FR_FR locale UPPER(<a-accent-grave>) is A (not
<A-accent-grave))
(as implemented by computers, anyway - the language rules are meaning
sensitive, which
computers generally don't implement <grin>) - meaning that
UPPER(<a-grave>) != UPPER(<A-grave>)
but <a-grave> should = <A-grave> if we ignore case.

(By the way - this points out the need to have ACCENT-Insensitive collation
orders, as well as
case-insensitive orders).

Case Insensitive collation is a simple problem, if you look at US English
only, but becomes
quite complex with multiple languages.

This is why I rejected putting collation INTO the engine when I originally
designed InterBase's
Internationalization -- from experiece I knew we would need something that
could be updated
more frequently than the engine could be updated - and adjusted to meet
individual customer
needs.

Dave

-----Original Message-----
From: Jason Wharton [mailto:jwharton@...]
Sent: Wednesday, March 29, 2000 9:56 AM
To: IB-Architect@onelist.com
Subject: Re: [IB-Architect] Syntax for case insensitive sort


From: "Jason Wharton" <jwharton@...>

> How about:
>
> { <expr> | <position> } [ ASC | DESC ] [CASE | NOCASE]

That would be perfect since this is the exact syntax I'm already using in IB
Objects to deliver a totally case insensitive front end view of the
database.

Since this seems to be a hot topic, I'll take a minute and explain what I
have been doing to address this. The entire IB Objects widget set (controls)
are designed to take case insensitivity into consideration. It's all
built-in so there isn't any messy coding. You just make a global setting in
you app and IBO does all the messy work for you.

At the connection level (meaning once for your whole application) you setup
what I call ColumnAttributes. I have one called NOCASE and in it you can
supply attribute parameters telling additional information about how the
NOCASE should be implemented.

In simple cases IBO just uses the UPPER() operator to wipe out case
sensitivity if no parameters are supplied. If it is a large table you are
dealing with obviously you still want to be able to benefit from an index so
I'll describe this most involved case. This requires that you have a
"shadow" column in your database. It is simply the uppercase equivalent of
the source column. Is all it requires is to be initially populated and then
add two triggers to maintain it for you. Then, you also put an INDEX on the
column so that you get the benefits when searching, ordering, etc.

Here are some sample entries in the ColumnAttributes property:

LASTNAME=NOCASE=LASTNAME_UC;NOTRAILING
FIRSTNAME=NOCASE=FIRSTNAME_UC;NOTRAILING
COMPANY=NOCASE=COMPANY_UC;NOTRAILING

This is assuming that the NOCASE equivalent columns in the database are
named the same with the _UC postfix on them (my personal convention).
NOTRAILING is a flag that tells IBO to apply search criteria entered into
the controls in a way that will ignore trailing characters (uses STARTING
instead of =)

By making these settings you go on using the original column as if the
NOCASE columns were not there in your application. IBO will automatically
substitue everywhere necessary in the SQL sent to the server to deliver the
case insensitive behavior.

This includes use of the dssSearch mode (QBE-like) for all controls,
OrderingItems, OrderingLinks, IndexFieldNames, Filter, Lookup(), Locate(),
FindFirst(), FindNext(), FindPrior(), FindLast(), etc.

If you make this change then it will be super easy for IBO to accomodate
this. I'll just have to make it act slightly differently by providing a new
parameter to the NOCASE attribute and change a line of code or two... Then,
people will have all of the same benefits as before and not have to have
duplicate columns in their database. Cheers!!!

So, when I write an "Access-like" front end tool, IBO will be a great tool
suite to do it in... Except I'll do it right when I do it... <g> Linux here
we come!

FWIW,
Jason Wharton
InterBase Developer Initiative
jwharton@...

InterBase will be the database of the new millennium.



------------------------------------------------------------------------
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954351843/
------------------------------------------------------------------------

To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com