fbintf/testsuite/Test5.pas

unit Test5;

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

{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, TestManager, IB;

type

  { TTest5 }

  TTest5 = class(TTestBase)
  private
    procedure DoQuery(Attachment: IAttachment);
  public
    function TestTitle: string; override;
    procedure RunTest(CharSet: string; 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: string;
begin
  Result := 'Test 5: Update Returning and Activity Check';
end;

procedure TTest5.RunTest(CharSet: string; 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:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (8 years ago) by tony
Content type:	text/x-pascal
File size:	4295 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	User	Rev	Content
1	tony	45	unit Test5;
2
3			{$mode objfpc}{$H+}
4			{$codepage utf8}
5
6			{Test 5: Update/Insert Returning and Activity Check}
7
8			{ This test opens the employee example databases with the supplied user name/password,
9			reconnects, and runs several queries:
10
11			1. Update an employee record, return LAST_NAME,and report affected rows.
12
13			2. Show Changed Record
14
15			3. Insert new employee record, return FULL_NAME and report affected rows.
16
17			4. Show inserted record
18
19			5. Check attachment and transaction activity and ensure reset.
20
21			6. Show total records and confirm attachment and transaction activity
22
23			7. Implicit Rollback and disconnect.
24
25			}
26
27			interface
28
29			uses
30			Classes, SysUtils, TestManager, IB;
31
32			type
33
34			{ TTest5 }
35
36			TTest5 = class(TTestBase)
37			private
38			procedure DoQuery(Attachment: IAttachment);
39			public
40			function TestTitle: string; override;
41			procedure RunTest(CharSet: string; SQLDialect: integer); override;
42			end;
43
44			implementation
45
46			{ TTest5 }
47
48			procedure TTest5.DoQuery(Attachment: IAttachment);
49			var Transaction: ITransaction;
50			Statement: IStatement;
51			Results: IResults;
52			begin
53			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
54			Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ? Returning LAST_NAME',3);
55			Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
56			Statement.GetSQLParams[1].AsInteger := 8;
57			Results := Statement.Execute;
58			WriteAffectedRows(Statement);
59			writeln(OutFile,'Last Name = ',Results[0].AsString);
60
61			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
62			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
63			ReportResults(Statement);
64
65			Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
66			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
67			'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
68			':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY) Returning FULL_NAME',3);
69			Transaction.RollbackRetaining;
70			with Statement.GetSQLParams do
71			begin
72			ByName('EMP_NO').AsInteger := 150;
73			ByName('FIRST_NAME').AsString := 'John';
74			ByName('LAST_NAME').AsString := 'Doe';
75			ByName('PHONE_EXT').AsString := '666';
76			ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
77			ByName('DEPT_NO').AsString := '600';
78			ByName('JOB_CODE').AsString := 'Eng';
79			ByName('JOB_GRADE').AsInteger := 4;
80			ByName('JOB_COUNTRY').AsString := 'England';
81			ByName('SALARY').AsFloat := 41000.89;
82			end;
83			writeln(OutFile,'Inserting');
84			Results := Statement.Execute;
85			writeln(OutFile,'Full Name = ',Results[0].AsString);
86			WriteAffectedRows(Statement);
87			CheckActivity(Statement.GetAttachment);
88			CheckActivity(Transaction);
89			CheckActivity(Statement.GetAttachment);
90			CheckActivity(Transaction);
91
92			writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
93			'Select count(*) from EMPLOYEE',3)[0].AsInteger);
94			CheckActivity(Statement.GetAttachment);
95			CheckActivity(Transaction);
96			if Transaction.InTransaction then
97			writeln(OutFile,'Transaction Active')
98			else
99			writeln(OutFile,'Transaction inactive');
100			Transaction.Rollback;
101			if Transaction.InTransaction then
102			writeln(OutFile,'Transaction Active')
103			else
104			writeln(OutFile,'Transaction inactive');
105			end;
106
107			function TTest5.TestTitle: string;
108			begin
109			Result := 'Test 5: Update Returning and Activity Check';
110			end;
111
112			procedure TTest5.RunTest(CharSet: string; SQLDialect: integer);
113			var Attachment: IAttachment;
114			DPB: IDPB;
115			begin
116			DPB := FirebirdAPI.AllocateDPB;
117			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
118			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
119			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
120			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
121
122			writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
123			Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
124			writeln(OutFile,'Database Open');
125			Attachment.Disconnect;
126			writeln(OutFile,'Database Closed');
127			Attachment.Connect;
128			writeln(OutFile,'Database Open');
129			DoQuery(Attachment);
130			end;
131
132			initialization
133			RegisterTest(TTest5);
134
135
136			end.
137