CachedUpdates

[cis-wurzen]

CachedUpdates doesn't work correct if a new record is inserted, the insert is applied (ApplyUpdates) and afterwards the same record is deleted and also applied. The record is only deleted in the local memory of the TSmartQuery but no sql is performed to delete the record in the database. The Record is still there. After refresh you will see him again. We are using ODAC 8.6.11.
See sample.

Code: Select all

program OdacCachedUpdSample;

{$APPTYPE CONSOLE}

uses
  DB, Ora, OraScript, OraSmart, sysutils;

procedure DropOracleTables(ASession: TOraSession);
var
  OraScript: TOraScript;
begin
  //script for deleting of Oracle tables
  OraScript := TOraScript.Create(nil);
  try
    OraScript.Session := ASession;
    try
      OraScript.SQL.Text := 'DROP TABLE A;';
      OraScript.Execute;
    except
      //maybe the table don'nt exists
    end;
  finally
    OraScript.Free;
  end;
end;
  
procedure CreateOracleTables(ASession: TOraSession);
var
  OraScript: TOraScript;
begin
  //script for creation of Oracle tables
  DropOracleTables(ASession);
  //script for creation of Oracle tables
  OraScript := TOraScript.Create(nil);
  try
    OraScript.Session := ASession;
    OraScript.SQL.BeginUpdate;
    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.EndUpdate;
    OraScript.Execute;
  finally
    OraScript.Free;
  end;
end;

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

    //Step 1: append new record and apply changes
    qr.Append;
    qr.FieldByName('ID').AsInteger    := 1;
    qr.FieldByName('FIELD1').AsString := 'Test';
    qr.Post;
    qr.ApplyUpdates;

    //Step 2: delete the appended and applied Record and then apply changes again
    qr.Delete;
    qr.ApplyUpdates;

    //Step 3: check if the record is really deleted
    qr.Refresh;
    if not qr.IsEmpty then
      Writeln('ApplyUpdates after Delete: Failed! Record not deleted.')
    else
      Writeln('ApplyUpdates after Delete: OK');
  finally
    qr.Free;
    DropOracleTables(ASession);
  end;
  Readln;
end;

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

  Session := TOraSession.Create(nil);
  try
    //logon to Database
    Session.LoginPrompt := False;
    Session.UserName    := UserName;
    Session.Password    := Password;
    Session.Server      := Server;
    Session.Open;
  
    //run test
    RunDemonstration(Session);
  
  finally
    Session.Free;
  end;
end.

[AlexP]

Hello,

To resolve the problem you should call the CommitUpdates method to clear the cached updates buffer after successful call to ApplyUpdates and database component's Commit methods, or you can call only the CommitUpdates method, and it will call the ApplyUpdates method automatically.