Subject Re: [IB-Architect] Syntax for case insensitive sort
Author Jason Wharton
> 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

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:


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!

Jason Wharton
InterBase Developer Initiative

InterBase will be the database of the new millennium.