Lock table and update help me

[alicinar]

The following code does not work.
How can I do?

OraQuery1.LockMode:=lmLockDelayed;
OraQuery1.sql.text:='select number_column from tableNAme where rownum=1';
OraQuery1.Open;
OraQuery1.Lock;
OraQuery1.SQLUpdate.Text:='update tableName set number_column=number_column+1';
OraQuery1.Post;
OraQuery1.UnLock;

[AlexP]

Hello,

If you want to modify records in the table using your query, you should invoke the TOraQuery.Edit method as well as assign the SQLUpdate.Text value. In addition, your sample has no sense, as after invoking the Lock method, only one record will be Locked, and your UPDATE will be applied to all the table. Please describe your issue in more details, and we will try to provide you with an appropriate sample.

[alicinar]

My table script(one table one columd one row)(not use oracle sequence):

Code: Select all

CREATE TABLE UOPUM_NUMARA(DEGER  NUMBER)

Unit1.dfm

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 159
  ClientWidth = 341
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Label1: TLabel
    Left = 56
    Top = 64
    Width = 31
    Height = 13
    Caption = 'Label1'
  end
  object Label2: TLabel
    Left = 56
    Top = 96
    Width = 31
    Height = 13
    Caption = 'Label2'
  end
  object Button1: TButton
    Left = 40
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Read'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Button2: TButton
    Left = 236
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Unlock'
    TabOrder = 1
    OnClick = Button2Click
  end
  object Button3: TButton
    Left = 136
    Top = 16
    Width = 75
    Height = 25
    Caption = 'Update'
    TabOrder = 2
    OnClick = Button3Click
  end
  object OraQuery1: TOraQuery
    SQLUpdate.Strings = (
      'UPDATE UOPUM_NUMARA SET DEGER = DEGER+1;')
    CachedUpdates = True
    LockMode = lmLockDelayed
    Left = 160
    Top = 56
  end
  object OraQuery2: TOraQuery
    SQL.Strings = (
      'select deger from uopum_numara where rownum=1')
    Left = 248
    Top = 56
  end
end

Unit1.pas:

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Data.DB, MemDS, DBAccess,
  Ora;

type
  TForm1 = class(TForm)
    OraQuery1: TOraQuery;
    Button1: TButton;
    Button2: TButton;
    Label1: TLabel;
    Button3: TButton;
    OraQuery2: TOraQuery;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
OraQuery1.Close;
OraQuery1.SQL.Text:='SELECT DEGER FROM UOPUM_NUMARA';
OraQuery1.Open;
OraQuery1.Lock;
Label1.Caption:=OraQuery1.Fields.Fields[0].AsString;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
OraQuery1.UnLock;
end;

procedure TForm1.Button3Click(Sender: TObject);

begin
OraQuery1.LockMode:=lmLockDelayed;
OraQuery1.Post;
OraQuery1.Close;

OraQuery2.Close;
OraQuery2.Open;
Label2.Caption:=OraQuery2.Fields.Fields[0].AsString;

end;

end.

[AlexP]

Hello,

in your case, there is no need to use two TOraQuery's and modify the SQLUpdate property, the following will be enough:

Code: Select all

OraQuery1.LockMode := lmLockDelayed;
OraQuery1.SQL.Text := 'SELECT DEGER FROM UOPUM_NUMARA';
OraQuery1.Open;
OraQuery1.Edit;
OraQuery1.FieldByName('DEGER').AsInteger := OraQuery1.FieldByName('DEGER').AsInteger + 1;
OraQuery1.Post;

[alicinar]

thanks alexp.