--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@t...> wrote:

> At 01:13 AM 25/01/2004 +0000, you wrote:
>
> > > You could pass the values of the new record to a stored

procedure,

> >the SP checks if the PK already
> > > exists and if so, updates the record. Otherwise, it inserts the
> >record.
> > >
> > >
> > > Florian
> >
> >So, if I understand you correctly, you are saying that
> >(1) it IS the case that I must know in advance whether a record I
> >want to add is to be INSERTed or UPDATEd; and
> >(2) a stored procedure would be a more efficient way of doing this
> >than a query.
> >
> >Correct??
>
> The contrary: if you don't know (in your application) whether the

record

> is to be added or updated, then (one of the) right way(s) to go

about this

> is to assign a stored procedure to your dataset's InsertSQL

property. The

> SP then performs the job of checking for existence and then either
> inserting or updating accordingly.
>
> create procedure InsertOrUpdateX (

[snip]

> Helen

Thank you Florian for noting that I should use a stored procedure,
and thank you Helen for showing me the SQL needed.

I think I understand the theory behind the savings involved, to wit,
SQL preparation and re-use of the PK when the new data results in an
UPDATE.

The implementation, however, is less clear to me.

Helen, in her post of the 25th, noted that her SQL statements should
be assigned as a stored procedure to my dataset's InsertSQL
property. Ahhh, which component is it that has this property? So
far, I have only an IB_Connection and an IB_Query. Would it be the
IB_Query's InsertSQL property, or should I be adding another
component that has this property? Also, in my Delphi 5 code, how do
I invoke the SP--I assume as SQL in the IB_Query, in which case I
assume I end it with ExecSQL. (And what role, if any, does "Commit"
have?)

Note: The first 4 columns are defined as the PK, so Helen's careful
warnings about generated PKs were not needed.

Thanks again,
-- Stan