At 04:02 PM 8/08/2005 +0000, you wrote:

>--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:
>First of all: Thanks for your help.
>... but ... it leads to new questions.
>
>The select statement I create on the fly reads only one record from
>one table in DB1, identified by it's PK. It then writes this record to
>a table in DB2 (insert/update/delete).
>Earlier I wanted to do this with DML-Statements.
>Now I changed that: I use the same select statement in both source and
>destination DB and transfer the data with Edit, Insert or Delete
>commands.
>
>Is it better to
>a) set the IB_Query component to RequestLive=TRUE in the destination
>DB
>or
>b) should I build Insert/Update/Delete-SQL's for the corresponding
>IB_Query properties?

I would not do it this way at all.

Use ReadCommitted isolation for your transaction.

Use a SELECT statement and an ib_cursor to fetch your single row from the
source database; use a TIB_DSQL for the DML statement(s) to the
destination database - all of this invisible to the user. Neither of these
statements is linked to any visual controls.

In the same transaction, run a SELECT statement in an ib_query for the
destination data you want the user to watch. Make RequestLive False in
this dataset.

However, if you need to display a set from which the user must select the
source record, then use a SELECT statement and an ib_query *instead* of the
single-row select in an ib_cursor. That is, if you already have the source
data in your application, you don't need to go and get it again.

>In case of a) - does the "for update" clause have any influence on DB
>performance

Yes. It causes rows to be fetched from the server one-at-a-time, instead
of multiple rows per packet. TIB_Cursor uses FOR UPDATE to fetch exactly
one row at a time.

>or the locks that are applied?

No. The FOR UPDATE clause on its own has nothing to do with locks.

Where it gets confusing is the addition into Fb 1.5, by Nickolay Samofatov,
of the WITH LOCK clause, which causes each individual row to be
pessimistically locked at the moment it is fetched. FOR UPDATE WITH LOCK
should be used only with extreme caution and NOT for statements that fetch
multiple rows.

>if b), how do I handle BLOBs?

A BLOB is still a BLOB when it is fetched. If you are just reading the
blob from one DB and writing it to the other, the Prepare part of the DML
into the destination database will take care of setting up the structures
needed for streaming the blob's contents from A to B. Just pass parameters
for it as you would for any other type.

Have you considered using a TIB_Datapump for this job? It just takes care
of everything.

Helen