> I am just now venturing into the world of using stored procedures
> for all select, insert, update, and delete (SUID) activities. I'm
> hoping someone will give some general advice and be able to answer a
> couple of questions. We are using TDataset stufg with TIBOQueries
> almost exclusively. D5.01 and IBO v4.2Fn

Hi Rob,

Rather than tackle your questions directly I will stay general,
I think you will find the questions answered in amongst this
following...


Firstly, as your questions imply, it is the keylinks/primary-key
issue that comes into play most. The problem is that a stored
procedure that normally returns a set of (for example) 100 records,
cannot be used to return just one of those records by key value.
So after an insert, update or delete you must re-read the entire
result set from the select stored procedure in order to see the
change. This effectively reduces the useability of stored procedures
inside interactive queries to those that return small result sets.

A very long time ago Jason and I discussed updating the datasets so
that they supported a special SelectByKeySQL property that could be
used to return a record by primary key. So you would create a new
stored procedure designed to accept the keylinks as parameters and
return the full set of fields for a single record. However this
turned out to be quite difficult and got put off, AFAIK it has not
been done since.

Just as in keylinks for normal select statements, IBO must be able to
determine the final key for an insert BEFORE it is posted - otherwise
it cannot identify the record after refresh from the select SQL. This
means using generator links or some other mechanism and feeding that
information into the InsertSQL stored procedure - and returning that
same information as part of the select SQL stored procedure.


For similar reasons search mode is less effective - because there is
no way for the server to optimise a query. It must still process the
full procedure and filter the results. There are additional problems
with using search mode when a detail stored procedure is linked via
parameter to a master dataset - it does not work.


That is not to say that stored procedures are not useful, only that
you must ensure that the result set is small for any interactive use.
By "small" I mean probably less than 100 records - small enough so
that a full refresh is very fast. For example I use stored procedures
to display and maintain timesheets - where the actual days data may or
may not exist in a table the stored procedures that the user interacts
with makes it appear that there are always a full seven days in the
week - inserting, updating and deleting underneath as required.


I would also suggest that when writing stored procedures that accept
input parameters - that you return those input parameters as fields.
eg.
CREATE PROCEDURE X( InputVal INTEGER )
RETURNING( OutputVal INTEGER )
AS
BEGIN
OutputVal = InputVal;
SUSPEND;
END

By always having the input parameters copied to the Fields list
various possibilities exist (such as display of inputs, or passing
those inputs to a lower level detail procedure) that are not otherwise
possible/convenient.

> 3. Anybody have a general comments about doing all activities
> through SPs and how it changes the way a delphi app is coded? We are
> doing this to set the stage for a developing a solid journaling
> system.

I would suggest that a "journaling system" is exactly the sort of
problem to which stored procedure make a great solution. However I
would not recommend that you lock yourself in to doing all queries via
stored procedures - it will be fine for non-interactive stuff (display
only or reporting) but anywhere that large selects may need to be
interactive will probably need solving with a "normal" select
statement.

If you have the time/interest to get into the guts of IBO, then
perhaps you would be interested in revisiting the idea of providing a
special SelectByKeySQL property - which would resolve some of the
issues raised above. Without that solution using stored procedures
exclusively will probably not be practical.

hth

--
Geoff Worboys
Telesis Computing