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;
    us: UnicodeString;
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);

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

  us := UTF8Decode('Yanowski');   {Test a UnicodeString as a parameter}

  writeln(OutFile,'"Yanowski" Employee Count = ',Attachment.OpenCursorAtStart(
         Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
         'Select count(*) As Counter from EMPLOYEE Where LAST_NAME = ?',3,[us])[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:	70
Committed:	Thu Oct 26 12:59:51 2017 UTC (7 years, 1 month ago) by tony
Content type:	text/x-pascal
File size:	4815 byte(s)
Log Message:	FBAttachment: Avoid string reference count error when processing an Ansistring parameter in an array of const passed to IAttachment.OpenCursor, etc. FBAttachment: Allow for WideString and UnicodeString parameters in an array of const passed to IAttachment.OpenCursor, etc.
#	Content
1	unit Test3;
2	{$IFDEF MSWINDOWS}
3	{$DEFINE WINDOWS}
4	{$ENDIF}
5
6	{$IFDEF FPC}
7	{$mode delphi}
8	{$codepage utf8}
9	{$ENDIF}
10
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	function TestTitle: AnsiString; override;
43	procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
44	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	us: UnicodeString;
57	begin
58	writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE')[0].AsInteger);
59
60	TPB := FirebirdAPI.AllocateTPB;
61	TPB.Add(isc_tpb_write);
62	TPB.Add(isc_tpb_nowait);
63	TPB.Add(isc_tpb_concurrency);
64	TPB.Add(isc_tpb_lock_read).AsString := 'EMPLOYEE';
65	TPB.Add(isc_tpb_protected);
66	Transaction := Attachment.StartTransaction(TPB,taRollback);
67	Attachment.ExecuteSQL(Transaction, 'Execute Procedure DELETE_EMPLOYEE ?', [8]);
68
69	ResultSet := Attachment.OpenCursorAtStart(
70	Transaction,
71	'Select count(*) from EMPLOYEE',3);
72
73	writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
74
75	ResultSet := Attachment.OpenCursorAtStart('Select count(*) from EMPLOYEE');
76	writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
77
78	{$IFNDEF FPC}
79	Transaction.Rollback; {Delphi does not dispose of interfaces until the end of the function
80	so we need to explicitly rollback here. FPC will dispose of the
81	interface as soon as it is overwritten - hence this is not needed.}
82	{$ENDIF}
83	Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
84	Statement := Attachment.PrepareWithNamedParameters(Transaction,'Execute Procedure DELETE_EMPLOYEE :EMP_NO',3);
85	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
86	Statement.Execute;
87
88	ResultSet := Attachment.OpenCursorAtStart(
89	Transaction,
90	'Select count(*) from EMPLOYEE',3);
91
92	writeln(OutFile,'Employee Count = ',ResultSet[0].AsInteger);
93
94	Transaction := nil; {implicit rollback}
95
96
97	writeln(OutFile,'Employee Count = ',Attachment.OpenCursorAtStart(
98	Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
99	'Select count(*) As Counter from EMPLOYEE',3)[0].AsInteger);
100
101	writeln(OutFile,'Constrained Employee Count = ',Attachment.OpenCursorAtStart(
102	Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
103	'Select count(*) As Counter from EMPLOYEE Where EMP_NO < ?',3,[8])[0].AsInteger);
104
105	writeln(OutFile,'"Johnson" Employee Count = ',Attachment.OpenCursorAtStart(
106	Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
107	'Select count(*) As Counter from EMPLOYEE Where LAST_NAME = ?',3,['Johnson'])[0].AsInteger);
108
109	us := UTF8Decode('Yanowski'); {Test a UnicodeString as a parameter}
110
111	writeln(OutFile,'"Yanowski" Employee Count = ',Attachment.OpenCursorAtStart(
112	Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit),
113	'Select count(*) As Counter from EMPLOYEE Where LAST_NAME = ?',3,[us])[0].AsInteger);
114
115	end;
116
117	function TTest3.TestTitle: AnsiString;
118	begin
119	Result := 'Test 3: ad hoc queries';
120	end;
121
122	procedure TTest3.RunTest(CharSet: AnsiString; SQLDialect: integer);
123	var Attachment: IAttachment;
124	DPB: IDPB;
125	begin
126	DPB := FirebirdAPI.AllocateDPB;
127	DPB.Add(isc_dpb_user_name).AsString := Owner.GetUserName;
128	DPB.Add(isc_dpb_password).AsString := Owner.GetPassword;
129	DPB.Add(isc_dpb_lc_ctype).AsString := CharSet;
130	DPB.Add(isc_dpb_set_db_SQL_dialect).AsByte := SQLDialect;
131
132	writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
133	Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
134	writeln(OutFile,'Database Open');
135	DoQuery(Attachment);
136	end;
137
138	initialization
139	RegisterTest(TTest3);
140
141	end.
142