At 09:03 PM 27/07/2003 +0000, you wrote:

> > Can someone tell me how I can tell my database not to use case
>sensitive
> > searches and compares? I have seen an article on doing this but
>it
> > involved creating a "cloned field" if you will, and adding the
>data to
> > the original field as is and then adding the data using an Upper
>or
> > Lower case method into the "cloned field". Then when comparing
>when not
> > needing case sensitivity you would do an Upper or Lower on the
>data
> > being sent to the database against the "cloned field". Is there a
> > different way of accomplishing this?
>
>Thank you.

Yes, you can, technically do this in your search criteria:

where upper(Charcolumn) = upper(searchkey)

But try it, and you find it is deathly - that's because the uppercasing
prevents your index on Charcolumn from being used. (The database engine
doesn't support indexed expressions).

"Adding the data to the cloned field" is the way we do things when we want
a fast search. I refer to it as a "proxy search column". The proxy column
is the same type and size as the search column but it is all upper
case. You populate it using Before Insert and Before Update triggers:

....
if (new.Charcolumn is not null) then
new.Proxycolumn = upper (new.Charcolumn);
....
and you index Proxycolumn instead of Charcolumn.

IB Objects lets you assign a property linking your indexed proxy column to
the actual column so that, when you do

where Charcolumn = :uppercasesearchargument

the parser will replace Charcolumn with Proxycolumn before it fires off the
statement.

Without IBO, you need to do this manipulation yourself.

heLen