fbintf/testsuite/Test3.pas

unit Test3;

{$mode objfpc}{$H+}
{$codepage utf8}

{ Test 3: ad hoc queries}

{ This test opens the employee example databases with the supplied user name/password
  and runs several queries:

  1. The convenience function OpenCursorAtStart is used to return a count of the
     records in the employee database. This creates its own read only transaction.

  2. A parameterised query is used to delete a record. The record count is repeated
     using the same transaction as the deletion. The transaction is rolled back.

  3. Rollback is demonstrated by returning a record count. This time creating the
     transaction in place.

  4. The above two steps are repeated but with a named parameter ad an implicit end to the transaction.

  5. Note implicit disconnect on end
}

interface

uses
  Classes, SysUtils, TestManager, IB;

type
  { TTest3 }

  TTest3 = class(TTestBase)
  private
    procedure DoQuery(Attachment: IAttachment);
  public
    function TestTitle: string; override;
    procedure RunTest(CharSet: string; SQLDialect: integer); override;
  end;


implementation

{ TTest3 }

procedure TTest3.DoQuery(Attachment: IAttachment);
var Transaction: ITransaction;
    ResultSet: IResultSet;
    Statement: IStatement;
    TPB: ITPB;
begin
  writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE')[0].AsInteger);

  TPB := FirebirdAPI.AllocateTPB;
  TPB.Add(isc_tpb_write);
  TPB.Add(isc_tpb_nowait);
  TPB.Add(isc_tpb_concurrency);
  TPB.Add(isc_tpb_lock_read).AsString := 'EMPLOYEE';
  TPB.Add(isc_tpb_protected);
  Transaction := Attachment.StartTransaction(TPB,taRollback);
  Attachment.ExecuteSQL(Transaction, 'Execute Procedure DELETE_EMPLOYEE ?', [8]);

  ResultSet := Attachment.OpenCursorAtStart(
         Transaction,
         'Select count(*) from EMPLOYEE',3);

  writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);

  ResultSet := Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE');
  writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);

  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
  Statement := Attachment.PrepareWithNamedParameters(Transaction,'Execute Procedure DELETE_EMPLOYEE :EMP_NO',3);
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
  Statement.Execute;

  ResultSet := Attachment.OpenCursorAtStart(
         Transaction,
         'Select count(*) from EMPLOYEE',3);

  writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);

  Transaction := nil; {implicit rollback}


  writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart(
         Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
         'Select count(*) As Counter from EMPLOYEE',3)[0].AsInteger);

  writeln(OutFile,'Constrained Employee Count = ',Attachment.OpenCursorAtStart(
         Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
         'Select count(*) As Counter from EMPLOYEE Where EMP_NO < ?',3,[8])[0].AsInteger);

end;

function TTest3.TestTitle: string;
begin
  Result := 'Test 3: ad hoc queries';
end;

procedure TTest3.RunTest(CharSet: string; SQLDialect: integer);
var Attachment: IAttachment;
    DPB: IDPB;
begin
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_user_name).AsString := Owner.GetUserName;
  DPB.Add(isc_dpb_password).AsString := Owner.GetPassword;
  DPB.Add(isc_dpb_lc_ctype).AsString := CharSet;
  DPB.Add(isc_dpb_set_db_SQL_dialect).AsByte := SQLDialect;

  writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
  Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
  writeln(OutFile,'Database Open');
  DoQuery(Attachment);
end;

initialization
  RegisterTest(TTest3);

end.

Revision:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (8 years ago) by tony
Content type:	text/x-pascal
File size:	3784 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	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