Thank you all for this help. it is really appreciated


At 01:18 22/06/2006, Adam wrote:

>--- In firebird-support@yahoogroups.com, Johannes Pretorius
><johannes@...> wrote:
>>
>> Good day
>> 09--0-0-0-0
>>
>> This is related to the question I had. : "Rec Ver / Updates and
>Deletes".
>>
>> Taking transaction into account. How does the stored procedures fit
>into this ?
>
>A call to a stored procedure (SP) is just like any other DML
>(insert/update/delete). A SP but rather a sequence of statements and
>control mechanisms that is executed as a single operation.
>
>Unlike some other DBMS, a stored procedure has no control over the
>starting or ending of a transaction.
>
>> If we
>> take the scenario as follows.
>>
>> Client A starts a transaction and does some updates and inserts and
>then calls a stored proc and then commits.
>> Now A gets a dead lock thanks to a other clients updates, but linked
>to an update made by client a and not the stored procedure.
>>
>> DOES the changes that the stored procedure make (Lets say the stored
>procedure inserted data into a table based on some rules)
>> also roll back ? or is it outside of the transaction ? .. basically
>is the stored procedure in its OWN transaction and not part of the
>clients transaction ?
>
>The SP runs in the same transactions your updates and inserts. If you
>rollback that transaction, all the changes made by your SP are undone.
>
>Using a SP allows you to implement a basic form of exception handling
>within the SP, so you can catch and react to particular exceptions as
>you see fit. For example, you may catch a lock conflict. Below is a
>simple example of attempting to delete a record, but if you get a lock
>conflict leave it untouched.
>
>BEGIN
> DELETE FROM SOMETABLE
> WHERE ID = :ID
>
> WHEN GDSCODE LOCK_CONFLICT DO
> BEGIN
> -- IGNORE
> END
>END
>
>You would still receive any other exception. Any exception you do not
>handle internally is passed back to the client application, and ALL
>work done by the stored procedure is undone.
>
>There are only three ways to end a transaction in Firebird.
>
>1. Explicitly commit the transaction
>2. Explicitly rollback the transaction
>3. Break the connection (Firebird will rollback after it detects this
>has occurred)
>
>Firebird will never automatically start a transaction for you, you
>must do that (although some tools may start one for you if you issue a
>SQL command without an active transaction, the engine itself will
>requires a transaction to do ANYTHING.)
>
>Adam
>
>
>
>
>
>
>
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://firebird.sourceforge.net and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 19/06/2006

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 19/06/2006