At 04:16 PM 10/02/2005 +0000, you wrote:

>Dear all,
>
>I have a table containing names. When a user requests a particular
>name, I want to also fetch the neighboring names in an alphabetical
>sense. Example: viewing SMITH,JOHN; previous: SMITH,JAMES; next:
>SMITH,PETER
>
>The SQL I am using is:
>SELECT fullname FROM mnames WHERE fullname>'SMITH,JOHN' ORDER BY
>fname;
>SELECT fullname FROM mnames WHERE fullname<'SMITH,JOHN' ORDER BY fname
>DESC;
>
>So I want to fetch the first row from each result set. There is an
>index on the fullname column. The table contains maybe 50K names and
>is accessed over a low-speed connection, so doing a client-based
>'locate' is unviable.
>
>Here is the problem: In IBX, simply opening two datasets with the
>above SQL and reading the first record works FINE. But when I use
>IBO, it is MUCH slower depending on the position of the name.

That's because IB_Cursor is not buffered.

But this approach of trying to engineer this set on the client is horribly
inefficient. The golden rule for getting sets that can't be got via
regular dsql is to use a selectable SP. Here's an example that will fetch
the set you want. Put this selectable SP into an ib_query and you will get
the exact set you want in a single hit:

create procedure get_a_group (
like_name varchar(60), param2 ....)
returns
return_name varchar(60), retval2...)
as
declare variable counter1 integer = 0;
declare variable counter2 integer = 0;
declare variable temp_name varchar(60);
declare variable temp_retval2....

begin
/* Start by simulating SELECT FIRST to find the cardinality of the first
matching record */

for select fullname [, other_value] from mnames
where fullname = :likename
and [other params]
order by fname
into :temp_name, :temp_retval2...
do
begin
counter1 = counter1 + 1;
if (temp_name = like_name) then
leave;
end

/* Now we know which record was the last matching one, get the rows we
want */
if (counter1 > 0) then
begin
for select fullname [, other_value] from mnames
where fullname < :likename
and [other params]
order by fname
into :return_name, :retval2...
do
begin
counter2 = counter2 + 1;
if counter2 = counter1 - 1) /* we are at the previous row */ then
begin
suspend;
leave;
end
end
/* now get the others */
for select fullname [, other_value] from mnames
where fullname >= :likename
and [other params]
order by fname
into :return_name, :retval2...
do
begin
suspend;
if (return_name <> like_name) then
leave;
end
end
end

To avoid the problems you mention, re users typing the wrong thing into
a search parameter box, consider using a tight little ib_cursor query to
fetch all of the fullname values for a given alphabetical starting
character into a TStrings (such as a TComboBox) and have them select the
FULLNAME value they want to look at. This is something IB_Cursor is
*really* good for:

SELECT DISTINCT FULLNAME FROM MNAMES
WHERE FNAME STARTING WITH :FCHAR
ORDER BY FNAME

Delphi code (sorry, I don't do C++):
...
MyComboBox.Items.Clear;
with MyAlphaSelector do
begin
if MyEditbox.Text = '' then Exit;
if not Prepared then Prepare;
Params[0].AsString := uppercase(copy(MyEditbox.Text, 1, 1));
First;
while not EOF do
begin
MyComboBox.Items.Add(Fields[0].AsString;
Next;
end;
Close;
end;

Grab the string the user selects as the input parameter to the SP and
you're away.

Another thing you can do with slow connections is use schema caching to cut
down the wire time spent on prepares.

Helen