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, 2 months ago) by tony
Content type:	text/x-pascal
File size:	6216 byte(s)
Log Message:	Updated for IBX 4 release
#	Content
1	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