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