fbintf/testsuite/Test4.pas

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

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

{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: AnsiString; override;
    procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
  end;


implementation

{ TTest4 }

procedure TTest4.DoQuery(Attachment: IAttachment);
var Transaction, Transaction2, Transaction3: ITransaction;
    Statement, Statement2: 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 = :F1',3);
  Statement.EnableStatistics(true);
  Statement.GetSQLParams.ByName('F1').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').AsString :=  '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').AsString := '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);

  Statement2 := Attachment.PrepareWithNamedParameters(Transaction,'Update EMPLOYEE Set FIRST_NAME = ''Jayne''''s'' Where EMP_NO = :EMP_NO',3);
  Statement2.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
  writeln(OutFile,'Updating');
  Statement2.Execute;
  WriteAffectedRows(Statement);

  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);

  writeln(outfile,'Test using Execute Block');

  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
  Statement := Attachment.PrepareWithNamedParameters(Transaction,
    'Execute Block (Hired Timestamp = :Hire_Date, empno integer = :EMP_NO) '+
    'As Begin ' +
    '  Update Employee Set Hire_Date = :Hired Where EMP_NO = :empno; '+
    'End'
    ,3);
  Statement.GetSQLParams.ByName('Hire_Date').AsDateTime := EncodeDate(2015,1,31);;
  Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
  Statement.Execute;
  WriteAffectedRows(Statement);
  Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
  Statement.GetSQLParams.ByName('emp_no').AsInteger := 8;
  ReportResults(Statement);

end;

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

procedure TTest4.RunTest(CharSet: AnsiString; SQLDialect: integer);
var Attachment: IAttachment;
    DPB: IDPB;
    S: TStrings;
    i: integer;
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);
  DPB.Add(isc_dpb_config).SetAsString('WireCompression=true');

  writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
  Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
  writeln(OutFile,'Database Open');
  S := TStringList.Create;
  try
    Attachment.getFBVersion(S);
    for i := 0 to S.Count -1 do
      writeln(OutFile,S[i]);
  finally
    S.Free;
  end;
  DoQuery(Attachment);
end;

initialization
  RegisterTest(TTest4);

end.

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