At 10:17 PM 04-11-02 +0100, Frank Ingermann wrote:

>so i am really curious why you recommend against those SELECTable DML beasts??
>(Warning: if you can give good reasons i'll likely have to rewrite half of my
>app - which is running in production for three years now ;-)
>
>regards & tia,
>fingerman
>
>P.S.: i know what commonsense is (not that i'm sure i have it, but i know
>what
>it is<g>), and i know acid jazz, but what on earth are "ACID rules" ???

Aaargh, I hoped everybody knew. :-)) Just for interest's sake, I'll append
a fairly reachable description that Ann Harrison sent me once when I asked
the same question.

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

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. What you are doing thereby is to provide the user with
the ability to edit the recversions. In the case of newly-inserted
recversions, these are rows which have no "real" counterpart in the
database at all, providing the potential to "edit" non-existent rows that
another transaction has inserted in the meantime, causing unforeseen
violations; in the case of update recversions, you are giving the user the
opportunity to stack up new recversions. It just takes one violation in
any one of many scenarios to "blow" the whole operation and you need to
roll back the whole task. How do you explain to the user what went wrong?

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.

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.

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

regards,
Helen
-----------------------------------------------------------------------------------------------------
The buzzword here is ACID - which stands for something...
This was lifted from the NuSphere site (Progress's attempt
to improve MySQL).
In the context of database transactions, ACID is an acronym for Atomic,
Consistent, Isolation, and Durable. Transactions provide a simple model of
success or failure. A transaction either commits (i.e. all its actions
happen),
or it aborts (i.e. all its actions are undone). This all-or-nothing quality
makes
for a simple programming model. The attributes of an ACID transaction are:

Atomicity
A transaction allows for the grouping of one or more
changes to tables and rows in the database to form an
atomic or indivisible operation. That is, either all of the
changes occur or none of them do. If for any reason the
transaction cannot be completed, everything this
transaction changed can be restored to the state it was
in prior to the start of the transaction via a rollback
operation.


Consistency
Transactions always operate on a consistent view of
the data and when they end always leave the data in a
consistent state. Data may be said to be consistent as
long as it conforms to a set of invariants, such as no
two rows in the customer table have the same
customer id and all orders have an associated
customer row. While a transaction executes these
invariants may be violated, but no other transaction will
be allowed to see these inconsistencies, and all such
inconsistencies will have been eliminated by the time
the transaction ends.


Isolation
To a given transaction, it should appear as though it is
running all by itself on the database. The effects of
concurrently running transactions are invisible to this
transaction, and the effects of this transaction are
invisible to others until the transaction is committed.


Durability
Once a transaction is committed, its effects are
guaranteed to persist even in the event of subsequent
system failures. Until the transaction commits, not
only are any changes made by that transaction not
durable, but are guaranteed not to persist in the face of
a system failure, as crash recovery will rollback their
effects.