Thanks. I will try to work with this.

On Fri, 12 Nov 2004 23:31:35 +1100, Helen Borrie <helebor@...> wrote:
>
>
>
> At 12:48 PM 12/11/2004 +0100, you wrote:
>
> > > >The original application has a stored procedure that reads a record
> > > >and locks it. Then this record is procesed by the application and the
> > > >results of the process are saved again in the record using another
> > > >stored procedure, unlocking the record. It's important that no other
> > > >user reads the record and begins processing it until the first user
> > > >ends his work.
> > >
> > > I assume then that your SP processes one single record. That was one
> > > purpose of my question. It is a different story if you have a SP that is
> > > processing multiple records. But what you are telling us here is still far
> > > too vague. Does the first SP do anything else except this locking?
> > >
> >
> >Searchs thru a table based on the parameters, finds the record (only
> >one), locks it and returns his contents.
> >
> > > > I want to emulate this behaviour without writing Delphi code, simply
> > > >writing an equivalent stored procedure, to get the same code working
> > > >with DB2 and Firebird.
> > >
> > > I don't understand this. According to your description, the client calls
> > > one stored procedure, does some work, and then calls another stored
> > > procedure. You need Delphi code to call stored procedures.
> >
> >Yes. But all the callings to SP in the application are done through a
> >library (a custom VCL component). When we use DB2 we link against a
> >version of the library and when we use FireBird we link against
> >another version of the library. So all the code of the main
> >application (the one that I don't want to change) remains the same.
>
> Well, you could do this:
>
> create procedure lock_emp1 (emp_no smallint)
> as
> declare variable dummy varchar(20);
> begin
> select last_name
> from employee
> where emp_no = :emp_no
> for update with lock
> into :dummy ;
> end
>
> This will get your transaction a lock on the record without causing any
> update triggers to fire.
>
> The lock will disappear when the transaction is committed or rolled
> back. There is no other way to "unlock" a record.
>
> If another transaction tries to run the procedure, it will get a lock
> conflict error, which you can catch, and tell the user that the record is busy.
>
> If you need a do-nothing "unlock" procedure for the sake of polymorphism,
> you could do something like this:
>
> create procedure dummy_unlock
> as
> begin
> /* */
> end
>
> Of course, Firebird can't prevent readers from reading a locked record
> (unless you do something really prohibitive and use table-level locking).
>
> ./heLen
>
>
>
> Yahoo! Groups Links
>
>
>
>
>


--
--
Antonio Carrillo
antonio.a.carrillo@...