Devart Forums

Posted: **Wed 23 Jan 2013 13:16**

The refresh option [roBeforeEdit] in TSmartQuery doesn't work if the LockMode is set to lmLockImmediate. No RefreshRecord is performed. Only when the LockMode is set to lmNone it works. We are using ODAC 8.6.11.
See sample.

Code: Select all


program OdacRefreshBeforeEditSample;

{$APPTYPE CONSOLE}

uses
  DB, Ora, OraScript, OraSmart, DBAccess, sysutils;

procedure CreateOracleTables(ASession: TOraSession);
var
  OraScript: TOraScript;
begin
  //script for creation of Oracle tables
  OraScript := TOraScript.Create(nil);
  try
    OraScript.Session := ASession;
    OraScript.SQL.BeginUpdate;

    OraScript.SQL.Add('DROP TABLE A;');
    OraScript.SQL.Add('CREATE TABLE A (');
    OraScript.SQL.Add('  ID      NUMBER(9) NOT NULL,');
    OraScript.SQL.Add('  FIELD1  VARCHAR2(40));');
    OraScript.SQL.Add('ALTER TABLE A ADD (CONSTRAINT PK_A_ID PRIMARY KEY (ID));');

    OraScript.SQL.Add('INSERT INTO A (ID, FIELD1) VALUES (1, ''Test'');');
    OraScript.SQL.Add('COMMIT;');

    OraScript.SQL.EndUpdate;
    OraScript.Execute;
  finally
    OraScript.Free;
  end;
end;

procedure RunDemonstration(Session: TOraSession);
var
  qr1,qr2: TSmartQuery;
begin
  qr1 := TSmartQuery.Create(nil);
  qr2 := TSmartQuery.Create(nil);
  try
    qr1.Session        := Session;
    qr1.SQL.Text       := 'SELECT * FROM A';
    qr1.KeyFields      := 'ID';
    qr1.LockMode       := lmLockImmediate;
    qr1.RefreshOptions := [roAfterInsert,roAfterUpdate,roBeforeEdit];
    qr1.Open;

    qr2.Session   := Session;
    qr2.SQL.Text  := 'SELECT * FROM A';
    qr2.KeyFields := 'ID';
    qr2.LockMode  := lmLockImmediate;
    qr2.RefreshOptions := [roAfterInsert,roAfterUpdate,roBeforeEdit];
    //qr2.LockMode  := Ora.lmNone;
    qr2.RefreshOptions := [roAfterInsert,roAfterUpdate,roBeforeEdit];
    qr2.Open;
    
    //Step 1: Both queries are open and show the same record. Now we update the record in only one query and post it.
    qr1.Edit;
    qr1.FieldByName('FIELD1').AsString := 'Updated';
    qr1.Post;

    //Step 2: Now we edit the same record in the other query
    qr2.Edit;
    if qr1.FieldByName('FIELD1').AsString <> qr2.FieldByName('FIELD1').AsString then //check if the record is refreshed
    begin
      Writeln('No RefreshBeforeEdit was performed!!!!');
      Readln;
      //RefreshOption contain [roBeforeEdit] but no RefreshRecord is performed before edit.
      //If the LockMode of qr2 is set from lmLockImmediate to lmNone the RefreshBeforeEdit works.
      //So I have to decide if I want to lock or if I want a RefreshBeforeEdit.
    end else
    begin
      Writeln('RefreshBeforeEdit works.');
      Readln;
    end;
  finally
    qr1.Free;
    qr2.Free;
  end;
end;


var
  UserName,Password,Server: String;
  OraSession: TOraSession;
begin
  //Logon information - Please fill out !!!!
  UserName := '';
  Password := '';
  Server   := '';

  OraSession := TOraSession.Create(nil);
  try
    //logon to Database
    OraSession.LoginPrompt := False;
    OraSession.UserName    := UserName;
    OraSession.Password    := Password;
    OraSession.Server      := Server;
    OraSession.AutoCommit  := True;
    OraSession.Open;

    //creation of needed Oracle tables
    CreateOracleTables(OraSession);

    //run test
    RunDemonstration(OraSession);

  finally
    OraSession.Free;
  end;
end.

Posted: **Wed 23 Jan 2013 14:59**

Hello,

Thank you for the information, we have reproduced and fixed the problem, this fix will be included in the next build.
In addition, when using the lmLockImmediate block mode, you do not need to additionally update data, as when calling the Edit method, the SELECT ..... FOR UPDATE NOWAIT query will be invoked to update the necessary data. I.e., in this mode, there is no need to set the RefreshOptions property to roBeforeEdit.

Posted: **Wed 23 Jan 2013 15:18**

OK thanks. But if I set the Lockmode=lmLockImmediate and the RefreshOptions=[] in my example it don't work too.

Posted: **Wed 23 Jan 2013 15:29**

Hello,

Yes, your code won't work for the time being, not depending on whether RefreshOptions are installed or not. After we applied changes, the code will work even without the roBeforeEdit option. If you have an edition with source code, I can send you the code for solving the problem.

Posted: **Wed 23 Jan 2013 15:48**

Yes we have an edition with source code. It would be helpfull for us if you can send the solving code. Thanks.

Posted: **Thu 24 Jan 2013 08:40**

Hello,

To fix this problem, you should replace the following code in the OraServices.pas module, in the TCustomOraSQLGenerator.GenerateLockSQL; method

Code: Select all

// SELECT * FOR UPDATE NOWAIT = // SELECT rowid FOR UPDATE NOWAIT
  FHeaderSB.Append('SELECT rowid FROM ');
  FHeaderSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull,      FDataSet.Options.QuoteNames))

;
with

Code: Select all

  GenerateRefreshSQLSelectPart(KeyAndDataFields);
  FMiddleSB.Append(' FROM ');
  FMiddleSB.Append(SQLInfo.NormalizeName(FTableInfo.TableNameFull, FDataSet.Options.QuoteNames));

Posted: **Fri 25 Jan 2013 07:57**

Thanks, works now as expected.

Posted: **Fri 25 Jan 2013 09:14**

Hello,

Glad to see that the problem was solved. If you have any other questions, feel free to contact us.

Posted: **Fri 25 Jan 2013 09:51**

Hello,

I found another Problem with RefreshOptions in relation to CachedUpdates. There is a not
comprehensible behavior of TSmartQuery in CachedUpates with Lockmode=lmLockImmediate and
roBeforeEdit in RefreshOptions and also without roBeforeEdit. You said that there is no
need to set the RefreshOptions property to roBeforeEdit when using the lmLockImmediate
block mode. Because the lock statement will refresh the necessary data by itself.
But there are two Problems:

If I don't set the roBeforeEdit in RefreshOptions it works fine for the first Query. The
Data is refreshed before editing and after Post and ApplyUpdates the table in Oracle is
getting an Update. But if I try to edit the same Table with a second query with the same
otpions as query 1 no refresh is performed and the query holds the old data.
(See Test2 in my example)

But if I set the roBeforeEdit in RefreshOptions to solve the problem above the edited but
not applied Data in the first Query is discarded by RefreshRecord. The entered data is
lost. (See Test1 in my example)

Example

Code: Select all

program OdacRefreshBeforeEditSample;

{$APPTYPE CONSOLE}

uses
  DB, Ora, OraScript, OraSmart, DBAccess, sysutils;

procedure CreateOracleTables(ASession: TOraSession);
var
  OraScript: TOraScript;
begin
  //script for creation of Oracle tables
  OraScript := TOraScript.Create(nil);
  try
    OraScript.Session := ASession;
    OraScript.SQL.BeginUpdate;

    OraScript.SQL.Add('DROP TABLE A;');
    OraScript.SQL.Add('CREATE TABLE A (');
    OraScript.SQL.Add('  ID      NUMBER(9) NOT NULL,');
    OraScript.SQL.Add('  FIELD1  VARCHAR2(40));');
    OraScript.SQL.Add('ALTER TABLE A ADD (CONSTRAINT PK_A_ID PRIMARY KEY (ID));');

    OraScript.SQL.Add('INSERT INTO A (ID, FIELD1) VALUES (1, ''Test'');');
    OraScript.SQL.Add('COMMIT;');

    OraScript.SQL.EndUpdate;
    OraScript.Execute;
  finally
    OraScript.Free;
  end;
