fbintf/testsuite/Test3.pas

unit Test3;
{$IFDEF MSWINDOWS} 
{$DEFINE WINDOWS} 
{$ENDIF}

{$IFDEF FPC}
{$mode delphi}
{$codepage utf8}
{$ENDIF}

{ 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: AnsiString; override;
    procedure RunTest(CharSet: AnsiString; 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);

  {$IFNDEF FPC}
  Transaction.Rollback; {Delphi does not dispose of interfaces until the end of the function
                         so we need to explicitly rollback here. FPC will dispose of the
                         interface as soon as it is overwritten - hence this is not needed.}
  {$ENDIF}
  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: AnsiString;
begin
  Result := 'Test 3: ad hoc queries';
end;

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