fbintf/testsuite/Test6.pas

unit Test6;

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

{Test 6: Blob Handling}

{
  1. Create an empty database and populate with a single table.

  2. Show the character sets available (List RDB$CHARACTER_SETS)

  3. Select all from new table and show metadata.

  4. Insert row and include WIN1252 characters known to be in two byte UTF8, plus Fixed point

  5. Select all from new table

  6. Use Update Query to set blob field with plain text loaded from file

  7. Select all from new table

  8. Add another row with a null blob

  9. Update this row's blob field with a copy of the first row (demo of blob assignment)

  10. Select all from new table.

  11. Drop Database and repeat above but with no default connection character set.
}

interface

uses
  Classes, SysUtils, TestManager, IB;

type

  { TTest6 }

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

implementation

const
  sqlCreateTable =
    'Create Table TestData ('+
    'RowID Integer not null,'+
    'FixedPoint Decimal(8,2), '+
    'FloatingPoint Double Precision, '+
    'Title VarChar(32) Character Set UTF8,'+
    'BlobData Blob sub_type 1 Character Set UTF8,'+
    'Primary Key(RowID)'+
    ')';

  sqlGetCharSets = 'Select RDB$CHARACTER_SET_NAME,RDB$CHARACTER_SET_ID from RDB$CHARACTER_SETS order by 2';

  sqlInsert = 'Insert into TestData(RowID,Title,FixedPoint,FloatingPoint) Values(:RowID,:Title,:FP, :DP)';

  sqlUpdate = 'Update TestData Set BlobData = ? Where RowID = ?';


{ TTest6 }

procedure TTest6.UpdateDatabase(Attachment: IAttachment);
var Transaction: ITransaction;
    Statement,
    Statement2: IStatement;
    ResultSet: IResultSet;
    i: integer;
begin
  Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);

  Statement := Attachment.Prepare(Transaction,sqlGetCharSets);
  PrintMetaData(Statement.GetMetaData);
  ReportResults(Statement);
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  PrintMetaData(Statement.GetMetaData);
  Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
  ParamInfo(Statement.SQLParams);
  with Statement.GetSQLParams do
  begin
    ByName('rowid').AsInteger := 1;
    ByName('title').AsString := 'Blob Test ©€';
    ByName('Fp').AsDouble := 20.28;
    ByName('DP').AsDouble := 3.142;
  end;
  Statement.Execute;
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  ReportResults(Statement);


  Statement := Attachment.Prepare(Transaction,sqlUpdate);
  ParamInfo(Statement.SQLParams);
  Statement.SQLParams[0].AsBlob := Attachment.CreateBlob(Transaction,'TestData','BlobData').LoadFromFile('testtext.txt');
  Statement.SQLParams[1].AsInteger := 1;
  Statement.Execute;
  Statement := Attachment.Prepare(Transaction,'Select * from TestData');
  ReportResults(Statement);

  {second row}
  Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
  ParamInfo(Statement.SQLParams);
  with Statement.GetSQLParams do
  begin
    ByName('rowid').AsInteger := 2;
    ByName('title').AsString := 'Blob Test ©€';
  end;
  Statement.Execute;
  Statement := Attachment.Prepare(Transaction,'Select * from TestData Where rowid = 1');
  ResultSet := Statement.OpenCursor;
  if ResultSet.FetchNext then
  begin
    Statement2 := Attachment.Prepare(Transaction,sqlUpdate);
    Statement2.SQLParams[0].AsBlob := ResultSet.ByName('BlobData').AsBlob; {test duplication of blob}
    Statement2.SQLParams[1].AsInteger := 2;
    Statement2.Execute;
    Statement := Attachment.Prepare(Transaction,'Select * from TestData');
    ReportResults(Statement);
  end;
end;

function TTest6.TestTitle: string;
begin
  Result := 'Test 6: Blob Handling';
end;