end;

procedure RunDemonstration(Session: TOraSession);
var
  qr1,qr2: TSmartQuery;
begin
  qr1 := TSmartQuery.Create(nil);
  qr2 := TSmartQuery.Create(nil);
  try
    qr1.Session       := Session;
    qr1.SQL.Text      := 'SELECT * FROM A';
    qr1.KeyFields     := 'ID';
    qr1.CachedUpdates := True;
    qr1.LockMode      := lmLockImmediate;

    qr2.Session       := Session;
    qr2.SQL.Text      := 'SELECT * FROM A';
    qr2.KeyFields     := 'ID';
    qr1.CachedUpdates := True;
    qr2.LockMode      := lmLockImmediate;

    //Test1 with RefreshOptions in roBeforeEdit
    qr1.Close;
    qr1.RefreshOptions := [roAfterInsert,roAfterUpdate,roBeforeEdit];
    qr1.Open;

    qr1.Edit; //First Edit
    qr1.FieldByName('FIELD1').AsString := 'Updated1';
    qr1.Post;

    qr1.Edit; //Second Edit, RefreshBeforeEdit is Called and all changes discarded
    if qr1.FieldByName('FIELD1').AsString <> 'Updated1' then
      Writeln('Test 1 - with roBeforeEdit: Failed! All Changes discarded by RefreshBeforeEdit.')
    else
      Writeln('Test 1 - with roBeforeEdit: OK');
    Readln;
    qr1.CancelUpdates;

    //Test 2 without RefreshOptions in roBeforeEdit, Refresh is made by Lock-Statement
    qr1.Close;
    qr2.Close;
    qr1.RefreshOptions := [roAfterInsert,roAfterUpdate];
    qr2.RefreshOptions := [roAfterInsert,roAfterUpdate];
    qr1.Open;
    qr2.Open;

    qr1.Edit; //Edit of the first Query
    qr1.FieldByName('FIELD1').AsString := 'Updated1';
    qr1.Post;
    qr1.ApplyUpdates;
    qr1.Session.Commit; //to play safe, data is written to Oracle

    qr2.Edit; //Edit of the second Query
    if qr1.FieldByName('FIELD1').AsString <> qr2.FieldByName('FIELD1').AsString then //check if the record is refreshed
      Writeln('Test 2 - without roBeforeEdit: Failed! No Refresh is performed.')
    else
      Writeln('Test 2 - without roBeforeEdit: OK');
    Readln;
  finally
    qr1.Free;
    qr2.Free;
  end;
end;

var
  UserName,Password,Server: String;
  OraSession: TOraSession;
begin
  //Logon information - Please fill out !!!!
  UserName := '';
  Password := '';
  Server   := '';

  OraSession := TOraSession.Create(nil);
  try
    //logon to Database
    OraSession.LoginPrompt := False;
    OraSession.UserName    := UserName;
    OraSession.Password    := Password;
    OraSession.Server      := Server;
    OraSession.AutoCommit  := True;
    OraSession.Open;

    //creation of needed Oracle tables
    CreateOracleTables(OraSession);

    //run test
    RunDemonstration(OraSession);

  finally
    OraSession.Free;
  end;
end.

Posted: **Fri 25 Jan 2013 15:51**

Hello,

Thank you for the information. We have reproduced the problem described in Test2, and will try to fix it as soon as possible. We will send you the code as soon as we fix the problem.
If you set RefreshOptions to RoBeforeEdit explicitly, the record will be re-read forced from the database, erasing the applied changes. This is the correct behaviour, therefore you have no need to set up this option in the lmLockImmediate block mode.

Posted: **Fri 08 Feb 2013 16:08**

Hello,

We have fixed the problem. Now, to update a record in the lmLockImmediate mode when calling the Edit method, you should set the CheckMode option to cmRefresh. The new verson with this fix will be released next week.

Devart Forums

RefreshBeforeEdit doesn't work with locking

RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking

Re: RefreshBeforeEdit doesn't work with locking