fbintf/testsuite/Test8.pas

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

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

{Test 8: Create and read back an Array with 2 dimensions}

{
1. Create an empty database and populate with a single table including a two
   dimensional array of varchar(16) column.

2. Select all and show metadata including array metadata.

3. Insert a row but leave array null

4. Show result.

5. Update row with a populated array and show results.
}

interface

uses
  Classes, SysUtils, TestManager, IB;

type

  { TTest8 }

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

implementation

const
  sqlCreateTable =
    'Create Table TestData ('+
    'RowID Integer not null,'+
    'Title VarChar(32) Character Set UTF8,'+
    'MyArray VarChar(16) [0:16, -1:7] Character Set ISO8859_2,'+
    'Primary Key(RowID)'+
    ')';

  sqlInsert = 'Insert into TestData(RowID,Title) Values(:RowID,:Title)';

  sqlUpdate = 'Update TestData Set MyArray = ? Where RowID = 1';

{ TTest8 }

procedure TTest8.UpdateDatabase(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement: IStatement;
    ResultSet: IResultSet;
    i,j,k : integer;
    ar: IArray;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  PrintMetaData(Statement.GetMetaData);
  Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
  ParamInfo(Statement.GetSQLParams);
  with Statement.GetSQLParams do
  begin
    ByName('rowid').AsInteger := 1;
    ByName('title').AsString := '2D Array';
  end;
  Statement.Execute;
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  ReportResults(Statement);

  Statement := Attachment.Prepare(Transaction,sqlUpdate);
  ar := Attachment.CreateArray(Transaction,'TestData','MyArray');
  if ar <> nil then
  begin
    k := 50;
    for i := 0 to 16 do
      for j := -1 to 7 do
      begin
        ar.SetAsString([i,j],'A' + IntToStr(k));
        Inc(k);
      end;
    Statement.SQLParams[0].AsArray := ar;
    Statement.Execute;
  end;
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  ReportResults(Statement);
end;

function TTest8.TestTitle: AnsiString;
begin
  Result := 'Test 8: Create and read back an Array with 2 dimensions';
end;

procedure TTest8.RunTest(CharSet: AnsiString; 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(CharSet);
  DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
  Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
  Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
  UpdateDatabase(Attachment);

  Attachment.DropDatabase;
end;

initialization
  RegisterTest(TTest8);

end.

Revision:	56
Committed:	Mon Mar 6 10:20:02 2017 UTC (7 years, 9 months ago) by tony
Content type:	text/x-pascal
File size:	3214 byte(s)
Log Message:	Committing updates for Trunk
#	User	Rev	Content
1	tony	45	unit Test8;
2	tony	56	{$IFDEF MSWINDOWS}
3			{$DEFINE WINDOWS}
4			{$ENDIF}
5	tony	45
6	tony	56	{$IFDEF FPC}
7			{$mode delphi}
8	tony	45	{$codepage utf8}
9	tony	56	{$ENDIF}
10	tony	45
11			{Test 8: Create and read back an Array with 2 dimensions}
12
13			{
14			1. Create an empty database and populate with a single table including a two
15			dimensional array of varchar(16) column.
16
17			2. Select all and show metadata including array metadata.
18
19			3. Insert a row but leave array null
20
21			4. Show result.
22
23			5. Update row with a populated array and show results.
24			}
25
26			interface
27
28			uses
29			Classes, SysUtils, TestManager, IB;
30
31			type
32
33			{ TTest8 }
34
35			TTest8 = class(TTestBase)
36			private
37			procedure UpdateDatabase(Attachment: IAttachment);
38			public
39	tony	56	function TestTitle: AnsiString; override;
40			procedure RunTest(CharSet: AnsiString; SQLDialect: integer); override;
41	tony	45	end;
42
43			implementation
44
45			const
46			sqlCreateTable =
47			'Create Table TestData ('+
48			'RowID Integer not null,'+
49			'Title VarChar(32) Character Set UTF8,'+
50			'MyArray VarChar(16) [0:16, -1:7] Character Set ISO8859_2,'+
51			'Primary Key(RowID)'+
52			')';
53
54			sqlInsert = 'Insert into TestData(RowID,Title) Values(:RowID,:Title)';
55
56			sqlUpdate = 'Update TestData Set MyArray = ? Where RowID = 1';
57
58			{ TTest8 }
59
60			procedure TTest8.UpdateDatabase(Attachment: IAttachment);
61			var Transaction: ITransaction;
62			Statement: IStatement;
63			ResultSet: IResultSet;
64			i,j,k : integer;
65			ar: IArray;
66			begin
67			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
68			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
69			PrintMetaData(Statement.GetMetaData);
70			Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
71			ParamInfo(Statement.GetSQLParams);
72			with Statement.GetSQLParams do
73			begin
74			ByName('rowid').AsInteger := 1;
75			ByName('title').AsString := '2D Array';
76			end;
77			Statement.Execute;
78			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
79			ReportResults(Statement);
80
81			Statement := Attachment.Prepare(Transaction,sqlUpdate);
82			ar := Attachment.CreateArray(Transaction,'TestData','MyArray');
83			if ar <> nil then
84			begin
85			k := 50;
86			for i := 0 to 16 do
87			for j := -1 to 7 do
88			begin
89			ar.SetAsString([i,j],'A' + IntToStr(k));
90			Inc(k);
91			end;
92			Statement.SQLParams[0].AsArray := ar;
93			Statement.Execute;
94			end;
95			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
96			ReportResults(Statement);
97			end;
98
99	tony	56	function TTest8.TestTitle: AnsiString;
100	tony	45	begin
101			Result := 'Test 8: Create and read back an Array with 2 dimensions';
102			end;
103
104	tony	56	procedure TTest8.RunTest(CharSet: AnsiString; SQLDialect: integer);
105	tony	45	var DPB: IDPB;
106			Attachment: IAttachment;
107			begin
108			DPB := FirebirdAPI.AllocateDPB;
109			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
110			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
111			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
112			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
113			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
114			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
115			UpdateDatabase(Attachment);
116
117			Attachment.DropDatabase;
118			end;
119
120			initialization
121			RegisterTest(TTest8);
122
123			end.
124