Subject Re: [IBO] dssEdit
Author Helen Borrie
At 05:57 AM 26/01/2003 +0000, you wrote:

>Thanks very much Helen.
>I am sitting here at 04:00 uk time meditating on your very full reply.
>What I am trying to do is as follows:
>I have a Local database B and a central database A.
>I have assembled a lot of stuff in the local database B and want to
>send it to A.
>The records match exactly except that the database B has different
>record numbering (primary keys) from A.

Presumably you have before insert triggers on the A tables to deal with this.

>I have been doing everything with a stored procedure. But this
>involved a lot of programming and the error messages are not eaasy to
>handle SO in course of a rewrite I decided to try to do without a
>stored proc. I have 4 tables to send records from in this way. All
>tied together with foreign keys.
>I tried using datapump object but this seems overkill for 1 record.

<Smiles> So doing this the hard way is underkill?
Still trying to work out why there is only 1 record involved. How can you
be sending data from 4 B tables to 4 corresponding A tables, and only have
1 record involved?

>I have an open record on database B and I can assign the fields of
>this to a Tib_dsql...but this requires a lot of tping of parameter

But this is not overkill?

>SO instead I have a TIB_Query and copy the fields from the query on B
>into the fields of the query on B (For j = 0 to fieldcount-1 ...)

I suppose you mean "..into the fields of the query on A..."?

That sounds like extra-ultra-super-duper-overkill, from the point of view
of clientside activity, compared to an IB_datapump.

>Then I do qryonA.insert ...

If you must avoid using a datapump, at least do this the way the datapump
does it. Read a TIB_Cursor on the B table and plug the values directly
into an IB_SQL.

SQL on the B table (in an ib_cursor):

select field2, field3, field4, ....
from TableInB
where <whatever>

SQL for A table (in an ib_dsql):

insert into TableInA(field2, field3, field4, ....)

Then, opening the ib_cursor using First(), just step through it until
EOF. Just before you call next, call a proc that does this:
jj: integer;
with CursorOnB do
...the other stuff;
if not ib_DSQLforA.Prepared then
for jj := 1 to FieldCount - 1 do // ignore 0 as it is the primary key
ib_DSQLforA.params[jj-1].Value := Fields[jj].Value;

>but I presume this just sets things into
>an insert state. Then qryonA.execute. This seems to work, but am I
>doing a sensible thing. There are so many apparently different ways
>of achieving an end in IBO and I never know which is best !!

Well, this isn't one of 'em.