trunk/testsuite/Test20.pas

unit Test20;

{$mode objfpc}{$H+}

{Test 20: TIBUpdateSQL Tests}

{ Description
}

interface

uses
  Classes, SysUtils,   TestApplication, IBXTestBase, DB, IB,
  IBCustomDataSet, IBUpdateSQL, IBQuery;

const
  aTestID    = '20';
  aTestTitle = 'TIBUpdateSQL Tests';

type

{ TTest20 }

  TTest20 = class(TIBXTestBase)
  private
    FUpdateSQL: TIBUpdateSQL;
    FUpdateSQL2: TIBUpdateSQL;
    FIBQuery2: TIBQuery;
    procedure HandleDeleteReturning(Sender: TObject; QryResults: IResults);
  protected
    procedure CreateObjects(Application: TTestApplication); override;
    function GetTestID: AnsiString; override;
    function GetTestTitle: AnsiString; override;
    procedure InitTest; override;
  public
    procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
  end;


implementation

{ TTest20 }

procedure TTest20.HandleDeleteReturning(Sender: TObject; QryResults: IResults);
begin
  writeln(OutFile,'Delete Returning');
  ReportResult(QryResults);
  writeln(OutFile);
end;

procedure TTest20.CreateObjects(Application: TTestApplication);
begin
  inherited CreateObjects(Application);
  FUpdateSQL := TIBUpdateSQL.Create(Application);
  with IBQuery do
  begin
    UpdateObject := FUpdateSQL;
    SQL.Add('Select * From IBDataSetTest');
    GeneratorField.Field := 'KeyField';
    GeneratorField.Generator := 'AGENERATOR';
    GeneratorField.Increment := 1;
  end;
  with FUpdateSQL do
  begin
    InsertSQL.Add('Insert into IBDataSetTest(KeyField,PlainText) Values (:KeyField,:PlainText)');
    ModifySQL.Add('Update IBDataSetTest Set KeyField = :KeyField, PlainText = :PlainText Where KeyField = :Old_KeyField');
    DeleteSQL.Add('Delete from IBDataSetTest Where KeyField = :old_KeyField');
    RefreshSQL.Add('Select * from IBDataSetTest Where KeyField = :KeyField');
  end;
  FIBQuery2 := TIBQuery.Create(Application);
  FUpdateSQL2 := TIBUpdateSQL.Create(Application);
  with FIBQuery2 do
  begin
    Database := IBDatabase;
    Transaction := IBTransaction;
    UpdateObject := FUpdateSQL2;
    SQL.Add('Select * From IBDataSetTest');
    OnDeleteReturning := @HandleDeleteReturning;
  end;
  with FUpdateSQL2 do
  begin
    InsertSQL.Add('Insert into IBDataSetTest(KeyField,PlainText) Values (Gen_ID(AGenerator,1),:PlainText) Returning KeyField, TextAndKey');
    ModifySQL.Add('Update IBDataSetTest Set KeyField = :KeyField, PlainText = :PlainText Where KeyField = :Old_KeyField Returning TextAndKey,ServerSideText');
    DeleteSQL.Add('Delete from IBDataSetTest Where KeyField = :old_KeyField Returning KeyField');
    RefreshSQL.Add('Select * from IBDataSetTest Where KeyField = :KeyField');
  end;
end;

function TTest20.GetTestID: AnsiString;
begin
  Result := aTestID;
end;

function TTest20.GetTestTitle: AnsiString;
begin
  Result := aTestTitle;
end;

procedure TTest20.InitTest;
begin
  IBDatabase.DatabaseName := Owner.GetNewDatabaseName;
  IBDatabase.CreateIfNotExists := true;
  ReadWriteTransaction;
end;

procedure TTest20.RunTest(CharSet: AnsiString; SQLDialect: integer);
begin
  IBDatabase.CreateDatabase;
  try
    IBTransaction.Active := true;
    with IBQuery do
    begin
      Active := true;
      writeln(OutFile,'Simple Append');
      Append;
      FieldByName('PlainText').AsString := 'This is a test';
      Post;
      PrintDataSetRow(IBQuery);
      Refresh;
      writeln(OutFile,'After Refresh');
      PrintDataSetRow(IBQuery);
      writeln(OutFile,'Append and Update');
      Append;
      FieldByName('PlainText').AsString := 'This is another test';
      Post;
      PrintDataSetRow(IBQuery);
      Edit;
      FieldByName('PlainText').AsString := 'This is the update test';
      Post;
      PrintDataSetRow(IBQuery);
      writeln(OutFile,'Now delete the first row');
      PrintDataSet(IBQuery);
      First;
      Delete;
      PrintDataSet(IBQuery);
      writeln(Outfile,'On Post KeyField Assignment');
      GeneratorField.ApplyOnEvent := gaeOnPostRecord;
      Append;
      FieldByName('PlainText').AsString := 'On Post KeyField test';
      PrintDataSetRow(IBQuery);
      Post;
      writeln(Outfile,'Row data after post');
      PrintDataSetRow(IBQuery);
      GeneratorField.ApplyOnEvent := gaeOnNewRecord; {restore}
    end;
    IBTransaction.Rollback;

    writeln(Outfile);
    writeln(Outfile,'Repeat with cached updates');
    IBTransaction.Active := true;
    with IBQuery do
    begin
      CachedUpdates := true;
      Active := true;
      writeln(OutFile,'Simple Append');
      Append;
      FieldByName('PlainText').AsString := 'This is a test';
      Post;
      PrintDataSetRow(IBQuery);
      Refresh;
      writeln(OutFile,'After Refresh');
      PrintDataSetRow(IBQuery);
      writeln(OutFile,'Append and Update');
      Append;
      FieldByName('PlainText').AsString := 'This is another test';
      Post;
      PrintDataSetRow(IBQuery);
      Edit;
      FieldByName('PlainText').AsString := 'This is the update test';
      Post;
      PrintDataSetRow(IBQuery);
      writeln(OutFile,'Now delete the first row');
      PrintDataSet(IBQuery);
      First;
      Delete;
      PrintDataSet(IBQuery);
      ApplyUpdates;
      Active := false;
      Active := true;
      writeln(Outfile,'close and re-open and print again');
      PrintDataSet(IBQuery);
    end;
    IBTransaction.Active := true;
    with FIBQuery2 do
    begin
      Active := true;
      writeln(OutFile,'FIBQuery2: Simple Append');
      Append;
      FieldByName('PlainText').AsString := 'This is a test';
      Post;
      PrintDataSetRow(FIBQuery2);
      Refresh;
      writeln(OutFile,'FIBQuery2 Refresh');
      PrintDataSetRow(FIBQuery2);
      writeln(OutFile,'Append and Update');
      Append;
      FieldByName('PlainText').AsString := 'This is another test';
      Post;
      PrintDataSetRow(FIBQuery2);
      Edit;
      FieldByName('PlainText').AsString := 'This is the update test';
      Post;
      PrintDataSetRow(FIBQuery2);
      writeln(OutFile,'Now delete the first row');
      PrintDataSet(FIBQuery2);
      First;
      Delete;
      writeln(Outfile,'Dataset after delete');
      PrintDataSet(FIBQuery2);

    end;
  finally
    IBDatabase.DropDatabase;
  end;
end;

initialization
  RegisterTest(TTest20);

end.

