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 (7 years, 11 months ago) by tony
Content type:	text/x-pascal
File size:	3784 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	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