Subject Re: [IBO] Adding records
Author drstanl
> >I think I understand the theory behind the savings involved, to
> >SQL preparation and re-use of the PK when the new data results in
> >
> >The implementation, however, is less clear to me.
> >
> >Helen, in her post of the 25th, noted that her SQL statements
> >be assigned as a stored procedure to my dataset's InsertSQL
> >property.
> >
> >Note: The first 4 columns are defined as the PK, so Helen's
> >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
> 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
> transaction).
> > Note: The first 4 columns are defined as the PK, so Helen's
> > warnings about generated PKs were not needed.
> Helen would issue *strong* warnings about 4-field primary keys that
> "meaningful" data and can be be meddled with by users.
> 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.

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('declare variable PKValue integer;');
IB_Q.insertSQL.add(' Select ThePKey from T1');

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?

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

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;

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

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.

-- Stan