> >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.

[snip]

> >
> >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........)
> as....
>
> 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

> transaction).
>
> > 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.
>
> Helen

Helen,

Thanks for your comments with the code for the Stored Procedure.
I remain uncertain, however, how exactly to do the following:
(1) Create the Stored Procedure, and
(2) Invoke the SP with the required values.

(1)
To create the SP, do I issue a set of commands like the below:

IB_Q.insertSQL.add('create procedure InsertOrUpdate (');
IB_Q.insertSQL.add(' ID1 VARCHAR(12), ID2 VARCHAR(4),');
IB_Q.insertSQL.add(' D1 INTEGER, D2 INTEGER, V1 DOUBLE)');
IB_Q.insertSQL.add('as');
IB_Q.insertSQL.add('declare variable PKValue integer;');
IB_Q.insertSQL.add('begin');
IB_Q.insertSQL.add(' Select ThePKey from T1');
etc.

If so, do I end it with any call (like ExecSQL)?

Also, you use the variable "ThePKey". Is this actually a variable
introduced into the SP, or was your intention that I replace that
string with the actual name of the primary key (Constraint) I defined
when I created the table?

(2)
Regarding using the SP:
I will be updating (or inserting) records in (or into) the table
using data read from a text file. Each text line (record to update or
add) will be parsed into a structure, MyRec, that will hold the
values for that record. I will read each line, parse into MyRec,
call the SP, and read again until the (large) text file has been
exhausted.

To use the SP, do I do the following:

{ read a text line }
{ parse the line into MyRec }
IB_Q.sql.add('EXECUTE PROCEDURE InsertOrUpdate (');
IB_Q.sql.add(':ID1, :ID2, :D1, :D2, :V1 )');
IB_Q.sql.param[0].asString := MyRec.ID1;
IB_Q.sql.param[1].asString := MyRec.ID2;
IB_Q.sql.param[2].asInteger := MyRec.D1;
IB_Q.sql.param[3].asInteger := MyRec.D2;
IB_Q.sql.param[4].asFloat := MyRec.V1;
IB_Q.execSQL;

You had referred to calling insert and post; aren't those Table (a la
the BDE) methods? I'm not sure how to use them here, or with what
component (I have only an IB_Connection and an IB_Query).

Do I call "Prepare" anywhere?? Any special settings to turn on or
off in IB_Q that I normally would either ignore or leave at their
default??

Sorry for asking these little details. The IB Developers Guide gives
a rather confusing discussion of this topic and brings in additional
components, so I felt I had no choice but to ask.

Thanks,
-- Stan