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 (8 years ago) by tony
Content type:	text/x-pascal
File size:	5004 byte(s)
Log Message:	Committing updates for Release R2-0-0
#	Content
1	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