Subject Re: [IBO] Transactions Scope for a Beginner
Author rajesh69nair
Hi Alan,
Well thanks a lot for the response. But i would like to understand if
all transaction scope does happen at client side what is the
relevance of transaction scope commands like set transaction, commit
and rollback as listed out in the index...or am i using incorrect
syntax.. i have been able to implement similar business logic in MS
Sql Server.

TIA

rajesh


--- In IBObjects@yahoogroups.com, "Alan McDonald" <alan@m...> wrote:
> >
> > Hi All,
> >
> > I am a beginner at this Database ..I wanted to incorporate a
> > transaction scope into my stored procedure. Sample script. If
someone
> > could identify with the error. Im getting Invalid token.
> > Dynamic SQL Error.
> > SQL error code = -104.
> > Token unknown - line 9, char 13.
> > SET.
> >
> >
> > Below is the stored procedure...
> >
>
> the transaction scope is set at the client. You cannot set
transaction scope
> within a stored procedure, it applies to all behaviour at the
server for the
> scope of the transaction
> Alan
> >
> >
> >
> > CREATE PROCEDURE UPDATE_DEPT_MODIFY
> > AS
> > DECLARE VARIABLE DEPT_ID INTEGER;
> > BEGIN
> > FOR SELECT TB001_MR_DEPARTMENT.FD001_ID FROM
> > TB001_MR_DEPARTMENT INTO :DEPT_ID
> > DO
> > BEGIN
> >
> > SET TRANSACTION UPDATE_DEPT;
> >
> >
> > UPDATE TB001_MR_DEPARTMENT SET
> > TB001_MR_DEPARTMENT.FD001_MODIFY_BY=:DEPT_ID
> > WHERE TB001_MR_DEPARTMENT.FD001_ID = :DEPT_ID;
> >
> > IF (DEPT_ID =2)
> > THEN
> > BEGIN
> > COMMIT TRANSACTION UPDATE_DEPT;
> >
> > END
> > ELSE
> > BEGIN
> > ROLLBACK TRANSACTION UPDATE_DEPT;
> > END
> >
> >
> > END
> >
> > END
> >
> >
> >
> >
> > __________________________________________________________________
> > _________
> > IB Objects - direct, complete, custom connectivity to Firebird or
> > InterBase
> > without the need for BDE, ODBC or any other layer.
> > __________________________________________________________________
> > _________
> > http://www.ibobjects.com - your IBO community resource for Tech
> > Info papers,
> > keyword-searchable FAQ, community code contributions and more !
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >