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;
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.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)',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:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (8 years ago) by tony
Content type:	text/x-pascal
File size:	7563 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	Content
1	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	begin
61	Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
62	Statement := Attachment.Prepare(Transaction,'Update Employee Set Hire_Date = ? Where EMP_NO = ?',3);
63	Statement.GetSQLParams[0].AsDAteTime := EncodeDate(2016,1,31);;
64	Statement.GetSQLParams[1].AsInteger := 8;
65	Statement.Execute;
66	WriteAffectedRows(Statement);
67	Transaction.Rollback;
68	Transaction.Start(TARollback);
69
70	Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
71	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
72	ReportResults(Statement);
73
74	Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
75	'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
76	'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
77	':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
78	with Statement.GetSQLParams do
79	begin
80	ByName('EMP_NO').AsInteger := 150;
81	ByName('FIRST_NAME').AsString := 'John';
82	ByName('LAST_NAME').AsString := 'Doe';
83	ByName('PHONE_EXT').AsString := '';
84	ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
85	ByName('DEPT_NO').AsString := '600';
86	ByName('JOB_CODE').AsString := 'Eng';
87	ByName('JOB_GRADE').AsInteger := 4;
88	ByName('JOB_COUNTRY').AsString := 'England';
89	ByName('SALARY').AsFloat := 41000.89;
90	end;
91	Statement.Execute;
92	WriteAffectedRows(Statement);
93
94	Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
95	writeln(OutFile,'Relation Name = ',Statement.Metadata.GetUniqueRelationName);
96	PrintMetaData(Statement.GetMetaData);
97	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
98	ReportResults(Statement);
99
100	{Now repeat but with a non-unique parameter name}
101	Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
102	'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
103	'VALUES (:EMP_NO, :FIRST_NAME, :FIRST_NAME, :PHONE_EXT, :HIRE_DATE,' +
104	':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
105	with Statement.GetSQLParams do
106	begin
107	ByName('EMP_NO').AsInteger := 151;
108	ByName('FIRST_NAME').AsString := 'Major';
109	ByName('PHONE_EXT').AsString := '';
110	ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
111	ByName('DEPT_NO').AsString := '600';
112	ByName('JOB_CODE').AsString := 'Eng';
113	ByName('JOB_GRADE').AsInteger := 4;
114	ByName('JOB_COUNTRY').AsString := 'England';
115	ByName('SALARY').AsFloat := 40000.59;
116	end;
117	Statement.Execute;
118	WriteAffectedRows(Statement);
119
120	Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
121	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 151;
122	ReportResults(Statement);
123
124	writeln(OutFile,'Now Delete the rows');
125	Statement := Attachment.Prepare(Transaction,'Delete From Employee Where EMP_NO = ?',3);
126	Statement.GetSQLParams[0].AsInteger := 150;
127	Statement.Execute;
128	WriteAffectedRows(Statement);
129	Statement.GetSQLParams[0].AsInteger := 151;
130	Statement.Execute;
131	WriteAffectedRows(Statement);
132
133	{Now again but with a null}
134	Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
135	'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
136	'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
137	':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)',3);
138	with Statement.GetSQLParams do
139	begin
140	ByName('EMP_NO').AsInteger := 150;
141	ByName('FIRST_NAME').AsString := 'Jane';
142	ByName('LAST_NAME').AsString := 'Doe';
143	ByName('PHONE_EXT').Clear;
144	ByName('HIRE_DATE').AsDateTime := EncodeDate(2015,4,1);;
145	ByName('DEPT_NO').AsString := '600';
146	ByName('JOB_CODE').AsString := 'Eng';
147	ByName('JOB_GRADE').AsInteger := 4;
148	ByName('JOB_COUNTRY').AsString := 'England';
149	ByName('SALARY').AsFloat := 41000.89;
150	end;
151	writeln(OutFile,'Inserting');
152	Statement.Execute;
153	WriteAffectedRows(Statement);
154
155	Statement := Attachment.PrepareWithNamedParameters(Transaction,'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
156	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
157	ReportResults(Statement);
158
159	writeln(OutFile,'Employee Count = ', Attachment.OpenCursorAtStart(Transaction,
160	'Select count(*) from EMPLOYEE',3)[0].AsInteger);
161
162	writeln(OutFile,'Prepare Query again');
163	writeln(OutFile);
164	Statement.Prepare;
165	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 150;
166	ReportResults(Statement);
167
168	Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
169	writeln(OutFile,'Prepare Query again with a different transaction');
170	writeln(OutFile);
171	Statement.Prepare(Transaction2);
172	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
173	ReportResults(Statement);
174
175	Transaction3 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
176	writeln(OutFile,'Open Cursor with a different transaction');
177	writeln(OutFile);
178	Rows := Statement.OpenCursor(Transaction3);
179	try
180	while Rows.FetchNext do
181	ReportResult(Rows);
182	finally
183	Rows.Close;
184	end;
185	writeln(OutFile,'Same Statement - updated params');
186	Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 9;
187	ReportResults(Statement);
188	end;
189
190	function TTest4.TestTitle: string;
191	begin
192	Result := 'Test 4: Update, Insert and Delete Queries';
193	end;
194
195	procedure TTest4.RunTest(CharSet: string; SQLDialect: integer);
196	var Attachment: IAttachment;
197	DPB: IDPB;
198	begin
199	DPB := FirebirdAPI.AllocateDPB;
200	DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
201	DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
202	DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
203	DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
204
205	writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
206	Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
207	writeln(OutFile,'Database Open');
208	DoQuery(Attachment);
209	end;
210
211	initialization
212	RegisterTest(TTest4);
213
214	end.
215