Revision:	315
Committed:	Thu Feb 25 11:56:36 2021 UTC (3 years, 8 months ago) by tony
Content type:	text/x-pascal
File size:	6216 byte(s)
Log Message:	Updated for IBX 4 release
#	User	Rev	Content
1	tony	315	unit Test20;
2
3			{$mode objfpc}{$H+}
4
5			{Test 20: TIBUpdateSQL Tests}
6
7			{ Description
8			}
9
10			interface
11
12			uses
13			Classes, SysUtils, TestApplication, IBXTestBase, DB, IB,
14			IBCustomDataSet, IBUpdateSQL, IBQuery;
15
16			const
17			aTestID = '20';
18			aTestTitle = 'TIBUpdateSQL Tests';
19
20			type
21
22			{ TTest20 }
23
24			TTest20 = class(TIBXTestBase)
25			private
26			FUpdateSQL: TIBUpdateSQL;
27			FUpdateSQL2: TIBUpdateSQL;
28			FIBQuery2: TIBQuery;
29			procedure HandleDeleteReturning(Sender: TObject; QryResults: IResults);
30			protected
31			procedure CreateObjects(Application: TTestApplication); override;
32			function GetTestID: AnsiString; override;
33			function GetTestTitle: AnsiString; override;
34			procedure InitTest; override;
35			public
36			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
37			end;
38
39
40			implementation
41
42			{ TTest20 }
43
44			procedure TTest20.HandleDeleteReturning(Sender: TObject; QryResults: IResults);
45			begin
46			writeln(OutFile,'Delete Returning');
47			ReportResult(QryResults);
48			writeln(OutFile);
49			end;
50
51			procedure TTest20.CreateObjects(Application: TTestApplication);
52			begin
53			inherited CreateObjects(Application);
54			FUpdateSQL := TIBUpdateSQL.Create(Application);
55			with IBQuery do
56			begin
57			UpdateObject := FUpdateSQL;
58			SQL.Add('Select * From IBDataSetTest');
59			GeneratorField.Field := 'KeyField';
60			GeneratorField.Generator := 'AGENERATOR';
61			GeneratorField.Increment := 1;
62			end;
63			with FUpdateSQL do
64			begin
65			InsertSQL.Add('Insert into IBDataSetTest(KeyField,PlainText) Values (:KeyField,:PlainText)');
66			ModifySQL.Add('Update IBDataSetTest Set KeyField = :KeyField, PlainText = :PlainText Where KeyField = :Old_KeyField');
67			DeleteSQL.Add('Delete from IBDataSetTest Where KeyField = :old_KeyField');
68			RefreshSQL.Add('Select * from IBDataSetTest Where KeyField = :KeyField');
69			end;
70			FIBQuery2 := TIBQuery.Create(Application);
71			FUpdateSQL2 := TIBUpdateSQL.Create(Application);
72			with FIBQuery2 do
73			begin
74			Database := IBDatabase;
75			Transaction := IBTransaction;
76			UpdateObject := FUpdateSQL2;
77			SQL.Add('Select * From IBDataSetTest');
78			OnDeleteReturning := @HandleDeleteReturning;
79			end;
80			with FUpdateSQL2 do
81			begin
82			InsertSQL.Add('Insert into IBDataSetTest(KeyField,PlainText) Values (Gen_ID(AGenerator,1),:PlainText) Returning KeyField, TextAndKey');
83			ModifySQL.Add('Update IBDataSetTest Set KeyField = :KeyField, PlainText = :PlainText Where KeyField = :Old_KeyField Returning TextAndKey,ServerSideText');
84			DeleteSQL.Add('Delete from IBDataSetTest Where KeyField = :old_KeyField Returning KeyField');
85			RefreshSQL.Add('Select * from IBDataSetTest Where KeyField = :KeyField');
86			end;
87			end;
88
89			function TTest20.GetTestID: AnsiString;
90			begin
91			Result := aTestID;
92			end;
93
94			function TTest20.GetTestTitle: AnsiString;
95			begin
96			Result := aTestTitle;
97			end;
98
99			procedure TTest20.InitTest;
100			begin
101			IBDatabase.DatabaseName := Owner.GetNewDatabaseName;
102			IBDatabase.CreateIfNotExists := true;
103			ReadWriteTransaction;
104			end;
105
106			procedure TTest20.RunTest(CharSet: AnsiString; SQLDialect: integer);
107			begin
108			IBDatabase.CreateDatabase;
109			try
110			IBTransaction.Active := true;
111			with IBQuery do
112			begin
113			Active := true;
114			writeln(OutFile,'Simple Append');
115			Append;
116			FieldByName('PlainText').AsString := 'This is a test';
117			Post;
118			PrintDataSetRow(IBQuery);
119			Refresh;
120			writeln(OutFile,'After Refresh');
121			PrintDataSetRow(IBQuery);
122			writeln(OutFile,'Append and Update');
123			Append;
124			FieldByName('PlainText').AsString := 'This is another test';
125			Post;
126			PrintDataSetRow(IBQuery);
127			Edit;
128			FieldByName('PlainText').AsString := 'This is the update test';
129			Post;
130			PrintDataSetRow(IBQuery);
131			writeln(OutFile,'Now delete the first row');
132			PrintDataSet(IBQuery);
133			First;
134			Delete;
135			PrintDataSet(IBQuery);
136			writeln(Outfile,'On Post KeyField Assignment');
137			GeneratorField.ApplyOnEvent := gaeOnPostRecord;
138			Append;
139			FieldByName('PlainText').AsString := 'On Post KeyField test';
140			PrintDataSetRow(IBQuery);
141			Post;
142			writeln(Outfile,'Row data after post');
143			PrintDataSetRow(IBQuery);
144			GeneratorField.ApplyOnEvent := gaeOnNewRecord; {restore}
145			end;
146			IBTransaction.Rollback;
147
148			writeln(Outfile);
149			writeln(Outfile,'Repeat with cached updates');
150			IBTransaction.Active := true;
151			with IBQuery do
152			begin
153			CachedUpdates := true;
154			Active := true;
155			writeln(OutFile,'Simple Append');
156			Append;
157			FieldByName('PlainText').AsString := 'This is a test';
158			Post;
159			PrintDataSetRow(IBQuery);
160			Refresh;
161			writeln(OutFile,'After Refresh');
162			PrintDataSetRow(IBQuery);
163			writeln(OutFile,'Append and Update');
164			Append;
165			FieldByName('PlainText').AsString := 'This is another test';
166			Post;
167			PrintDataSetRow(IBQuery);
168			Edit;
169			FieldByName('PlainText').AsString := 'This is the update test';
170			Post;
171			PrintDataSetRow(IBQuery);
172			writeln(OutFile,'Now delete the first row');
173			PrintDataSet(IBQuery);
174			First;
175			Delete;
176			PrintDataSet(IBQuery);
177			ApplyUpdates;
178			Active := false;
179			Active := true;
180			writeln(Outfile,'close and re-open and print again');
181			PrintDataSet(IBQuery);
182			end;
183			IBTransaction.Active := true;
184			with FIBQuery2 do
185			begin
186			Active := true;
187			writeln(OutFile,'FIBQuery2: Simple Append');
188			Append;
189			FieldByName('PlainText').AsString := 'This is a test';
190			Post;
191			PrintDataSetRow(FIBQuery2);
192			Refresh;
193			writeln(OutFile,'FIBQuery2 Refresh');
194			PrintDataSetRow(FIBQuery2);
195			writeln(OutFile,'Append and Update');
196			Append;
197			FieldByName('PlainText').AsString := 'This is another test';
198			Post;
199			PrintDataSetRow(FIBQuery2);
200			Edit;
201			FieldByName('PlainText').AsString := 'This is the update test';
202			Post;
203			PrintDataSetRow(FIBQuery2);
204			writeln(OutFile,'Now delete the first row');
205			PrintDataSet(FIBQuery2);
206			First;
207			Delete;
208			writeln(Outfile,'Dataset after delete');
209			PrintDataSet(FIBQuery2);
210
211			end;
212			finally
213			IBDatabase.DropDatabase;
214			end;
215			end;
216
217			initialization
218			RegisterTest(TTest20);
219
220			end.
221