Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I lock oracle table form Delphi

Posted on 1999-01-08
4
Medium Priority
?
697 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 100 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

705 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