ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/branches/udr/testsuite/Test4.pas
Revision: 266
Committed: Wed Dec 26 18:34:32 2018 UTC (5 years, 3 months ago) by tony
Content type: text/x-pascal
Original Path: ibx/trunk/fbintf/testsuite/Test4.pas
File size: 8822 byte(s)
Log Message:
Fixes Merged

File Contents

# Content
1 unit Test4;
2 {$IFDEF MSWINDOWS}
3 {$DEFINE WINDOWS}
4 {$ENDIF}
5
6 {$IFDEF FPC}
7 {$mode delphi}
8 {$codepage utf8}
9 {$ENDIF}
10
11 {Test 4: Update, Insert and Delete Queries}
12
13 { This test opens the employee example databases with the supplied user name/password
14 and runs several queries:
15
16 1. Update an employee record and report affected rows.
17
18 2. Show Changed Record
19
20 3. Insert new employee record and report affected rows Repeat with a duplicated
21 parameter name.
22
23 4. Show inserted record and then delete it and report affected rows
24
25 5. Repeat insert with a null PHONE_EXT.
26
27 6. Show inserted record and total records
28
29 7. Prepare query again and report results
30
31 8. Prepare query with a different transaction and report results.
32
33 9. Open Cursor with a different transaction and report results.
34
35 10. Implicit Rollback and disconnect.
36
37 }
38
39 interface
40
41 uses
42 Classes, SysUtils, TestManager, IB;
43
44 type
45
46 { TTest4 }
47
48 TTest4 = class(TTestBase)
49 private
50 procedure DoQuery(Attachment: IAttachment);
51 public
52 function TestTitle: AnsiString; override;
53 procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
54 end;
55
56
57 implementation
58
59 { TTest4 }
60
61 procedure TTest4.DoQuery(Attachment: IAttachment);
62 var Transaction, Transaction2, Transaction3: ITransaction;
63 Statement: IStatement;
64 Rows: IResultSet;
65 stats: TPerfCounters;
66 begin
67 Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
68 Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ?',3);
69 Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
70 Statement.GetSQLParams[1].AsInteger := 8;
71 Statement.Execute;
72 WriteAffectedRows(Statement);
73 Transaction.Rollback;
74 Transaction.Start(TARollback);
75
76 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :F1',3);
77 Statement.EnableStatistics(true);
78 Statement.GetSQLParams.ByName('F1').AsInteger := 8;
79 ReportResults(Statement);
80 if Statement.GetPerfStatistics(stats) then
81 WritePerfStats(stats);
82
83 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
84 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
85 'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
86 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
87 with Statement.GetSQLParams do
88 begin
89 ByName('EMP_NO').AsInteger := 150;
90 ByName('FIRST_NAME').AsString := 'John';
91 ByName('LAST_NAME').AsString := 'Doe';
92 ByName('PHONE_EXT').AsString := '';
93 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);
94 ByName('DEPT_NO').AsString := '600';
95 ByName('JOB_CODE').AsString := 'Eng';
96 ByName('JOB_GRADE').AsInteger := 4;
97 ByName('JOB_COUNTRY').AsString := 'England';
98 ByName('SALARY').AsFloat := 41000.89;
99 end;
100 Statement.Execute;
101 WriteAffectedRows(Statement);
102
103 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
104 writeln(OutFile,'Relation Name = ',Statement.Metadata.GetUniqueRelationName);
105 PrintMetaData(Statement.GetMetaData);
106 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
107 ReportResults(Statement);
108
109 {Now repeat but with a non-unique parameter name}
110 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
111 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
112 'VALUES (:EMP_NO, :FIRST_NAME, :FIRST_NAME, :PHONE_EXT, :HIRE_DATE,' +
113 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
114 with Statement.GetSQLParams do
115 begin
116 ByName('EMP_NO').AsInteger := 151;
117 ByName('FIRST_NAME').AsString := 'Major';
118 ByName('PHONE_EXT').AsString := '';
119 ByName('HIRE_DATE').AsString := '2015-4-1';
120 ByName('DEPT_NO').AsString := '600';
121 ByName('JOB_CODE').AsString := 'Eng';
122 ByName('JOB_GRADE').AsInteger := 4;
123 ByName('JOB_COUNTRY').AsString := 'England';
124 ByName('SALARY').AsString := '40000.59';
125 end;
126 Statement.Execute;
127 WriteAffectedRows(Statement);
128
129 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
130 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 151;
131 ReportResults(Statement);
132
133 writeln(OutFile,'Now Delete the rows');
134 Statement := Attachment.Prepare(Transaction,'Delete From Employee Where EMP_NO = ?',3);
135 Statement.GetSQLParams[0].AsInteger := 150;
136 Statement.Execute;
137 WriteAffectedRows(Statement);
138 Statement.GetSQLParams[0].AsInteger := 151;
139 Statement.Execute;
140 WriteAffectedRows(Statement);
141
142 {Now again but with a null}
143 Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
144 'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
145 'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
146 ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
147 with Statement.GetSQLParams do
148 begin
149 ByName('EMP_NO').AsInteger := 150;
150 ByName('FIRST_NAME').AsString := 'Jane';
151 ByName('LAST_NAME').AsString := 'Doe';
152 ByName('PHONE_EXT').Clear;
153 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
154 ByName('DEPT_NO').AsString := '600';
155 ByName('JOB_CODE').AsString := 'Eng';
156 ByName('JOB_GRADE').AsInteger := 4;
157 ByName('JOB_COUNTRY').AsString := 'England';
158 ByName('SALARY').AsFloat := 41000.89;
159 end;
160 writeln(OutFile,'Inserting');
161 Statement.Execute;
162 WriteAffectedRows(Statement);
163
164 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
165 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
166 ReportResults(Statement);
167
168 writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
169 'Select count(*) from EMPLOYEE',3)[0].AsInteger);
170
171 writeln(OutFile,'Prepare Query again');
172 writeln(OutFile);
173 Statement.Prepare;
174 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
175 ReportResults(Statement);
176
177 Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
178 writeln(OutFile,'Prepare Query again with a different transaction');
179 writeln(OutFile);
180 Statement.Prepare(Transaction2);
181 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
182 ReportResults(Statement);
183
184 Transaction3 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
185 writeln(OutFile,'Open Cursor with a different transaction');
186 writeln(OutFile);
187 Rows := Statement.OpenCursor(Transaction3);
188 try
189 while Rows.FetchNext do
190 ReportResult(Rows);
191 finally
192 Rows.Close;
193 end;
194 writeln(OutFile,'Same Statement - updated params');
195 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 9;
196 ReportResults(Statement);
197
198 writeln(outfile,'Test using Execute Block');
199
200 Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
201 Statement := Attachment.PrepareWithNamedParameters(Transaction,
202 'Execute Block (Hired Timestamp = :Hire_Date, empno integer = :EMP_NO) '+
203 'As Begin ' +
204 ' Update Employee Set Hire_Date = :Hired Where EMP_NO = :empno; '+
205 'End'
206 ,3);
207 Statement.GetSQLParams.ByName('Hire_Date').AsDateTime := EncodeDate(2015,1,31);;
208 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
209 Statement.Execute;
210 WriteAffectedRows(Statement);
211 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
212 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
213 ReportResults(Statement);
214
215 end;
216
217 function TTest4.TestTitle: AnsiString;
218 begin
219 Result := 'Test 4: Update, Insert and Delete Queries';
220 end;
221
222 procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
223 var Attachment: IAttachment;
224 DPB: IDPB;
225 S: TStrings;
226 i: integer;
227 begin
228 DPB := FirebirdAPI.AllocateDPB;
229 DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
230 DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
231 DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
232 DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
233 // DPB.Add(isc_dpb_config).SetAsString('WireCompression=true');
234
235 writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
236 Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
237 writeln(OutFile,'Database Open');
238 S := TStringList.Create;
239 try
240 Attachment.getFBVersion(S);
241 for i := 0 to S.Count -1 do
242 writeln(OutFile,S[i]);
243 finally
244 S.Free;
245 end;
246 DoQuery(Attachment);
247 end;
248
249 initialization
250 RegisterTest(TTest4);
251
252 end.
253