Subject RE: [IBO] IBO Searching capabilities
Author IBO Support List
Sorry for my delay in responding to these questions.
See my answers below notated with [JW].

-----Original Message-----
From: []
Sent: Saturday, June 27, 2015 11:15 AM
Subject: [IBO] IBO Searching capabilities

Hello All

I'd like to ask if following is possible and if so, how it can be set up.

1. Case insensitive searching.
Actually I know this possible but I don't know how to properly configure
the dataset for this capabilty.
Say I have a table for customer list that has (among others) two columns:
- short_name which is the primary key for the table
- full_name - a varchar(250) column

What should be the optimum scenario to achieve case insensitive
searching on these two column - additional columns in the table with
upper case content or additonal column in the select statement
representing upper case content.

Normally I would add in a separate physical column to the table and then
maintain in it the upper-case version of the original so that you can put an
index on it and thereby have the SQL IBO generates for you to be supported
by an index.

With the more recent versions of Firebird it is possible to have an index
based upon an expression and to also have the optimizer make sense of that
but I have not experimented with this just yet.

2. 'Containing' like searches without stars (*).
I am fully aware of using stars characters when specifying search
criteria and I use them a lot. But I wonder if it possible to tell IBO
to search with 'containing' or 'like' predicate without using stars.
Lets get back to the table from point 1.
Now user, when searches for a record where certain field might contain
specified text, he uses stars as brackets. It works great. But the user
complains about using stars. Can we set up the dataset somehow that it
always use 'containing' predicate?

I think I know what you are getting at. The Containing operator is not case
sensitive but the Like operator is. So, if you have eliminated the
case-sensitivity factor, you are wondering if you can have it use the Like
operator instead in the chance that you could pick up some index based
optimization for the query.

I think I would have to put some work into things to reap a benefit here.
This isn't really supported directly by IBO at this time.

3. 'Or' searching
Possible at all?
If I put two edits binded to same column IBO will 'and' both criterias.
How can I tell to 'or' them?

I have often contemplated doing this but I have not thought through a clean
way to set it up.
Doing it manually with regular TEdit controls would be pretty straight
forward though.
You just tap into the query's OnPrepareSQL event and pop in the whole phrase
as follows:

MyQuery.SQLWhereItems.Add( '(' );
MyQuery.SQLWhereItems.Add( 'ACOL=''AVal1''' );
MyQuery.SQLWhereItems.Add( 'OR' );
MyQuery.SQLWhereItems.Add( 'ACOL=''AVal2''' );
MyQuery.SQLWhereItems.Add( ')' );

Thanks in advance