fbintf/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:	315
Committed:	Thu Feb 25 11:56:36 2021 UTC (3 years, 9 months ago) by tony
Content type:	text/x-pascal
File size:	5372 byte(s)
Log Message:	Updated for IBX 4 release
#	Content
1	(*
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	unit Test5;
30	{$IFDEF MSWINDOWS}
31	{$DEFINE WINDOWS}
32	{$ENDIF}
33
34	{$IFDEF FPC}
35	{$mode delphi}
36	{$codepage utf8}
37	{$ENDIF}
38
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	Classes, SysUtils, TestApplication, FBTestApp, IB;
64
65	type
66
67	{ TTest5 }
68
69	TTest5 = class(TFBTestBase)
70	private
71	procedure DoQuery(Attachment: IAttachment);
72	public
73	function TestTitle: AnsiString; override;
74	procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
75	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	Statement.GetSQLParams[0].AsDateTime := EncodeDate(2016,1,31);;
89	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	function TTest5.TestTitle: AnsiString;
141	begin
142	Result := 'Test 5: Update Returning and Activity Check';
143	end;
144
145	procedure TTest5.RunTest(CharSet: AnsiString; SQLDialect: integer);
146	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