Subject Re: [IBO] Using TIB_QUERY
Author Geoff Worboys
> Help. Put it how it should be Please !!
> Does the 'open' method do any 'fetching' ??

Depends on the properties like AutoFetchFirst and AutoFetchAll, and
also whether you have any data-aware components attempting to read
from the dataset. IB_Query supports inserting without openning.

You need to realise that TIB_Query is a bufferred dataset. It is
designed to read multiple records back to the client, and its code is
setup primarily to interactive with ibo-aware controls. If you are
not using ibo-data-aware controls then I suggest the use of TIB_Query
is inappropriate. However, if you wanted to manage the query in code
you can do something like...

// Set the SQL - IBO will automatically generate
// the required insert/edit/delete statements for
// simple SQL selects like this one.
IB_Query.SQL.Add('SELECT afield, bfield');
IB_Query.SQL.Add('FROM atable');

// In most interactive queries you dont add this
// where clause, but if are wanting to retrieve
// particular records for edit then this is ONE
// way of doing it. The alternative is to leave
// out this line and use the Locate method to
// find the required record from the buffer or
// table when the query has been opened.
IB_Query.SQL.Add('WHERE afield=:MyPKey');

// 'for update' automatically sets RequestLive to true
// so either add this clause or set RequestLive directly
// IB_Query.SQL.Add('FOR UPDATE');
IB_Query.SQL.RequestLive := true;

// This is probably redundant given the where clause
// above, but here it is anyway. If you got rid of the
// the parameter and decided to use Locate then this
// addition is recommended to explicitly tell IBO what
// the primary key is (instead of making IBO try to
// determine key itself).

// Presuming afield is the primary key and is
// defined using generators you will also want

// Prepare the query - try to setup to do this
// as rarely as possible as prepare is expensive
// to performance.

// To insert a new record:
// with GeneratorLinks attached above we
// wont need to set afield, IBO will do that
// automatically.
IB_Query.FieldByName(...).As... := ...

// To edit an existing record:
// first we need to make sure the required
// record is the current record.
IB_Query.ParamByName(...).As... := ...
if not IB_Query.Eof then
IB_Query.FieldByName(...).As... := ...
// if we had not defined a where clause with
// a parameter above, then we could have
// achieve the same thing by Locate. eg;
// if Locate( ... ) then
// begin
// IB_Query.Edit <etc>

// To delete an existing record:
// first we need to make sure the required
// record is the current record
IB_Query.ParamByName(...).As... := ...
if not IB_Query.Eof then
// if AutoPostDelete is false then which you
// may decide to do if you want to setup a prompt
// to confirm delete at this point. If so then
// you would need to have an explicit call to post.
// IB_Query.Post;

The above use of TIB_Query (using the parameter to select only a
single record) is an example of using TIB_Query as though it were a
TIB_Cursor. In fact you can same some resources by replacing
TIB_Query with TIB_Cursor and still do exactly what I showed above.
(You cant use Locate with TIB_Cursor, you must use the parameter
situation described above, locate only works with the buffered dataset

IMO there is no advantage in using TIB_Query in the above example.
However if you were wanting to have the records buffered at the client
for some other reason, then you could remove the where clause and
parameter shown above and use Locate instead of ParamByName, First.

So the above example is valid for IB_Query or IB_Cursor. Note that
you can also use TIB_DSQL something like...

// to Insert
IB_DSQL.SQL.Add('INSERT INTO atable( afield, bfield )');
IB_DSQL.SQL.Add('VALUES( :afield, :bfield )');
IB_DSQL.ParamByName(...).As... := ...

// to Edit
IB_DSQL.SQL.Add('UDPATE atable');
IB_DSQL.SQL.Add('SET bfield=:bfield');
IB_DSQL.SQL.Add('WHERE afield=:afield');
IB_DSQL.ParamByName(...).As... := ...

// to Delete
IB_DSQL.SQL.Add('WHERE afield=:afield');
IB_DSQL.ParamByName(...).As... := ...

This will save you even more resources, since IB_DSQL contains even
less overhead than IB_Cursor.

Note: For simplicity I have shown ParamByName and FieldByName above.
These are acceptable for simple situations but keep in mind that for
looping situations (eg importing many records) it is better to keep
track of the column references and use them directly. eg:

ACol, BCol: TIB_Column;
IB_DSQL.SQL.Add('INSERT INTO atable( afield, bfield )');
IB_DSQL.SQL.Add('VALUES( :afield, :bfield )');
ACol := IB_DSQL.ParamByName('afield');
BCol := IB_DSQL.ParamByName('bfield');
for <<some loop criteria>> do
ACol.As... := ...
BCol.As... := ...

There are more complete examples of managing IB_Cursor and IB_DSQL in
code in some of the sample applications. The idea of the above was to
try and demonstrate the main differences between how you use query,
cursor an dsql. It is really up to you to decide what is going to fit
best with your application - I will simply reiterate that ib_query
does not *appear* to fit with what you have explained so far. I
recommend either ib_cursor or ib_dsql.


Geoff Worboys
Telesis Computing