fbintf/testsuite/Test5.pas

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

type

  { TTest5 }

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