Hi Helen,

Helen Borrie wrote:

> At 10:17 PM 04-11-02 +0100, Frank Ingermann wrote:
>
>>so i am really curious why you recommend against those SELECTable DML beasts??
>
> My rationale for avoiding performing DML in a selectable stored procedure
> is that I treat all executable SPs as "pre-packaged DML" that should either
> complete as intended or fail with an anticipated exception. That way, the
> SP can return the error to the client where it can be handled in a
> predictable way.

that's often what i'm trying to do - i just need to take a look at the result
set as the errors i'm looking for are not real database errors but logical
inconsistencies in (otherwise "well-formed") data.

> Along the same line of reasoning, I don't write
> executable SPs that perform multiple DML ops on a single row: I always
> construct them so that a row is not updated until the procedure knows all
> of the final values for the row that it wants to insert or update...
>
> This way, the transaction has one new recversion for each updated or
> inserted row.

sounds very reasonable (and signs of relief here as i do this the same way :)

> Now, switching over to your scenario, where you get a set of rows, perform
> some DML operations on them and then output those temporary rows to your
> client app as a virtual set...if the SELECT succeeds, then you know that
> (a) you have the underlying database rows locked and (b) that the DML you
> performed was free of run-time errors.
>
> Over in the client, you make this temporary set updatable by the use of
> XXXXSQL properties.

oops - i should have mentioned that i never let users *edit* the result set of
such an SP. Usually i just loop through the set with a Cursor to check the
results are as expected and then decide in code whether to Commit or Rollback.
The advantage to me is that when i detect an inconsistency in the first row(s)
returned, i can rollback right away and don't have to wait until the SP executed
completely (as i would have to if i just executed it as pure DML proc).

In some rare cases the results are presented to the user in a grid so he/she can
decide for himself to Commit or Rollback ("is this what you wanted?"), but i try
to avoid this in the interest of keeping transactions as short as possible - you
never know when, if ever, the user will decide to Commit or Rollback...
And, in those situations a FetchAll is done before the user sees the results.

<snipped several good points that apply when editing the result set>

> As for "rewriting all of your procedures", Thomas, I would not presume to
> recommend that to you, as one who is fully in command of the transaction
> model.

well, since i'm about to rewrite most of them anyway (upcoming redesign / new
release of our app) i thought i'd better ask before starting this... so i'm
happy there are no major showstoppers in *the way i use* those SPs.

> Myself, I will always avoid it, in pursuit of the principle "render unto
> the server that which is the server's". I want a client operation to be
> atomic; so my approach with a selectable SP is to provide the selected
> rows to the user, let her perform her edits and then, when I know what she
> wants to do, pass her results back to the server and operate on them there
> - with an executable SP and triggers.

with the pure selectable SPs (no DML) i go about the same way, practically all
DML in the app is performed by pure executable SPs - so can we agree on :

"selecteable DML procedures are acceptable when you know what you *shouldn't* do
with'em" ? ;-)

> Now, because you asked :-) I give you - the ACID rules!
>
> regards,
> Helen

thanx a lot - i had a feeling they have nothing to do with sulfur or the like,
and "All Clients Interact Devastatingly" didn't sound quite right either :-)
off to a second attempt to get some sleep now...

regards,
fingerman

--
-------------------------------------------------------------------------
when parsers parse, and compilers compile, then why don't objects object?

fingerbirdy - fingerman's door to Firebird
http://www.fingerbird.de