Subject RE: [IBO] TIB_LookupCombo case insensitive inc search
Author Brian Dunstan
Helen,

thanks for your reply. I understand the advantage of having a proxy column
of UPPER case strings for searching, but it is hardly justified for short
list of a dozen or so rows.

The IBO help on TIB_CustomCombo says:
"It is case sensitive by default but if the column the lookup dataset is
currently ordered by has a NOCASE entry in the column attributes then the
incremental searching will be made case insensitive for you automatically."

And Jason has said:
"If you are pulling all the records to the client then there is no
appreciable benefit to having a dedicated column for the uppercase
equivalent. IBO will perform it in memory as you expect."

So I expect LookupCombo to work without ANY regard to case with the NOCASE
CollumnAttribute.

> The way the NOCASE feature works is this:
>
> For the MIXED CASE column on which you want the NOCASE
> feature to work, you
> can define (in your database) a proxy column, populated by
> trigger on the
> main column, which stores UPPER(TheMixedCasedColumnValue).
> You would place
> a non-unique index on this column.
>
> When you come to set up properties for the main (mixed case)
> column, you
> check NOCASE and enter the column name of the column containing the
> trigger-populated values. The match sought is
>
> where ProxyColumn = UPPER(SearchValue)
>
> As Jason said:
>
> > >
> > > By leaving it out IBO will use the UPPER( ) where
> > > appropriate. Just keep in
> > > mind that you lose the benefit of having an index so for
> > > larger datasets
> > > this is undesirable.
>
> Meaning that, if the search is forced to be done on the
> mixed-case column
> itself, the query to match the NOCASE criterion will be
>
> WHERE UPPER(SearchedColumn) = UPPER(SearchValue).


Why would you need UPPER(SearchedColumn) if you have stored an upper case
column?

> That is, UPPERing mixed case SearchedColumn creates an
> expression, thus
> denying you the benefit of any index the column has.
>
> In the case of the sample, you are "double-blinding" it, by
> adding a mixed
> case value to the proxy (otherwise all uppercase) set.

No, I am not double blinding it. In this example there is no upper case
column in the DB. All the other values are mixed case and IBO is converting
the search value to upper case. The issue appears to be that it doesn't work
when the column has mixed case. In the example it finds 'Corp' which is
mixed case but it can't find 'corp'.

> In
> the first place,
> it appears last in the dataset because everything preceding
> it is in upper
> case and hence ahead of it in the collation sequence;

No, it is last because the first byte is lower case.

Please, try inserting the row:
insert into department values(1,'corp x',null,105,100000,'Monterey','(408)
555-1234');
and run Samples\LookupCombo\LookupCombo.exe
Any other values seem to work OK, it appears to just be an issue when the
first character is lower case.
Besides, when you enter 'corp ' (no x) into the edit box it displays 'corp
x' in the edit box, so it has obviously found it, but the list doesn't
scroll and it gives an error. I still think it is a bug.

> and
> second, because
> it does not match what is being searched for, which is
> UPPER(SearchValue).

IMHO IBO should be doing UPPER(SearchValue) and UPPER(SearchedColumn) on
it's in memory tables.

Thanks for your thoughts :-)

Cheers,

Brian

>
> Cheers,
> H.
> All for Open and Open for All
> InterBase Developer Initiative ยท http://www.interbase2000.org
> _______________________________________________________
>
> -------------------------- eGroups Sponsor
> -------------------------~-~>
> eGroups eLerts
> It's Easy. It's Fun. Best of All, it's Free!
> http://click.egroups.com/1/9698/1/_/685810/_/975576749/
> --------------------------------------------------------------
> -------_->
>
>
>