ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/fbintf/testsuite/Test4.pas
Revision: 315
Committed: Thu Feb 25 11:56:36 2021 UTC (3 years, 1 month ago) by tony
Content type: text/x-pascal
File size: 10118 byte(s)
Log Message:
Updated for IBX 4 release

File Contents

# Content
1 (*
2 * Firebird Interface (fbintf) Test suite. This program is used to
3 * test the Firebird Pascal Interface and provide a semi-automated
4 * pass/fail check for each test.
5 *
6 * The contents of this file are subject to the Initial Developer's
7 * Public License Version 1.0 (the "License"); you may not use this
8 * file except in compliance with the License. You may obtain a copy
9 * of the License here:
10 *
11 * http://www.firebirdsql.org/index.php?op=doc&id=idpl
12 *
13 * Software distributed under the License is distributed on an "AS
14 * IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
15 * implied. See the License for the specific language governing rights
16 * and limitations under the License.
17 *
18 * The Initial Developer of the Original Code is Tony Whyman.
19 *
20 * The Original Code is (C) 2016 Tony Whyman, MWA Software
21 * (http://www.mwasoftware.co.uk).
22 *
23 * All Rights Reserved.
24 *
25 * Contributor(s): ______________________________________.
26 *
27 *)
28
29 unit Test4;
30 {$IFDEF MSWINDOWS}
31 {$DEFINE WINDOWS}
32 {$ENDIF}
33
34 {$IFDEF FPC}
35 {$mode delphi}
36 {$codepage utf8}
37 {$ENDIF}
38
39 {Test 4: Update, Insert and Delete Queries}
40
41 { This test opens the employee example databases with the supplied user name/password
42 and runs several queries:
43
44 1. Update an employee record and report affected rows.
45
46 2. Show Changed Record
47
48 3. Insert new employee record and report affected rows Repeat with a duplicated
49 parameter name.
50
51 4. Show inserted record and then delete it and report affected rows
52
53 5. Repeat insert with a null PHONE_EXT.
54
55 6. Show inserted record and total records
56
57 7. Prepare query again and report results
58
59 8. Prepare query with a different transaction and report results.
60
61 9. Open Cursor with a different transaction and report results.
62
63 10. Implicit Rollback and disconnect.
64
65 }
66
67 interface
68
69 uses
70 Classes, SysUtils, TestApplication, FBTestApp, IB;
71
72 type
73
74 { TTest4 }
75
76 TTest4 = class(TFBTestBase)
77 private
78 procedure DoQuery(Attachment: IAttachment);
79 public
80 function TestTitle: AnsiString; override;
81 procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
82 end;
83
84
85 implementation
86
87 { TTest4 }
88
89 procedure TTest4.DoQuery(Attachment: IAttachment);
90 var Transaction, Transaction2, Transaction3: ITransaction;
91 Statement, Statement2: IStatement;
92 Rows: IResultSet;
93 stats: TPerfCounters;
94 begin
95 Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
96 Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ?',3);
97 Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
98 Statement.GetSQLParams[1].AsInteger := 8;
99 Statement.Execute;
100 WriteAffectedRows(Statement);
101 Transaction.Rollback;
102 Transaction.Start(TARollback);
103
104 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :F1',3);
105 Statement.EnableStatistics(true);
106 Statement.GetSQLParams.ByName('F1').AsInteger := 8;
107 ReportResults(Statement);
108 if Statement.GetPerfStatistics(stats) then
109 WritePerfStats(stats);
110
111 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
112 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
113 'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
114 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
115 with Statement.GetSQLParams do
116 begin
117 ByName('EMP_NO').AsInteger := 150;
118 ByName('FIRST_NAME').AsString := 'John';
119 ByName('LAST_NAME').AsString := 'Doe';
120 ByName('PHONE_EXT').AsString := '';
121 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);
122 ByName('DEPT_NO').AsString := '600';
123 ByName('JOB_CODE').AsString := 'Eng';
124 ByName('JOB_GRADE').AsInteger := 4;
125 ByName('JOB_COUNTRY').AsString := 'England';
126 ByName('SALARY').AsFloat := 41000.89;
127 end;
128 Statement.Execute;
129 WriteAffectedRows(Statement);
130
131 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
132 writeln(OutFile,'Relation Name = ',Statement.Metadata.GetUniqueRelationName);
133 PrintMetaData(Statement.GetMetaData);
134 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
135 ReportResults(Statement);
136
137 {Now repeat but with a non-unique parameter name}
138 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
139 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
140 'VALUES (:EMP_NO, :FIRST_NAME, :FIRST_NAME, :PHONE_EXT, :HIRE_DATE,' +
141 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
142 with Statement.GetSQLParams do
143 begin
144 ByName('EMP_NO').AsInteger := 151;
145 ByName('FIRST_NAME').AsString := 'Major';
146 ByName('PHONE_EXT').AsString := '';
147 ByName('HIRE_DATE').AsString := '2015-4-1';
148 ByName('DEPT_NO').AsString := '600';
149 ByName('JOB_CODE').AsString := 'Eng';
150 ByName('JOB_GRADE').AsInteger := 4;
151 ByName('JOB_COUNTRY').AsString := 'England';
152 ByName('SALARY').AsString := '40000.59';
153 end;
154 Statement.Execute;
155 WriteAffectedRows(Statement);
156
157 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
158 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 151;
159 ReportResults(Statement);
160
161 writeln(OutFile,'Now Delete the rows');
162 Statement := Attachment.Prepare(Transaction,'Delete From Employee Where EMP_NO = ?',3);
163 Statement.GetSQLParams[0].AsInteger := 150;
164 Statement.Execute;
165 WriteAffectedRows(Statement);
166 Statement.GetSQLParams[0].AsInteger := 151;
167 Statement.Execute;
168 WriteAffectedRows(Statement);
169
170 {Now again but with a null}
171 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
172 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
173 'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
174 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
175 with Statement.GetSQLParams do
176 begin
177 ByName('EMP_NO').AsInteger := 150;
178 ByName('FIRST_NAME').AsString := 'Jane';
179 ByName('LAST_NAME').AsString := 'Doe';
180 ByName('PHONE_EXT').Clear;
181 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
182 ByName('DEPT_NO').AsString := '600';
183 ByName('JOB_CODE').AsString := 'Eng';
184 ByName('JOB_GRADE').AsInteger := 4;
185 ByName('JOB_COUNTRY').AsString := 'England';
186 ByName('SALARY').AsFloat := 41000.89;
187 end;
188 writeln(OutFile,'Inserting');
189 Statement.Execute;
190 WriteAffectedRows(Statement);
191
192 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
193 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
194 ReportResults(Statement);
195
196 writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
197 'Select count(*) from EMPLOYEE',3)[0].AsInteger);
198
199 Statement2 := Attachment.PrepareWithNamedParameters(Transaction,'Update EMPLOYEE Set FIRST_NAME = ''Jayne''''s'' Where EMP_NO = :EMP_NO',3);
200 Statement2.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
201 writeln(OutFile,'Updating');
202 Statement2.Execute;
203 WriteAffectedRows(Statement);
204
205 writeln(OutFile,'Prepare Query again');
206 writeln(OutFile);
207 Statement.Prepare;
208 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
209 ReportResults(Statement);
210
211 Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
212 writeln(OutFile,'Prepare Query again with a different transaction');
213 writeln(OutFile);
214 Statement.Prepare(Transaction2);
215 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
216 ReportResults(Statement);
217
218 Transaction3 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
219 writeln(OutFile,'Open Cursor with a different transaction');
220 writeln(OutFile);
221 Rows := Statement.OpenCursor(Transaction3);
222 try
223 while Rows.FetchNext do
224 ReportResult(Rows);
225 finally
226 Rows.Close;
227 end;
228 writeln(OutFile,'Same Statement - updated params');
229 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 9;
230 ReportResults(Statement);
231
232 writeln(outfile,'Test using Execute Block');
233
234 Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
235 Statement := Attachment.PrepareWithNamedParameters(Transaction,
236 'Execute Block (Hired Timestamp = :Hire_Date, empno integer = :EMP_NO) '+
237 'As Begin ' +
238 ' Update Employee Set Hire_Date = :Hired Where EMP_NO = :empno; '+
239 'End'
240 ,3);
241 Statement.GetSQLParams.ByName('Hire_Date').AsDateTime := EncodeDate(2015,1,31);;
242 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
243 Statement.Execute;
244 WriteAffectedRows(Statement);
245 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
246 Statement.GetSQLParams.ByName('emp_no').AsInteger := 8;
247 ReportResults(Statement);
248
249 end;
250
251 function TTest4.TestTitle: AnsiString;
252 begin
253 Result := 'Test 4: Update, Insert and Delete Queries';
254 end;
255
256 procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
257 var Attachment: IAttachment;
258 DPB: IDPB;
259 S: TStrings;
260 i: integer;
261 begin
262 DPB := FirebirdAPI.AllocateDPB;
263 DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
264 DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
265 DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
266 DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
267 DPB.Add(isc_dpb_config).SetAsString('WireCompression=true');
268
269 writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
270 Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
271 writeln(OutFile,'Database Open');
272 S := TStringList.Create;
273 try
274 Attachment.getFBVersion(S);
275 for i := 0 to S.Count -1 do
276 writeln(OutFile,S[i]);
277 finally
278 S.Free;
279 end;
280 DoQuery(Attachment);
281 end;
282
283 initialization
284 RegisterTest(TTest4);
285
286 end.
287