Hi Rob,

(funny i just read Geoff's reply when i wanted to post mine - since i went
into a somewhat different direction i'll post it anyway - hth!)

Rob Schuff wrote:

> 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
>
> 1. We code the insert update and delete procedures to execute the SP's in
> question. This seemed to work well until i thought we should return the
> newly generated primary key as part of the Insert SP. When we did this we
> started getting an error about expecting a message length of 8 and getting
> 0. As I am writing this I am thinking that in general it really doesn't
> make sense to have the stored procedure return the primary key of the record
> it just inserted. Comments here?

With IBO- not really. When you're just doing "flat" inserts (into one table),
use GeneratorLinks or hand-code it with TIBOQuery.InternalDataSet.GEN_ID(
generatorname, increment).
However the fun part about SPs is that you can have "wanted sideeffects",
such as writing specific log records to another table while at the same time
inserting into your main table. That's were insert sps that return ids
are quite useful imo. About the error you got - don't know, but you
*do* have a SUSPEND at the end of the sps, do you?

> 2. Do we need to set the keylinks properties still? So far everything still
> seems to work as expected but its very early to tell for sure.

setting keylinks is always a good idea. explicity avoids ambiguity. i have
an arrangement (with myself <g>) that *every* table has a PK field named ID
(integer), so i never have to set it with KeylinksAuto=true, but YMMV.

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

we have quite a big app that is partly IBO-based (started as BDE but that was
easy to change), all the new parts are IB_ "native". We have to log and
double-check practically everything that happens. The main tables are
state/transition driven, and for the sake of simplicity every state transition
is a storedproc. Then i wrote two little Delphi compo classes to handle the
states and transitions. The states know their possible transitions, and every
transition knows the name of it's storedproc (since it's the same<g>) plus
it's params. "Data-driven programming" with storedprocs, and it works like
a charm! i just drop a button on a form, assign a state transition and let
the sps do all the rest.

(not that you would have to go this way, just one approach that i know works)

> Any general comments appreciated.

oops - afraid that wasn't really general :-)

(btw. take a look at http://www.fingerman.de/fbspgenerator.htm - you
wrote "we code the (...) procedures"- if by hand, this thing might save you
a lot of typing. works quite good already, but i could use someone (other
than myself) to test it!)

regards,
fingerman