Title: BLOBs in InterBase
Question: Befor e we can manipulate BLOBs in a database some design should be made before
Answer:
You can use TDBMemo or TDBImage controls to display large text fields or text data contained in BLOB fields.
So a TBlobField is the direct ancestor of TMemoField and TGraphicField in a dataset that holds a reference to a BLOB.
But how do we use a stream returned by a dataset's CreateBlobStream method to read or write the data managed by a BLOB field:
The term in // is an alternative to save a BLOB with a dataset and could be some information depending your configuration with client datasets.
First an example from a table ibTablemax in a DataModule called cachedata:
//blobdataset:= cachedata.ibclientds1 as TIBClientDataSet;
//SetControlStates(true);
//cachedata.IBClientDS1.FileName:= '';
//cachedata.ibdatabase1.sqldialect:= 2;
//cachedata.ibclientds1.close;
//cachedata.IBClientDS1.CommandText:= 'select * from MBLOB
where ID = 17';
//cachedata.ibclientds1.Open;
//blobdataset:= cachedata.IBclientds1;
//cachedata.letDocuSaveasBlop(blobdataset);
//try it with tibtable
procedure TCacheData.letDocuSaveasBlob2;
var blobdataset: TIBClientDataset;
blob: TStream;
fs: TFileStream;
begin
cachedata.IBDatabase1.SQLDialect:= 1;
with cachedata.IBtablemax do begin
active:= true;
if Locate('ID','17',[loCaseInsensitive]) then begin
Edit;
//IBtablemaxBLOBNAME: TBlobField;
blob:= createBlobStream(cachedata.ibtablemax.
FieldByName('Blobname'),bmwrite);
try
blob.Seek(0, soFromBeginning);
fs:= TFileStream.create('c:\milo_test\grid_del5.doc', fmopenRead or
fmsharedenyWrite);
try
blob.copyFrom(fs, fs.size);
Post;
finally
fs.free;
end
finally
blob.free;
cachedata.IBDatabase1.SQLDialect:= 2;
end;
end; // if
end;
end;
And that's the way we can read it from a blob into a file back:
procedure TCacheData.getDocufromBlob;
var blob: TStream;
fs: TFileStream;
begin
cachedata.IBDatabase1.SQLDialect:= 1;
with cachedata.IBtablemax do begin
active:= true;
Locate('ID','17',[loCaseInsensitive]);
//IBtablemaxBLOBNAME: TBlobField;
blob:= createBlobStream(cachedata.ibtablemax.
FieldByName('Blobname'),bmread);
try
blob.Seek(0, soFromBeginning);
fs:= TFileStream.create('D:\maxbox\ibasclient\getdocfromblob5.doc',
fmcreate or fmsharedenyWrite);
try
fs.copyFrom(blob, blob.size);
finally
fs.free;
end
finally
blob.free;
cachedata.IBDatabase1.SQLDialect:= 2;
end;
end;
end;
Here the example with a dataset for more flexibility:
procedure TCacheData.letDocuSaveasBlop(Vdataset: TIBClientDataSet);
var blob: TStream;
fs: TFileStream;
begin
vDataset.Edit;
blob:= vDataSet.createBlobStream(vDataset.
FieldByName('Blobname'),bmwrite);
try
blob.Seek(0, soFromBeginning);
fs:= TFileStream.create('c:\milo_test\grid_del5.doc', fmopenRead
or fmsharedenyWrite);
try
blob.copyFrom(fs, fs.size);
vDataset.Post;
finally
fs.free;
end
finally
blob.free;
end;
end;
The design in InterBase DDL SQL goes like this:
/*OPEN DATABASE "MILO:D:/Program
Files/Borland/InterBase/seekmachine/suchdb8.gdb"
USER "SYSDBA"
PASSWORD "masterkey" */
SET AUTODDL ON;
/* Domain definitions */
CREATE DOMAIN "DOM_DESCRIPTION" AS VARCHAR(1024);
CREATE DOMAIN "DOM_ID" AS INTEGER NOT NULL;
/* Table: MBLOB, Owner: SYSDBA */
CREATE TABLE "MBLOB"
(
"ID" "DOM_ID",
"BLOBNAME" BLOB SUB_TYPE 0 SEGMENT SIZE 80,
"DESCRIPTION" "DOM_DESCRIPTION",
CONSTRAINT "PK_MBLOB" PRIMARY KEY ("ID")
);
CREATE GENERATOR BlobID_GEN;
/*COMMIT WORK;*/
If you want to test the database by saving a BLOB null-record without Delphi, just use an INSERT from an SQL Monitor:
SET AUTODDL ON;
SET TERM ^ ;
INSERT INTO MBLOB VALUES (GEN_ID(blobid_GEN, 1), null, 'shows next
example new');
/*COMMIT WORK;*/
Last there it is possible to loads BLOB data from a stream into a field of a ClientDataSet:
procedure LoadFromStream(stream: TStream);
var ms: TMemoryStream;
begin
if not (cDataSet1.state in [dsInsert, dsEdit]) then
cDataSet.insert;
ms:= TMemoryStream.create();
try
image1.picture.Bitmap.SaveToStream(ms);
cDataSet.LoadFromStream(ms);
finally
ms.free;
end;
cDataSet.post;
end;
But the stream parameter is typically not a BLOB stream like the dataset's CreateBlobStream() which provide a completely separate mechanism for streaming data into a BLOB field.