udr/testsuite/Test5.pas

(*
 *  Firebird Interface (fbintf) Test suite. This program is used to
 *  test the Firebird Pascal Interface and provide a semi-automated
 *  pass/fail check for each test.
 *
 *  The contents of this file are subject to the Initial Developer's
 *  Public License Version 1.0 (the "License"); you may not use this
 *  file except in compliance with the License. You may obtain a copy
 *  of the License here:
 *
 *    http://www.firebirdsql.org/index.php?op=doc&id=idpl
 *
 *  Software distributed under the License is distributed on an "AS
 *  IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
 *  implied. See the License for the specific language governing rights
 *  and limitations under the License.
 *
 *  The Initial Developer of the Original Code is Tony Whyman.
 *
 *  The Original Code is (C) 2016 Tony Whyman, MWA Software
 *  (http://www.mwasoftware.co.uk).
 *
 *  All Rights Reserved.
 *
 *  Contributor(s): ______________________________________.
 *
*)

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

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

{Test 5: Update/Insert Returning and Activity Check}

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

  1. Update an employee record, return LAST_NAME,and report affected rows.

  2. Show Changed Record

  3. Insert new employee record, return FULL_NAME and report affected rows.

  4. Show inserted record

  5. Check attachment and transaction activity and ensure reset.

  6. Show total records and confirm attachment and transaction activity

  7. Implicit Rollback and disconnect.

}

interface

uses
  Classes, SysUtils, TestApplication, FBTestApp, IB;

type

  { TTest5 }

  TTest5 = class(TFBTestBase)
  private
    procedure DoQuery(Attachment: IAttachment);
  public
    function TestTitle: AnsiString; override;
    procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
  end;

implementation

{ TTest5 }

procedure TTest5.DoQuery(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    Results: IResults;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
  Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ? Returning LAST_NAME',3);
  Statement.GetSQLParams[0].AsDateTime := EncodeDate(2016,1,31);;
  Statement.GetSQLParams[1].AsInteger := 8;
  Results := Statement.Execute;
  WriteAffectedRows(Statement);
  writeln(OutFile,'Last Name = ',Results[0].AsString);

  Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
  ReportResults(Statement);

  Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
      'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
      'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
      ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY) Returning FULL_NAME',3);
  Transaction.RollbackRetaining;
  with Statement.GetSQLParams do
  begin
    ByName('EMP_NO').AsInteger := 150;
    ByName('FIRST_NAME').AsString := 'John';
    ByName('LAST_NAME').AsString := 'Doe';
    ByName('PHONE_EXT').AsString := '666';
    ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
    ByName('DEPT_NO').AsString := '600';
    ByName('JOB_CODE').AsString := 'Eng';
    ByName('JOB_GRADE').AsInteger := 4;
    ByName('JOB_COUNTRY').AsString := 'England';
    ByName('SALARY').AsFloat := 41000.89;
  end;
  writeln(OutFile,'Inserting');
  Results := Statement.Execute;
  writeln(OutFile,'Full Name = ',Results[0].AsString);
  WriteAffectedRows(Statement);
  CheckActivity(Statement.GetAttachment);
  CheckActivity(Transaction);
  CheckActivity(Statement.GetAttachment);
  CheckActivity(Transaction);

  writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
         'Select count(*) from EMPLOYEE',3)[0].AsInteger);
  CheckActivity(Statement.GetAttachment);
  CheckActivity(Transaction);
  if Transaction.InTransaction then
    writeln(OutFile,'Transaction Active')
  else
    writeln(OutFile,'Transaction inactive');
  Transaction.Rollback;
  if Transaction.InTransaction then
    writeln(OutFile,'Transaction Active')
  else
    writeln(OutFile,'Transaction inactive');
end;

function TTest5.TestTitle: AnsiString;
begin
  Result := 'Test 5: Update Returning and Activity Check';
end;

procedure TTest5.RunTest(CharSet: AnsiString; SQLDialect: integer);
var Attachment: IAttachment;
    DPB: IDPB;
begin
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
  DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
  DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);

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

initialization
  RegisterTest(TTest5);


end.

