Solved

How do I lock oracle table form Delphi

Posted on 1999-01-08
4
658 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
  • 2
4 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
you normally don't need to lock. Oracle does it all by itself.
0
 
LVL 8

Expert Comment

by:ZifNab
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now