Solved

How do I lock oracle table form Delphi

Posted on 1999-01-08
4
676 Views
Last Modified: 2010-08-05
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
0
Comment
Question by:danil082697
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355435
hi danil,

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

meikl

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1355436
you normally don't need to lock. Oracle does it all by itself.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1355437
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
 
LVL 2

Accepted Solution

by:
avico earned 50 total points
ID: 1355438
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question