ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/branches/udr/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

# User Rev Content
1 tony 45 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