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 |
|
|
|