At 12:32 PM 09-09-02 +1000, you wrote:

>I am using a cursor to fill some in-memory tables as follows:
>
> * DBSpriteDetail is my in-memory table (template stringlist class)
> * cirIPLookup is my TIB_Cursor with the SQL property as:
>
> SELECT IPL_IPADDRESS, IPL_LOCAL
> FROM IPLOOKUP
> WHERE ID = :ID
>
>My code:
>
>for(int i = 0; i < DBSpriteDetail->Count; i++)
> {
> // move to the next record in memory
> DBSpriteDetail->CurrentRecord = i;
>
> // find the record with the IPLookupID values in the IPLOOKUP table
> curIPLookup->Prepare();
> curIPLookup->ParamByName("ID")->AsInteger =
>DBSpriteDetail->Fields->IPLookupID;
> curIPLookup->Execute();
>
> // and assign the IPAddress and Local values
> DBSpriteDetail->Fields->IPAddress =
>curIPLookup->FieldByName("IPL_IPADDRESS")->AsString;
> DBSpriteDetail->Fields->Local =
>curIPLookup->FieldByName("IPL_LOCAL")->AsBoolean;
>
> curIPLookup->Close();
> }
>
>I am basically looking up the IPAddress and Local field values based on an
>ID value in another table - hence I have to do a query rather than just walk
>through the records.
>
>My question is whether this is the most efficient method (required to
>prepare/close on each iteration).

No! this is a very bizarre way to get the SpriteDetail data into your
application. You should bring it across into a dataset using a join
between the two tables. A dataset is already an in-memory table (remember,
we don't call them tables in SQL, we call them sets) with a huge amount of
encapsulated behaviour so "you don't keep a dog and bark yourself".

However, in principle, when iterating through one set to get values for
another set (not a thing you would usually seek to do with a relational
database as your back-end) where the only thing that changes is parameter
values, you would call Prepare "once or less". Meaning, you test to see
whether it is Prepared before you begin the loop.

Some points:

You NEVER call EXECUTE a SELECT statement. For IB_Cursor, use First; for
IB_Query, use Open.

Prepare has nothing to do with Close and Open. Prepare is a special API
call that queries the database to vaidate your query and to get the data
attributes of the columns in your query. Even if you close a query, it
remains prepared until either you explicitly call Unprepare (which you
would do with a query that you want to "put to sleep") or until the SQL
statement gets replaced. Most of the time, IBO does any unpreparing that
is necessary so you only need to be concerned about it when you want to
free up unused client resources.

Here's a a typical loop in pseudo-jumble (Pascal#Builder):

for(int i = 0; i < DBSpriteDetail->Count; i++)
{
// move to the next record in memory
DBSpriteDetail->CurrentRecord = i;
// find the record with the IPLookupID values in the IPLOOKUP table
if not curIPLookup->Prepared then
curIPLookup->Prepare();
curIPLookup->ParamByName("ID")->AsInteger =
DBSpriteDetail->Fields->IPLookupID;
curIPLookup->First();
// and assign the IPAddress and Local values
DBSpriteDetail->Fields->IPAddress =
curIPLookup->FieldByName("IPL_IPADDRESS")->AsString;
DBSpriteDetail->Fields->Local =
curIPLookup->FieldByName("IPL_LOCAL")->AsBoolean;
}

FWIW, here's the SQL to get these data in one set (guessing about the
structure of SpriteDetail).

select sd.IPLookupID,
ip.IPL_Address,
ip.IPL_Local
from SpriteDetail sd
join IPLocal ip
on sd.IPLookupID = ip.ID
where sd.something = 'x'
and ip.somethink = 'y'
order by sd.IPLookupID

Helen