Subject Re: [IBO] TIBOQuery and Case Sensitivity
Author Helen Borrie
At 06:27 PM 2/04/2003 -0500, you wrote:
>We are using a TIBOQuery to access an indexed field in our database. We
>know how to make a TIBOTable case insensitive, but that method does not
>work for the query, and fails to compile. Is there a comparable method
>for the query?
>For the TIBOTable we do
>with MainForm.OrderTable do
> begin
> with IndexDefs[1] do
> begin
> CaseInsFields := 'TITLE';
> Options := [ixCaseInsensitive];
> end;
> end;
>We want to be able to do
>and have the records located whether the filter value is 000000000x or
>0000000000X. Is there a way to do this with TIBOQuery?

Yes, use the ColumnAttribute NOCASE. At the query level, you can do this
in run-time, but it is more convenient to do it at design-time, in the
TIBODatabase object. Just click the ColumnAttributes ellipsis and add a
string like this:


(no spaces)

This alone will cause the SQL to be

where Upper(ISBN) = :YourISBNValue

Because Upper(..) can't use your index, and will thus be slow, you are
better to do this:

For fast searching, you can add a triggered, case-insensitive proxy
searching column to your table, index it, and use Before Insert and Before
Update triggers to populate it automatically, e.g.

alter table ordered
add proxy_ISBN varchar (..); // same size as the ISBN column
create trigger bi_ordered for ordered // and bu_ordered...
active before insert position 0 // and active before update...
if (new.ISBN is not null) then
new.proxy_ISBN = upper (new.ISBN);

Then, when setting up your case-insensitive search capability for the ISBN
column, you can name the proxy_ISBN column as the one which IBO will use to
locate the input ISBN value. For this, your IBODatabase.ColumnAttributes
entry would look like this:

(no spaces!)