Revision:	379
Committed:	Mon Jan 10 10:08:03 2022 UTC (2 years, 3 months ago) by tony
Content type:	text/x-pascal
File size:	5372 byte(s)
Log Message:	set line ending property
#	User	Rev	Content
1	tony	315	(*
2			* Firebird Interface (fbintf) Test suite. This program is used to
3			* test the Firebird Pascal Interface and provide a semi-automated
4			* pass/fail check for each test.
5			*
6			* The contents of this file are subject to the Initial Developer's
7			* Public License Version 1.0 (the "License"); you may not use this
8			* file except in compliance with the License. You may obtain a copy
9			* of the License here:
10			*
11			* http://www.firebirdsql.org/index.php?op=doc&id=idpl
12			*
13			* Software distributed under the License is distributed on an "AS
14			* IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
15			* implied. See the License for the specific language governing rights
16			* and limitations under the License.
17			*
18			* The Initial Developer of the Original Code is Tony Whyman.
19			*
20			* The Original Code is (C) 2016 Tony Whyman, MWA Software
21			* (http://www.mwasoftware.co.uk).
22			*
23			* All Rights Reserved.
24			*
25			* Contributor(s): ______________________________________.
26			*
27			*)
28
29	tony	45	unit Test5;
30	tony	56	{$IFDEF MSWINDOWS}
31			{$DEFINE WINDOWS}
32			{$ENDIF}
33	tony	45
34	tony	56	{$IFDEF FPC}
35			{$mode delphi}
36	tony	45	{$codepage utf8}
37	tony	56	{$ENDIF}
38	tony	45
39			{Test 5: Update/Insert Returning and Activity Check}
40
41			{ This test opens the employee example databases with the supplied user name/password,
42			reconnects, and runs several queries:
43
44			1. Update an employee record, return LAST_NAME,and report affected rows.
45
46			2. Show Changed Record
47
48			3. Insert new employee record, return FULL_NAME and report affected rows.
49
50			4. Show inserted record
51
52			5. Check attachment and transaction activity and ensure reset.
53
54			6. Show total records and confirm attachment and transaction activity
55
56			7. Implicit Rollback and disconnect.
57
58			}
59
60			interface
61
62			uses
63	tony	315	Classes, SysUtils, TestApplication, FBTestApp, IB;
64	tony	45
65			type
66
67			{ TTest5 }
68
69	tony	315	TTest5 = class(TFBTestBase)
70	tony	45	private
71			procedure DoQuery(Attachment: IAttachment);
72			public
73	tony	56	function TestTitle: AnsiString; override;
74			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
75	tony	45	end;
76
77			implementation
78
79			{ TTest5 }
80
81			procedure TTest5.DoQuery(Attachment: IAttachment);
82			var Transaction: ITransaction;
83			Statement: IStatement;
84			Results: IResults;
85			begin
86			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
87			Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ? Returning LAST_NAME',3);
88	tony	315	Statement.GetSQLParams[0].AsDateTime := EncodeDate(2016,1,31);;
89	tony	45	Statement.GetSQLParams[1].AsInteger := 8;
90			Results := Statement.Execute;
91			WriteAffectedRows(Statement);
92			writeln(OutFile,'Last Name = ',Results[0].AsString);
93
94			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
95			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
96			ReportResults(Statement);
97
98			Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
99			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
100			'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
101			':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY) Returning FULL_NAME',3);
102			Transaction.RollbackRetaining;
103			with Statement.GetSQLParams do
104			begin
105			ByName('EMP_NO').AsInteger := 150;
106			ByName('FIRST_NAME').AsString := 'John';
107			ByName('LAST_NAME').AsString := 'Doe';
108			ByName('PHONE_EXT').AsString := '666';
109			ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
110			ByName('DEPT_NO').AsString := '600';
111			ByName('JOB_CODE').AsString := 'Eng';
112			ByName('JOB_GRADE').AsInteger := 4;
113			ByName('JOB_COUNTRY').AsString := 'England';
114			ByName('SALARY').AsFloat := 41000.89;
115			end;
116			writeln(OutFile,'Inserting');
117			Results := Statement.Execute;
118			writeln(OutFile,'Full Name = ',Results[0].AsString);
119			WriteAffectedRows(Statement);
120			CheckActivity(Statement.GetAttachment);
121			CheckActivity(Transaction);
122			CheckActivity(Statement.GetAttachment);
123			CheckActivity(Transaction);
124
125			writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
126			'Select count(*) from EMPLOYEE',3)[0].AsInteger);
127			CheckActivity(Statement.GetAttachment);
128			CheckActivity(Transaction);
129			if Transaction.InTransaction then
130			writeln(OutFile,'Transaction Active')
131			else
132			writeln(OutFile,'Transaction inactive');
133			Transaction.Rollback;
134			if Transaction.InTransaction then
135			writeln(OutFile,'Transaction Active')
136			else
137			writeln(OutFile,'Transaction inactive');
138			end;
139
140	tony	56	function TTest5.TestTitle: AnsiString;
141	tony	45	begin
142			Result := 'Test 5: Update Returning and Activity Check';
143			end;
144
145	tony	56	procedure TTest5.RunTest(CharSet: AnsiString; SQLDialect: integer);
146	tony	45	var Attachment: IAttachment;
147			DPB: IDPB;
148			begin
149			DPB := FirebirdAPI.AllocateDPB;
150			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
151			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
152			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
153			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
154
155			writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
156			Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
157			writeln(OutFile,'Database Open');
158			Attachment.Disconnect;
159			writeln(OutFile,'Database Closed');
160			Attachment.Connect;
161			writeln(OutFile,'Database Open');
162			DoQuery(Attachment);
163			end;
164
165			initialization
166			RegisterTest(TTest5);
167
168
169			end.
170