fbintf/testsuite/Test4.pas

unit Test4;

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

{Test 4: Update, Insert and Delete Queries}

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

  1. Update an employee record and report affected rows.

  2. Show Changed Record

  3. Insert new employee record and report affected rows Repeat with a duplicated
     parameter name.

  4. Show inserted record and then delete it and report affected rows

  5. Repeat insert with a null PHONE_EXT.

  6. Show inserted record and total records

  7. Prepare query again and report results

  8. Prepare query with a different transaction and report results.

  9. Open Cursor with a different transaction and report results.

  10. Implicit Rollback and disconnect.

}

interface

uses
  Classes, SysUtils, TestManager, IB;

type

  { TTest4 }

  TTest4 = class(TTestBase)
  private
    procedure DoQuery(Attachment: IAttachment);
  public
    function TestTitle: string; override;
    procedure RunTest(CharSet: string; SQLDialect: integer); override;
  end;


implementation

{ TTest4 }

procedure TTest4.DoQuery(Attachment: IAttachment);
var Transaction, Transaction2, Transaction3: ITransaction;
    Statement: IStatement;
    Rows: IResultSet;
    stats: TPerfCounters;
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 = ?',3);
  Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
  Statement.GetSQLParams[1].AsInteger := 8;
  Statement.Execute;
  WriteAffectedRows(Statement);
  Transaction.Rollback;
  Transaction.Start(TARollback);

  Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
  Statement.EnableStatistics(true);
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
  ReportResults(Statement);
  if Statement.GetPerfStatistics(stats) then
    WritePerfStats(stats);

  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)',3);
  with Statement.GetSQLParams do
  begin
    ByName('EMP_NO').AsInteger := 150;
    ByName('FIRST_NAME').AsString := 'John';
    ByName('LAST_NAME').AsString := 'Doe';
    ByName('PHONE_EXT').AsString := '';
    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;
  Statement.Execute;
  WriteAffectedRows(Statement);

  Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
  writeln(OutFile,'Relation Name = ',Statement.Metadata.GetUniqueRelationName);
  PrintMetaData(Statement.GetMetaData);
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
  ReportResults(Statement);

  {Now repeat but with a non-unique parameter name}
  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, :FIRST_NAME, :PHONE_EXT, :HIRE_DATE,' +
      ':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
  with Statement.GetSQLParams do
  begin
    ByName('EMP_NO').AsInteger := 151;
    ByName('FIRST_NAME').AsString := 'Major';
    ByName('PHONE_EXT').AsString := '';
    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 := 40000.59;
  end;
  Statement.Execute;
  WriteAffectedRows(Statement);

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

  writeln(OutFile,'Now Delete the rows');
  Statement := Attachment.Prepare(Transaction,'Delete From Employee Where EMP_NO = ?',3);
  Statement.GetSQLParams[0].AsInteger := 150;
  Statement.Execute;
  WriteAffectedRows(Statement);
  Statement.GetSQLParams[0].AsInteger := 151;
  Statement.Execute;
  WriteAffectedRows(Statement);

  {Now again but with a null}
  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)',3);
  with Statement.GetSQLParams do
  begin
    ByName('EMP_NO').AsInteger := 150;
    ByName('FIRST_NAME').AsString := 'Jane';
    ByName('LAST_NAME').AsString := 'Doe';
    ByName('PHONE_EXT').Clear;
    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');
  Statement.Execute;
  WriteAffectedRows(Statement);

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

  writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
         'Select count(*) from EMPLOYEE',3)[0].AsInteger);

  writeln(OutFile,'Prepare Query again');
  writeln(OutFile);
  Statement.Prepare;
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
  ReportResults(Statement);

  Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
  writeln(OutFile,'Prepare Query again with a different transaction');
  writeln(OutFile);
  Statement.Prepare(Transaction2);
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
  ReportResults(Statement);

  Transaction3 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
  writeln(OutFile,'Open Cursor with a different transaction');
  writeln(OutFile);
  Rows := Statement.OpenCursor(Transaction3);
  try
    while Rows.FetchNext do
      ReportResult(Rows);
  finally
    Rows.Close;
  end;
  writeln(OutFile,'Same Statement - updated params');
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 9;
  ReportResults(Statement);
end;

function TTest4.TestTitle: string;
begin
  Result := 'Test 4: Update, Insert and Delete Queries';
end;

procedure TTest4.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');
  DoQuery(Attachment);
end;

initialization
  RegisterTest(TTest4);

end.

