ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/branches/udr/testsuite/Test4.pas
Revision: 59
Committed: Mon Mar 13 09:51:56 2017 UTC (7 years ago) by tony
Content type: text/x-pascal
Original Path: ibx/trunk/fbintf/testsuite/Test4.pas
File size: 7768 byte(s)
Log Message:

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     Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
77 tony 47 Statement.EnableStatistics(true);
78 tony 45 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
79     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     end;
198    
199 tony 56 function TTest4.TestTitle: AnsiString;
200 tony 45 begin
201     Result := 'Test 4: Update, Insert and Delete Queries';
202     end;
203    
204 tony 56 procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
205 tony 45 var Attachment: IAttachment;
206     DPB: IDPB;
207     begin
208     DPB := FirebirdAPI.AllocateDPB;
209     DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
210     DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
211     DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
212     DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
213    
214     writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
215     Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
216     writeln(OutFile,'Database Open');
217     DoQuery(Attachment);
218     end;
219    
220     initialization
221     RegisterTest(TTest4);
222    
223     end.
224