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
>
>TIBQuery.SQL.Text := 'SELECT * FROM ORDERED WHERE ISBN=''' + ISBN_Text +
>'''';
>
>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:

ORDERED.ISBN=NOCASE

(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
commit;
...
create trigger bi_ordered for ordered // and bu_ordered...
active before insert position 0 // and active before update...
as
begin
if (new.ISBN is not null) then
new.proxy_ISBN = upper (new.ISBN);
end

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:

ORDERED.ISBN=NOCASE=ORDERED.PROXY_ISBN
(no spaces!)

regards,
Helen