Revision:	47
Committed:	Mon Jan 9 15:31:51 2017 UTC (7 years, 11 months ago) by tony
Content type:	text/x-pascal
File size:	7696 byte(s)
Log Message:	Committing updates for Release R2-0-1
#	User	Rev	Content
1	tony	45	unit Test4;
2
3			{$mode objfpc}{$H+}
4			{$codepage utf8}
5
6			{Test 4: Update, Insert and Delete Queries}
7
8			{ This test opens the employee example databases with the supplied user name/password
9			and runs several queries:
10
11			1. Update an employee record and report affected rows.
12
13			2. Show Changed Record
14
15			3. Insert new employee record and report affected rows Repeat with a duplicated
16			parameter name.
17
18			4. Show inserted record and then delete it and report affected rows
19
20			5. Repeat insert with a null PHONE_EXT.
21
22			6. Show inserted record and total records
23
24			7. Prepare query again and report results
25
26			8. Prepare query with a different transaction and report results.
27
28			9. Open Cursor with a different transaction and report results.
29
30			10. Implicit Rollback and disconnect.
31
32			}
33
34			interface
35
36			uses
37			Classes, SysUtils, TestManager, IB;
38
39			type
40
41			{ TTest4 }
42
43			TTest4 = class(TTestBase)
44			private
45			procedure DoQuery(Attachment: IAttachment);
46			public
47			function TestTitle: string; override;
48			procedure RunTest(CharSet: string; SQLDialect: integer); override;
49			end;
50
51
52			implementation
53
54			{ TTest4 }
55
56			procedure TTest4.DoQuery(Attachment: IAttachment);
57			var Transaction, Transaction2, Transaction3: ITransaction;
58			Statement: IStatement;
59			Rows: IResultSet;
60	tony	47	stats: TPerfCounters;
61	tony	45	begin
62			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
63			Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ?',3);
64			Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
65			Statement.GetSQLParams[1].AsInteger := 8;
66			Statement.Execute;
67			WriteAffectedRows(Statement);
68			Transaction.Rollback;
69			Transaction.Start(TARollback);
70
71			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
72	tony	47	Statement.EnableStatistics(true);
73	tony	45	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
74			ReportResults(Statement);
75	tony	47	if Statement.GetPerfStatistics(stats) then
76			WritePerfStats(stats);
77	tony	45
78			Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
79			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
80			'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
81			':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
82			with Statement.GetSQLParams do
83			begin
84			ByName('EMP_NO').AsInteger := 150;
85			ByName('FIRST_NAME').AsString := 'John';
86			ByName('LAST_NAME').AsString := 'Doe';
87			ByName('PHONE_EXT').AsString := '';
88	tony	47	ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);
89	tony	45	ByName('DEPT_NO').AsString := '600';
90			ByName('JOB_CODE').AsString := 'Eng';
91			ByName('JOB_GRADE').AsInteger := 4;
92			ByName('JOB_COUNTRY').AsString := 'England';
93			ByName('SALARY').AsFloat := 41000.89;
94			end;
95			Statement.Execute;
96			WriteAffectedRows(Statement);
97
98			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
99			writeln(OutFile,'Relation Name = ',Statement.Metadata.GetUniqueRelationName);
100			PrintMetaData(Statement.GetMetaData);
101			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
102			ReportResults(Statement);
103
104			{Now repeat but with a non-unique parameter name}
105			Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
106			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
107			'VALUES (:EMP_NO, :FIRST_NAME, :FIRST_NAME, :PHONE_EXT, :HIRE_DATE,' +
108			':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
109			with Statement.GetSQLParams do
110			begin
111			ByName('EMP_NO').AsInteger := 151;
112			ByName('FIRST_NAME').AsString := 'Major';
113			ByName('PHONE_EXT').AsString := '';
114			ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
115			ByName('DEPT_NO').AsString := '600';
116			ByName('JOB_CODE').AsString := 'Eng';
117			ByName('JOB_GRADE').AsInteger := 4;
118			ByName('JOB_COUNTRY').AsString := 'England';
119			ByName('SALARY').AsFloat := 40000.59;
120			end;
121			Statement.Execute;
122			WriteAffectedRows(Statement);
123
124			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
125			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 151;
126			ReportResults(Statement);
127
128			writeln(OutFile,'Now Delete the rows');
129			Statement := Attachment.Prepare(Transaction,'Delete From Employee Where EMP_NO = ?',3);
130			Statement.GetSQLParams[0].AsInteger := 150;
131			Statement.Execute;
132			WriteAffectedRows(Statement);
133			Statement.GetSQLParams[0].AsInteger := 151;
134			Statement.Execute;
135			WriteAffectedRows(Statement);
136
137			{Now again but with a null}
138			Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
139			'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
140			'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
141			':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
142			with Statement.GetSQLParams do
143			begin
144			ByName('EMP_NO').AsInteger := 150;
145			ByName('FIRST_NAME').AsString := 'Jane';
146			ByName('LAST_NAME').AsString := 'Doe';
147			ByName('PHONE_EXT').Clear;
148			ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
149			ByName('DEPT_NO').AsString := '600';
150			ByName('JOB_CODE').AsString := 'Eng';
151			ByName('JOB_GRADE').AsInteger := 4;
152			ByName('JOB_COUNTRY').AsString := 'England';
153			ByName('SALARY').AsFloat := 41000.89;
154			end;
155			writeln(OutFile,'Inserting');
156			Statement.Execute;
157			WriteAffectedRows(Statement);
158
159			Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
160			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
161			ReportResults(Statement);
162
163			writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
164			'Select count(*) from EMPLOYEE',3)[0].AsInteger);
165
166			writeln(OutFile,'Prepare Query again');
167			writeln(OutFile);
168			Statement.Prepare;
169			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
170			ReportResults(Statement);
171
172			Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
173			writeln(OutFile,'Prepare Query again with a different transaction');
174			writeln(OutFile);
175			Statement.Prepare(Transaction2);
176			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
177			ReportResults(Statement);
178
179			Transaction3 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
180			writeln(OutFile,'Open Cursor with a different transaction');
181			writeln(OutFile);
182			Rows := Statement.OpenCursor(Transaction3);
183			try
184			while Rows.FetchNext do
185			ReportResult(Rows);
186			finally
187			Rows.Close;
188			end;
189			writeln(OutFile,'Same Statement - updated params');
190			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 9;
191			ReportResults(Statement);
192			end;
193
194			function TTest4.TestTitle: string;
195			begin
196			Result := 'Test 4: Update, Insert and Delete Queries';
197			end;
198
199			procedure TTest4.RunTest(CharSet: string; SQLDialect: integer);
200			var Attachment: IAttachment;
201			DPB: IDPB;
202			begin
203			DPB := FirebirdAPI.AllocateDPB;
204			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
205			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
206			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
207			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
208
209			writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
210			Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
211			writeln(OutFile,'Database Open');
212			DoQuery(Attachment);
213			end;
214
215			initialization
216			RegisterTest(TTest4);
217
218			end.
219