fbintf/testsuite/Test2.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 Test2;
{$IFDEF MSWINDOWS} 
{$DEFINE WINDOWS} 
{$ENDIF}

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

{Test 2: Open the employee database and run a query}

{ This test opens the employee example databases with the supplied user name/password
  and runs a simple query with no parameters. Note that the password parameter
  is first supplied empty and then updated.

  Both the output metadata and the query plan are printed out, followed by the results of the query.

  A specific employee record is then queried, first using a positional parameter
  and then a parameter by name. In each case, the SQL Parameter metadata is also
  printed followed by the query results.

  Finally, the database is explicitly disconnected.
}

interface

uses
  Classes, SysUtils, TestApplication, FBTestApp, IB;

type

{ TTest2 }

TTest2 = class(TFBTestBase)
private
  procedure DoQuery(Attachment: IAttachment);
public
  function TestTitle: AnsiString; override;
  procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
end;


implementation

{ TTest2 }

procedure TTest2.DoQuery(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
begin
    Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
    PrintTPB(Transaction.getTPB);
    Statement := Attachment.Prepare(Transaction,
    '-- SQL style inline comment' + LineEnding +
    '/* this is a comment */ '+
    'Select First 3 * from EMPLOYEE'
    ,3);
    PrintMetaData(Statement.GetMetaData);
    writeln(OutFile,'Plan = ' ,Statement.GetPlan);
    writeln(OutFile,Statement.GetSQLText);
    writeln(OutFile);
    ReportResults(Statement);
    Statement := Attachment.Prepare(Transaction,'Select * from EMPLOYEE Where EMP_NO = ?',3);
    writeln(OutFile,Statement.GetSQLText);
    ParamInfo(Statement.SQLParams);
    Statement.GetSQLParams[0].AsInteger := 8;
    ReportResults(Statement);
    writeln(OutFile,'With param names');
    Statement := Attachment.PrepareWithNamedParameters(Transaction,
    'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
    Statement.SetRetainInterfaces(true);
    try
      writeln(OutFile,Statement.GetSQLText);
      ParamInfo(Statement.SQLParams);
      Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
      ReportResults(Statement);
    finally
      Statement.SetRetainInterfaces(false);
    end;
end;

function TTest2.TestTitle: AnsiString;
begin
  Result := 'Test 2: Open the employee database and run a query';
end;

procedure TTest2.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(' ');
  DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
  DPB.Find(isc_dpb_password).setAsString(Owner.GetPassword);
  try
    Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
  except on e: Exception do
    writeln(OutFile,'Open Database fails ',E.Message);
  end;
  writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
  Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
  writeln(OutFile,'Database Open, SQL Dialect = ',Attachment.GetSQLDialect);
  DoQuery(Attachment);
  Attachment.Disconnect;
  writeln(OutFile,'Now open the employee database as a local database');
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
  DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
  if FirebirdAPI.GetClientMajor < 3 then
    DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
  try
    Attachment := FirebirdAPI.OpenDatabase(ExtractDBName(Owner.GetEmployeeDatabaseName),DPB);
  except on e: Exception do
    writeln(OutFile,'Open Local Database fails ',E.Message);
  end;
  DoQuery(Attachment);
  Attachment.Disconnect;
end;

initialization
  RegisterTest(TTest2);

end.

Revision:	333
Committed:	Fri Feb 26 16:34:26 2021 UTC (3 years, 9 months ago) by tony
Content type:	text/x-pascal
File size:	5045 byte(s)
Log Message:	Add missing files
#	User	Rev	Content
1	tony	333	(*
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 Test2;
30			{$IFDEF MSWINDOWS}
31			{$DEFINE WINDOWS}
32			{$ENDIF}
33
34			{$IFDEF FPC}
35			{$mode delphi}
36			{$codepage utf8}
37			{$ENDIF}
38
39			{Test 2: Open the employee database and run a query}
40
41			{ This test opens the employee example databases with the supplied user name/password
42			and runs a simple query with no parameters. Note that the password parameter
43			is first supplied empty and then updated.
44
45			Both the output metadata and the query plan are printed out, followed by the results of the query.
46
47			A specific employee record is then queried, first using a positional parameter
48			and then a parameter by name. In each case, the SQL Parameter metadata is also
49			printed followed by the query results.
50
51			Finally, the database is explicitly disconnected.
52			}
53
54			interface
55
56			uses
57			Classes, SysUtils, TestApplication, FBTestApp, IB;
58
59			type
60
61			{ TTest2 }
62
63			TTest2 = class(TFBTestBase)
64			private
65			procedure DoQuery(Attachment: IAttachment);
66			public
67			function TestTitle: AnsiString; override;
68			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
69			end;
70
71
72			implementation
73
74			{ TTest2 }
75
76			procedure TTest2.DoQuery(Attachment: IAttachment);
77			var Transaction: ITransaction;
78			Statement: IStatement;
79			begin
80			Transaction := Attachment.StartTransaction([isc_tpb_read,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
81			PrintTPB(Transaction.getTPB);
82			Statement := Attachment.Prepare(Transaction,
83			'-- SQL style inline comment' + LineEnding +
84			'/* this is a comment */ '+
85			'Select First 3 * from EMPLOYEE'
86			,3);
87			PrintMetaData(Statement.GetMetaData);
88			writeln(OutFile,'Plan = ' ,Statement.GetPlan);
89			writeln(OutFile,Statement.GetSQLText);
90			writeln(OutFile);
91			ReportResults(Statement);
92			Statement := Attachment.Prepare(Transaction,'Select * from EMPLOYEE Where EMP_NO = ?',3);
93			writeln(OutFile,Statement.GetSQLText);
94			ParamInfo(Statement.SQLParams);
95			Statement.GetSQLParams[0].AsInteger := 8;
96			ReportResults(Statement);
97			writeln(OutFile,'With param names');
98			Statement := Attachment.PrepareWithNamedParameters(Transaction,
99			'Select * from EMPLOYEE Where EMP_NO = :EMP_NO',3);
100			Statement.SetRetainInterfaces(true);
101			try
102			writeln(OutFile,Statement.GetSQLText);
103			ParamInfo(Statement.SQLParams);
104			Statement.GetSQLParams.ByName('EMP_NO').AsInteger := 8;
105			ReportResults(Statement);
106			finally
107			Statement.SetRetainInterfaces(false);
108			end;
109			end;
110
111			function TTest2.TestTitle: AnsiString;
112			begin
113			Result := 'Test 2: Open the employee database and run a query';
114			end;
115
116			procedure TTest2.RunTest(CharSet: AnsiString; SQLDialect: integer);
117			var Attachment: IAttachment;
118			DPB: IDPB;
119			begin
120			DPB := FirebirdAPI.AllocateDPB;
121			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
122			DPB.Add(isc_dpb_password).setAsString(' ');
123			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
124			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
125			DPB.Find(isc_dpb_password).setAsString(Owner.GetPassword);
126			try
127			Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
128			except on e: Exception do
129			writeln(OutFile,'Open Database fails ',E.Message);
130			end;
131			writeln(OutFile,'Opening ',Owner.GetEmployeeDatabaseName);
132			Attachment := FirebirdAPI.OpenDatabase(Owner.GetEmployeeDatabaseName,DPB);
133			writeln(OutFile,'Database Open, SQL Dialect = ',Attachment.GetSQLDialect);
134			DoQuery(Attachment);
135			Attachment.Disconnect;
136			writeln(OutFile,'Now open the employee database as a local database');
137			DPB := FirebirdAPI.AllocateDPB;
138			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
139			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
140			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
141			if FirebirdAPI.GetClientMajor < 3 then
142			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
143			try
144			Attachment := FirebirdAPI.OpenDatabase(ExtractDBName(Owner.GetEmployeeDatabaseName),DPB);
145			except on e: Exception do
146			writeln(OutFile,'Open Local Database fails ',E.Message);
147			end;
148			DoQuery(Attachment);
149			Attachment.Disconnect;
150			end;
151
152			initialization
153			RegisterTest(TTest2);
154
155			end.
156