ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/branches/journaling/fbintf/testsuite/Test3.pas
Revision: 45
Committed: Tue Dec 6 10:33:46 2016 UTC (7 years, 3 months ago) by tony
Content type: text/x-pascal
Original Path: ibx/trunk/fbintf/testsuite/Test3.pas
File size: 3784 byte(s)
Log Message:
Committing updates for Release R2-0-0

File Contents

# Content
1 unit Test3;
2
3 {$mode objfpc}{$H+}
4 {$codepage utf8}
5
6 { Test 3: ad hoc queries}
7
8 { This test opens the employee example databases with the supplied user name/password
9 and runs several queries:
10
11 1. The convenience function OpenCursorAtStart is used to return a count of the
12 records in the employee database. This creates its own read only transaction.
13
14 2. A parameterised query is used to delete a record. The record count is repeated
15 using the same transaction as the deletion. The transaction is rolled back.
16
17 3. Rollback is demonstrated by returning a record count. This time creating the
18 transaction in place.
19
20 4. The above two steps are repeated but with a named parameter ad an implicit end to the transaction.
21
22 5. Note implicit disconnect on end
23 }
24
25 interface
26
27 uses
28 Classes, SysUtils, TestManager, IB;
29
30 type
31 { TTest3 }
32
33 TTest3 = class(TTestBase)
34 private
35 procedure DoQuery(Attachment: IAttachment);
36 public
37 function TestTitle: string; override;
38 procedure RunTest(CharSet: string; SQLDialect: integer); override;
39 end;
40
41
42 implementation
43
44 { TTest3 }
45
46 procedure TTest3.DoQuery(Attachment: IAttachment);
47 var Transaction: ITransaction;
48 ResultSet: IResultSet;
49 Statement: IStatement;
50 TPB: ITPB;
51 begin
52 writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE')[0].AsInteger);
53
54 TPB := FirebirdAPI.AllocateTPB;
55 TPB.Add(isc_tpb_write);
56 TPB.Add(isc_tpb_nowait);
57 TPB.Add(isc_tpb_concurrency);
58 TPB.Add(isc_tpb_lock_read).AsString := 'EMPLOYEE';
59 TPB.Add(isc_tpb_protected);
60 Transaction := Attachment.StartTransaction(TPB,taRollback);
61 Attachment.ExecuteSQL(Transaction, 'Execute Procedure DELETE_EMPLOYEE ?', [8]);
62
63 ResultSet := Attachment.OpenCursorAtStart(
64 Transaction,
65 'Select count(*) from EMPLOYEE',3);
66
67 writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
68
69 ResultSet := Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE');
70 writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
71
72 Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
73 Statement := Attachment.PrepareWithNamedParameters(Transaction,'Execute Procedure DELETE_EMPLOYEE :EMP_NO',3);
74 Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
75 Statement.Execute;
76
77 ResultSet := Attachment.OpenCursorAtStart(
78 Transaction,
79 'Select count(*) from EMPLOYEE',3);
80
81 writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
82
83 Transaction := nil; {implicit rollback}
84
85
86 writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart(
87 Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
88 'Select count(*) As Counter from EMPLOYEE',3)[0].AsInteger);
89
90 writeln(OutFile,'Constrained Employee Count = ',Attachment.OpenCursorAtStart(
91 Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
92 'Select count(*) As Counter from EMPLOYEE Where EMP_NO < ?',3,[8])[0].AsInteger);
93
94 end;
95
96 function TTest3.TestTitle: string;
97 begin
98 Result := 'Test 3: ad hoc queries';
99 end;
100
101 procedure TTest3.RunTest(CharSet: string; SQLDialect: integer);
102 var Attachment: IAttachment;
103 DPB: IDPB;
104 begin
105 DPB := FirebirdAPI.AllocateDPB;
106 DPB.Add(isc_dpb_user_name).AsString := Owner.GetUserName;
107 DPB.Add(isc_dpb_password).AsString := Owner.GetPassword;
108 DPB.Add(isc_dpb_lc_ctype).AsString := CharSet;
109 DPB.Add(isc_dpb_set_db_SQL_dialect).AsByte := SQLDialect;
110
111 writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
112 Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
113 writeln(OutFile,'Database Open');
114 DoQuery(Attachment);
115 end;
116
117 initialization
118 RegisterTest(TTest3);
119
120 end.
121