fbintf/testsuite/Test14.pas

unit Test14;

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

{Test 14: Non select procedures}

{ this test creates a new database with a table and two stored procedures.

  1. The first stored procedure is run to populate the table

  2. The second returns data from the table, which is written out.
}

interface

uses
  Classes, SysUtils, TestManager, IB;

type
    { TTest14 }

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

implementation

{ TTest14 }

const
  SQLInsert = 'Execute Procedure InsertData';

procedure TTest14.UpdateDatabase(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);

  Statement := Attachment.Prepare(Transaction,sqlInsert);
  Statement.Execute;
end;

const
  sqlCallQueryProc = 'Execute Procedure ShowData';

procedure TTest14.QueryDatabase(Attachment: IAttachment);
var Transaction, Transaction2: ITransaction;
    Statement: IStatement;
    ResultSet: IResultSet;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  Statement := Attachment.Prepare(Transaction,sqlCallQueryProc);
  PrintMetaData(Statement.MetaData);
  ReportResult(Statement.Execute);
  writeln(OutFile);
  writeln(OutFile,'Repeat with a different execute transaction');
  writeln(OutFile);
  Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  ReportResult(Statement.Execute(Transaction2));
  writeln(OutFile);
  writeln(OutFile,'Repeat with a original transaction');
  writeln(OutFile);
  ReportResult(Statement.Execute);
end;

function TTest14.TestTitle: string;
begin
  Result := 'Test 14: Non select procedures';
end;

const
  sqlCreateTable =
    'Create Table TestData( '+
    'RowID Integer not null,'+
    'Title VarChar(32),'+
    'Primary Key(RowID)'+
    ')';

  sqlCreateProc1 =
    'Create Procedure InsertData As '+
    'Begin ' +
    'Insert into TestData(RowID,Title) VALUES (1,''Testing''); '+
    'End';

  sqlCreateProc2 =
    'Create Procedure ShowData Returns (RowID Integer, Title VarChar(32)) '+
    'As Begin '+
    'Select First 1 RowID,Title From TestData Into :RowID,:Title; '+
    'End';


procedure TTest14.RunTest(CharSet: string; SQLDialect: integer);
var DPB: IDPB;
    Attachment: IAttachment;
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('UTF8');
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
  Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
  writeln(OutFile,'Default Character set Name = ',Attachment.OpenCursorAtStart('Select RDB$CHARACTER_SET_NAME From RDB$Database')[0].AsString);
  Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
  Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateProc1);
  Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateProc2);

  UpdateDatabase(Attachment);
  QueryDatabase(Attachment);
  Attachment.DropDatabase;
end;

initialization
  RegisterTest(TTest14);
end.

Revision:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (8 years ago) by tony
Content type:	text/x-pascal
File size:	3518 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	User	Rev	Content
1	tony	45	unit Test14;
2
3			{$mode objfpc}{$H+}
4			{$codepage UTF8}
5
6			{Test 14: Non select procedures}
7
8			{ this test creates a new database with a table and two stored procedures.
9
10			1. The first stored procedure is run to populate the table
11
12			2. The second returns data from the table, which is written out.
13			}
14
15			interface
16
17			uses
18			Classes, SysUtils, TestManager, IB;
19
20			type
21			{ TTest14 }
22
23			TTest14 = class(TTestBase)
24			private
25			procedure UpdateDatabase(Attachment: IAttachment);
26			procedure QueryDatabase(Attachment: IAttachment);
27			public
28			function TestTitle: string; override;
29			procedure RunTest(CharSet: string; SQLDialect: integer); override;
30			end;
31
32			implementation
33
34			{ TTest14 }
35
36			const
37			SQLInsert = 'Execute Procedure InsertData';
38
39			procedure TTest14.UpdateDatabase(Attachment: IAttachment);
40			var Transaction: ITransaction;
41			Statement: IStatement;
42			begin
43			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
44
45			Statement := Attachment.Prepare(Transaction,sqlInsert);
46			Statement.Execute;
47			end;
48
49			const
50			sqlCallQueryProc = 'Execute Procedure ShowData';
51
52			procedure TTest14.QueryDatabase(Attachment: IAttachment);
53			var Transaction, Transaction2: ITransaction;
54			Statement: IStatement;
55			ResultSet: IResultSet;
56			begin
57			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
58			Statement := Attachment.Prepare(Transaction,sqlCallQueryProc);
59			PrintMetaData(Statement.MetaData);
60			ReportResult(Statement.Execute);
61			writeln(OutFile);
62			writeln(OutFile,'Repeat with a different execute transaction');
63			writeln(OutFile);
64			Transaction2 := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
65			ReportResult(Statement.Execute(Transaction2));
66			writeln(OutFile);
67			writeln(OutFile,'Repeat with a original transaction');
68			writeln(OutFile);
69			ReportResult(Statement.Execute);
70			end;
71
72			function TTest14.TestTitle: string;
73			begin
74			Result := 'Test 14: Non select procedures';
75			end;
76
77			const
78			sqlCreateTable =
79			'Create Table TestData( '+
80			'RowID Integer not null,'+
81			'Title VarChar(32),'+
82			'Primary Key(RowID)'+
83			')';
84
85			sqlCreateProc1 =
86			'Create Procedure InsertData As '+
87			'Begin ' +
88			'Insert into TestData(RowID,Title) VALUES (1,''Testing''); '+
89			'End';
90
91			sqlCreateProc2 =
92			'Create Procedure ShowData Returns (RowID Integer, Title VarChar(32)) '+
93			'As Begin '+
94			'Select First 1 RowID,Title From TestData Into :RowID,:Title; '+
95			'End';
96
97
98
99			procedure TTest14.RunTest(CharSet: string; SQLDialect: integer);
100			var DPB: IDPB;
101			Attachment: IAttachment;
102			begin
103			DPB := FirebirdAPI.AllocateDPB;
104			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
105			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
106			DPB.Add(isc_dpb_lc_ctype).setAsString('UTF8');
107			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
108			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
109			writeln(OutFile,'Default Character set Name = ',Attachment.OpenCursorAtStart('Select RDB$CHARACTER_SET_NAME From RDB$Database')[0].AsString);
110			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
111			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateProc1);
112			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateProc2);
113
114			UpdateDatabase(Attachment);
115			QueryDatabase(Attachment);
116			Attachment.DropDatabase;
117			end;
118
119			initialization
120			RegisterTest(TTest14);
121			end.
122