Devart Forums

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.