procedure TTest6.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(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;

  {Repeat with no lc_ctype}
  DPB := FirebirdAPI.AllocateDPB;
  DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
  DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
  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(TTest6);
end.

Revision:	45
Committed:	Tue Dec 6 10:33:46 2016 UTC (7 years, 4 months ago) by tony
Content type:	text/x-pascal
File size:	5004 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	User	Rev	Content
1	tony	45	unit Test6;
2
3			{$mode objfpc}{$H+}
4			{$codepage UTF8}
5
6			{Test 6: Blob Handling}
7
8			{
9			1. Create an empty database and populate with a single table.
10
11			2. Show the character sets available (List RDB$CHARACTER_SETS)
12
13			3. Select all from new table and show metadata.
14
15			4. Insert row and include WIN1252 characters known to be in two byte UTF8, plus Fixed point
16
17			5. Select all from new table
18
19			6. Use Update Query to set blob field with plain text loaded from file
20
21			7. Select all from new table
22
23			8. Add another row with a null blob
24
25			9. Update this row's blob field with a copy of the first row (demo of blob assignment)
26
27			10. Select all from new table.
28
29			11. Drop Database and repeat above but with no default connection character set.
30			}
31
32			interface
33
34			uses
35			Classes, SysUtils, TestManager, IB;
36
37			type
38
39			{ TTest6 }
40
41			TTest6 = class(TTestBase)
42			private
43			procedure UpdateDatabase(Attachment: IAttachment);
44			public
45			function TestTitle: string; override;
46			procedure RunTest(CharSet: string; SQLDialect: integer); override;
47			end;
48
49			implementation
50
51			const
52			sqlCreateTable =
53			'Create Table TestData ('+
54			'RowID Integer not null,'+
55			'FixedPoint Decimal(8,2), '+
56			'FloatingPoint Double Precision, '+
57			'Title VarChar(32) Character Set UTF8,'+
58			'BlobData Blob sub_type 1 Character Set UTF8,'+
59			'Primary Key(RowID)'+
60			')';
61
62			sqlGetCharSets = 'Select RDB$CHARACTER_SET_NAME,RDB$CHARACTER_SET_ID from RDB$CHARACTER_SETS order by 2';
63
64			sqlInsert = 'Insert into TestData(RowID,Title,FixedPoint,FloatingPoint) Values(:RowID,:Title,:FP, :DP)';
65
66			sqlUpdate = 'Update TestData Set BlobData = ? Where RowID = ?';
67
68
69			{ TTest6 }
70
71			procedure TTest6.UpdateDatabase(Attachment: IAttachment);
72			var Transaction: ITransaction;
73			Statement,
74			Statement2: IStatement;
75			ResultSet: IResultSet;
76			i: integer;
77			begin
78			Transaction := Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taCommit);
79
80			Statement := Attachment.Prepare(Transaction,sqlGetCharSets);
81			PrintMetaData(Statement.GetMetaData);
82			ReportResults(Statement);
83			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
84			PrintMetaData(Statement.GetMetaData);
85			Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
86			ParamInfo(Statement.SQLParams);
87			with Statement.GetSQLParams do
88			begin
89			ByName('rowid').AsInteger := 1;
90			ByName('title').AsString := 'Blob Test ©€';
91			ByName('Fp').AsDouble := 20.28;
92			ByName('DP').AsDouble := 3.142;
93			end;
94			Statement.Execute;
95			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
96			ReportResults(Statement);
97
98
99			Statement := Attachment.Prepare(Transaction,sqlUpdate);
100			ParamInfo(Statement.SQLParams);
101			Statement.SQLParams[0].AsBlob := Attachment.CreateBlob(Transaction,'TestData','BlobData').LoadFromFile('testtext.txt');
102			Statement.SQLParams[1].AsInteger := 1;
103			Statement.Execute;
104			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
105			ReportResults(Statement);
106
107			{second row}
108			Statement := Attachment.PrepareWithNamedParameters(Transaction,sqlInsert);
109			ParamInfo(Statement.SQLParams);
110			with Statement.GetSQLParams do
111			begin
112			ByName('rowid').AsInteger := 2;
113			ByName('title').AsString := 'Blob Test ©€';
114			end;
115			Statement.Execute;
116			Statement := Attachment.Prepare(Transaction,'Select * from TestData Where rowid = 1');
117			ResultSet := Statement.OpenCursor;
118			if ResultSet.FetchNext then
119			begin
120			Statement2 := Attachment.Prepare(Transaction,sqlUpdate);
121			Statement2.SQLParams[0].AsBlob := ResultSet.ByName('BlobData').AsBlob; {test duplication of blob}
122			Statement2.SQLParams[1].AsInteger := 2;
123			Statement2.Execute;
124			Statement := Attachment.Prepare(Transaction,'Select * from TestData');
125			ReportResults(Statement);
126			end;
127			end;
128
129			function TTest6.TestTitle: string;
130			begin
131			Result := 'Test 6: Blob Handling';
132			end;
133
134			procedure TTest6.RunTest(CharSet: string; SQLDialect: integer);
135			var DPB: IDPB;
136			Attachment: IAttachment;
137			begin
138			DPB := FirebirdAPI.AllocateDPB;
139			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
140			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
141			DPB.Add(isc_dpb_lc_ctype).setAsString(CharSet);
142			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
143			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
144			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
145			UpdateDatabase(Attachment);
146
147			Attachment.DropDatabase;
148
149			{Repeat with no lc_ctype}
150			DPB := FirebirdAPI.AllocateDPB;
151			DPB.Add(isc_dpb_user_name).setAsString(Owner.GetUserName);
152			DPB.Add(isc_dpb_password).setAsString(Owner.GetPassword);
153			DPB.Add(isc_dpb_set_db_SQL_dialect).setAsByte(SQLDialect);
154			Attachment := FirebirdAPI.CreateDatabase(Owner.GetNewDatabaseName,DPB);
155			Attachment.ExecImmediate([isc_tpb_write,isc_tpb_wait,isc_tpb_consistency],sqlCreateTable);
156			UpdateDatabase(Attachment);
157
158			Attachment.DropDatabase;
159			end;
160
161			initialization
162			RegisterTest(TTest6);
163			end.
164