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

# User Rev Content
1 tony 45 unit Test4;
2 tony 56 {$IFDEF MSWINDOWS}
3     {$DEFINE WINDOWS}
4     {$ENDIF}
5 tony 45
6 tony 56 {$IFDEF FPC}
7     {$mode delphi}
8 tony 45 {$codepage utf8}
9 tony 56 {$ENDIF}
10 tony 45
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 tony 56 function TestTitle: AnsiString; override;
53     procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
54 tony 45 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 tony 47 stats: TPerfCounters;
66 tony 45 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 tony 265 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :F1',3);
77 tony 47 Statement.EnableStatistics(true);
78 tony 265 Statement.GetSQLParams.ByName('F1').AsInteger := 8;
79 tony 45 ReportResults(Statement);
80 tony 47 if Statement.GetPerfStatistics(stats) then
81     WritePerfStats(stats);
82 tony 45
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 tony 47 ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);
94 tony 45 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 tony 59 ByName('HIRE_DATE').AsString := '2015-4-1';
120 tony 45 ByName('DEPT_NO').AsString := '600';
121     ByName('JOB_CODE').AsString := 'Eng';
122     ByName('JOB_GRADE').AsInteger := 4;
123     ByName('JOB_COUNTRY').AsString := 'England';
124 tony 59 ByName('SALARY').AsString := '40000.59';
125 tony 45 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 tony 263
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 tony 45 end;
216    
217 tony 56 function TTest4.TestTitle: AnsiString;
218 tony 45 begin
219     Result := 'Test 4: Update, Insert and Delete Queries';
220     end;
221    
222 tony 56 procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
223 tony 45 var Attachment: IAttachment;
224     DPB: IDPB;
225 tony 266 S: TStrings;
226     i: integer;
227 tony 45 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 tony 266 // DPB.Add(isc_dpb_config).SetAsString('WireCompression=true');
234 tony 45
235     writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
236     Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
237     writeln(OutFile,'Database Open');
238 tony 266 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 tony 45 DoQuery(Attachment);
247     end;
248    
249     initialization
250     RegisterTest(TTest4);
251    
252     end.
253