How do I lock oracle table form Delphi

i was trying to use lock or Exclusive property, but they work only for paradox or dbase. So how do i do it for Oracle?

Thanks
LVL 1
danil082697Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
kretzschmarCommented:
hi danil,

locks are not available on sql-databases,
try to do it with starttransaction  -> commit/rollback with a database component

meikl

0
 
ZifNabCommented:
you normally don't need to lock. Oracle does it all by itself.
0
 
ZifNabCommented:
dani,

example how to let Oracle do it (from B. Morales) :

Hi--I have enclosed a fragment of my code that locks a row when you click
the Edit button. I have a grid attached to Query1 which just selects all
the records in the COMPLAINT table. I have a second query component
(Query2) that I use whenever the user clicks Edit. Query2 selects just the
record the user is currently on in the grid, and the select has a FOR
UPDATE NOWAIT option. You can see from the ParamsByName lines that I want
the primary keys of the Query2 select statement to be equal to the primary
key field contents of the current record in Query1.
I also included the procedures from the data entry form below which starts
a transaction when the data entry form is first shown, saves or cancels the
data when the user clicks the Save or Cancel buttons. Both Query1 and
Query2 by the way are using cached updates, and I have a TUpdateSql
component attached to Query2 to modify, insert or delete a record. I also
have a TDatabase component to control transactions.

When one user is editing a record and a 2nd user tries to edit a second
record, this program gives the 2nd user an error message that the row is
locked with a For Update Nowait option. You can modify the program to
allow them read-only access to the data or not allow them access to that
record.

If you want the complete program, email me at moralesb@pbworld.com.

procedure TForm1.EditClick(Sender: TObject);
begin
{Use the FOR UPDATE clause to "lock" the row, and use the
NOWAIT clause so only one user can update the row and other users won't
wait for the same row, but will get an error message instead.}
Query2.Close;
Query2.SQL.Clear;
Query2.SQL.Add('SELECT * FROM SYSTEM.COMPLAINT');
Query2.SQL.Add('WHERE COMPLAINTKEY = :COMPLAINTKEY');
Query2.SQL.Add('AND RECTYPE = :RECTYPE');
Query2.SQL.Add('FOR UPDATE NOWAIT');
Query2.ParamByName('COMPLAINTKEY').AsString :=
Query1.FieldValues['COMPLAINTKEY'];
Query2.ParamByName('RECTYPE').AsString := Query1.FieldValues['RECTYPE'];
{ParamsByName above uses the first two columns/fields of Query1, which
make up the primary key for the SYSTEM.COMPLAINT table.}
if not Query2.Prepared then Query2.Prepare;
try
{Query2 "locks" the row in the Oracle table so other users can't update the
same
row at the same time.}
  Query2.Open;
  Query2.Edit;
  Form2.ShowModal; //Opens the data entry form.
finally
  Query2.Close;
  Query2.Unprepare;
  {Run RunQueryClick procedure to refresh the grid after data entry.}
  RunQueryClick(Sender);
end;
end;


//From form2--the data entry form:

procedure TForm2.OkayClick(Sender: TObject);
begin
{This procedure applies the data in the cache to the database and
commits the changes.}
Form1.Query2.ApplyUpdates;
Form1.Database1.Commit;
Form2.Close;
end;

procedure TForm2.CancelClick(Sender: TObject);
begin
{This procedure cancels and removes the data from the cache and rolls
back the database to its former state.}
Form1.Query2.CancelUpdates;
Form1.Database1.Rollback;
Form2.Close;
end;

procedure TForm2.FormShow(Sender: TObject);
begin
{This procedure starts a new transaction--which ends with either
a database commit or rollback. It's important to check first if
a transaction is already in process before starting a new one, or
else an error will be generated.}
if Form1.Database1.InTransaction = False then
  Form1.Database1.StartTransaction;
end;

for Oracla's information about this, see :

http://developers.href.com/NEWS:THREAD::waThread.142.180111

Regards, Zif.
0
 
avicoCommented:
danil,

Oracle has an SQL command for locking a table :
LOCK TABLE <Table_Name> IN EXCLUSIVE MODE;

The lock is released at the end of the transaction (Meaning, COMMIT or ROLLBACK).

You can create a Query and set the SQL property for the above command.

Hope it helps, Avi.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.