1 |
unit Test5;
|
2 |
{$IFDEF MSWINDOWS}
|
3 |
{$DEFINE WINDOWS}
|
4 |
{$ENDIF}
|
5 |
|
6 |
{$IFDEF FPC}
|
7 |
{$mode delphi}
|
8 |
{$codepage utf8}
|
9 |
{$ENDIF}
|
10 |
|
11 |
{Test 5: Update/Insert Returning and Activity Check}
|
12 |
|
13 |
{ This test opens the employee example databases with the supplied user name/password,
|
14 |
reconnects, and runs several queries:
|
15 |
|
16 |
1. Update an employee record, return LAST_NAME,and report affected rows.
|
17 |
|
18 |
2. Show Changed Record
|
19 |
|
20 |
3. Insert new employee record, return FULL_NAME and report affected rows.
|
21 |
|
22 |
4. Show inserted record
|
23 |
|
24 |
5. Check attachment and transaction activity and ensure reset.
|
25 |
|
26 |
6. Show total records and confirm attachment and transaction activity
|
27 |
|
28 |
7. Implicit Rollback and disconnect.
|
29 |
|
30 |
}
|
31 |
|
32 |
interface
|
33 |
|
34 |
uses
|
35 |
Classes, SysUtils, TestManager, IB;
|
36 |
|
37 |
type
|
38 |
|
39 |
{ TTest5 }
|
40 |
|
41 |
TTest5 = class(TTestBase)
|
42 |
private
|
43 |
procedure DoQuery(Attachment: IAttachment);
|
44 |
public
|
45 |
function TestTitle: AnsiString; override;
|
46 |
procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
|
47 |
end;
|
48 |
|
49 |
implementation
|
50 |
|
51 |
{ TTest5 }
|
52 |
|
53 |
procedure TTest5.DoQuery(Attachment: IAttachment);
|
54 |
var Transaction: ITransaction;
|
55 |
Statement: IStatement;
|
56 |
Results: IResults;
|
57 |
begin
|
58 |
Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
|
59 |
Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ? Returning LAST_NAME',3);
|
60 |
Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
|
61 |
Statement.GetSQLParams[1].AsInteger := 8;
|
62 |
Results := Statement.Execute;
|
63 |
WriteAffectedRows(Statement);
|
64 |
writeln(OutFile,'Last Name = ',Results[0].AsString);
|
65 |
|
66 |
Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
|
67 |
Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
|
68 |
ReportResults(Statement);
|
69 |
|
70 |
Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
|
71 |
'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
|
72 |
'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
|
73 |
':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY) Returning FULL_NAME',3);
|
74 |
Transaction.RollbackRetaining;
|
75 |
with Statement.GetSQLParams do
|
76 |
begin
|
77 |
ByName('EMP_NO').AsInteger := 150;
|
78 |
ByName('FIRST_NAME').AsString := 'John';
|
79 |
ByName('LAST_NAME').AsString := 'Doe';
|
80 |
ByName('PHONE_EXT').AsString := '666';
|
81 |
ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
|
82 |
ByName('DEPT_NO').AsString := '600';
|
83 |
ByName('JOB_CODE').AsString := 'Eng';
|
84 |
ByName('JOB_GRADE').AsInteger := 4;
|
85 |
ByName('JOB_COUNTRY').AsString := 'England';
|
86 |
ByName('SALARY').AsFloat := 41000.89;
|
87 |
end;
|
88 |
writeln(OutFile,'Inserting');
|
89 |
Results := Statement.Execute;
|
90 |
writeln(OutFile,'Full Name = ',Results[0].AsString);
|
91 |
WriteAffectedRows(Statement);
|
92 |
CheckActivity(Statement.GetAttachment);
|
93 |
CheckActivity(Transaction);
|
94 |
CheckActivity(Statement.GetAttachment);
|
95 |
CheckActivity(Transaction);
|
96 |
|
97 |
writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
|
98 |
'Select count(*) from EMPLOYEE',3)[0].AsInteger);
|
99 |
CheckActivity(Statement.GetAttachment);
|
100 |
CheckActivity(Transaction);
|
101 |
if Transaction.InTransaction then
|
102 |
writeln(OutFile,'Transaction Active')
|
103 |
else
|
104 |
writeln(OutFile,'Transaction inactive');
|
105 |
Transaction.Rollback;
|
106 |
if Transaction.InTransaction then
|
107 |
writeln(OutFile,'Transaction Active')
|
108 |
else
|
109 |
writeln(OutFile,'Transaction inactive');
|
110 |
end;
|
111 |
|
112 |
function TTest5.TestTitle: AnsiString;
|
113 |
begin
|
114 |
Result := 'Test 5: Update Returning and Activity Check';
|
115 |
end;
|
116 |
|
117 |
procedure TTest5.RunTest(CharSet: AnsiString; SQLDialect: integer);
|
118 |
var Attachment: IAttachment;
|
119 |
DPB: IDPB;
|
120 |
begin
|
121 |
DPB := FirebirdAPI.AllocateDPB;
|
122 |
DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
|
123 |
DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
|
124 |
DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
|
125 |
DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
|
126 |
|
127 |
writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
|
128 |
Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
|
129 |
writeln(OutFile,'Database Open');
|
130 |
Attachment.Disconnect;
|
131 |
writeln(OutFile,'Database Closed');
|
132 |
Attachment.Connect;
|
133 |
writeln(OutFile,'Database Open');
|
134 |
DoQuery(Attachment);
|
135 |
end;
|
136 |
|
137 |
initialization
|
138 |
RegisterTest(TTest5);
|
139 |
|
140 |
|
141 |
end.
|
142 |
|