ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/branches/udr/testsuite/Test4.pas
Revision: 362
Committed: Tue Dec 7 13:27:39 2021 UTC (2 years, 3 months ago) by tony
Content type: text/x-pascal
Original Path: ibx/branches/journaling/fbintf/testsuite/Test4.pas
File size: 10118 byte(s)
Log Message:
initiate test release

File Contents

# User Rev Content
1 tony 315 (*
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 tony 45 unit Test4;
30 tony 56 {$IFDEF MSWINDOWS}
31     {$DEFINE WINDOWS}
32     {$ENDIF}
33 tony 45
34 tony 56 {$IFDEF FPC}
35     {$mode delphi}
36 tony 45 {$codepage utf8}
37 tony 56 {$ENDIF}
38 tony 45
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 tony 315 Classes, SysUtils, TestApplication, FBTestApp, IB;
71 tony 45
72     type
73    
74     { TTest4 }
75    
76 tony 315 TTest4 = class(TFBTestBase)
77 tony 45 private
78     procedure DoQuery(Attachment: IAttachment);
79     public
80 tony 56 function TestTitle: AnsiString; override;
81     procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
82 tony 45 end;
83    
84    
85     implementation
86    
87     { TTest4 }
88    
89     procedure TTest4.DoQuery(Attachment: IAttachment);
90     var Transaction, Transaction2, Transaction3: ITransaction;
91 tony 270 Statement, Statement2: IStatement;
92 tony 45 Rows: IResultSet;
93 tony 47 stats: TPerfCounters;
94 tony 45 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 tony 265 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :F1',3);
105 tony 47 Statement.EnableStatistics(true);
106 tony 265 Statement.GetSQLParams.ByName('F1').AsInteger := 8;
107 tony 45 ReportResults(Statement);
108 tony 47 if Statement.GetPerfStatistics(stats) then
109     WritePerfStats(stats);
110 tony 45
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 tony 47 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);
122 tony 45 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 tony 59 ByName('HIRE_DATE').AsString := '2015-4-1';
148 tony 45 ByName('DEPT_NO').AsString := '600';
149     ByName('JOB_CODE').AsString := 'Eng';
150     ByName('JOB_GRADE').AsInteger := 4;
151     ByName('JOB_COUNTRY').AsString := 'England';
152 tony 59 ByName('SALARY').AsString := '40000.59';
153 tony 45 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 tony 270 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 tony 45 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 tony 263
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 tony 270 Statement.GetSQLParams.ByName('emp_no').AsInteger := 8;
247 tony 263 ReportResults(Statement);
248    
249 tony 45 end;
250    
251 tony 56 function TTest4.TestTitle: AnsiString;
252 tony 45 begin
253     Result := 'Test 4: Update, Insert and Delete Queries';
254     end;
255    
256 tony 56 procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
257 tony 45 var Attachment: IAttachment;
258     DPB: IDPB;
259 tony 266 S: TStrings;
260     i: integer;
261 tony 45 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 tony 267 DPB.Add(isc_dpb_config).SetAsString('WireCompression=true');
268 tony 45
269     writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
270     Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
271     writeln(OutFile,'Database Open');
272 tony 266 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 tony 45 DoQuery(Attachment);
281     end;
282    
283     initialization
284     RegisterTest(TTest4);
285    
286     end.
287