Subject Re: [IBO] Adding records
Author Helen Borrie
At 03:48 PM 28/01/2004 +0000, you wrote:
>--- In, Helen Borrie <helebor@t...> wrote:
>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
>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"
>Note: The first 4 columns are defined as the PK, so Helen's careful
>warnings about generated PKs were not needed.

Assume your SP is declared something like the following:

create procedure ins_or_upd_mytable (
pk1 sometype, pk2 sometype, pk3 sometype, pk4 sometype,
fieldp sometype, fieldq........)

The input param list should contain a parameter for each column you want to
insert or update.

Both InsertSQL and EditSQL will have the SQL property

EXECUTE PROCEDURE ins_or_upd_mytable (
:pk1, :pk2, :pk3, :pk4,
:fieldp, :fieldq........)

Make life easy for yourself by naming the input parameters the same as the
fieldnames in the ib_query, otherwise you will have to assign values to the
parameters in your code.

The Insert/Edit and Post methods of the ib_query will take care of
everything else for you. All your code needs to do is call Insert (or
Edit), let the user enter stuff, do checks in BeforePost (if state =
dssInsert then did the user enter values for the whole of the PK?) and then
call Post. When ready, call Commit. (I don't recommend an Autocommit

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

Helen would issue *strong* warnings about 4-field primary keys that contain
"meaningful" data and can be be meddled with by users.