Save and retrieve a file to/ from a blob field

Mirror

Save and retrieve a file to/ from a blob field (Views: 708)

Problem/Question/Abstract:

How can I save a file in an Access database. I would like to save zip files in an Access mdb database. (OLE OBJECT Field) BLOB FIELD

Answer:

Solve 1:

Here's a unit that demonstrates getting a file to/ from a blob field:

unit FileToFromDB;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, FileCtrl, DBCtrls, ExtCtrls, Db, DBTables, Menus;

type
  TFrmFileToFromDB = class(TForm)
    CbxDriveSelect: TDriveComboBox;
    LbxDirSelect: TDirectoryListBox;
    LbxFileSelect: TFileListBox;
    BtnToDB: TButton;
    DataSource1: TDataSource;
    Table1: TTable;
    Table1TheLongInt: TIntegerField;
    Table1ABlobField: TBlobField;
    Table1Bytes1: TBlobField;
    Table1Bytes2: TBytesField;
    Table1B32_1: TBlobField;
    Table1B32_2: TBytesField;
    DBNavigator1: TDBNavigator;
    BtnFromDB: TButton;
    Memo1: TMemo;
    LblCopyright: TLabel;
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure BtnToDBClick(Sender: TObject);
    procedure BtnFromDBClick(Sender: TObject);
    procedure LbxFileSelectChange(Sender: TObject);
    procedure DataSource1DataChange(Sender: TObject; Field: TField);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  FrmFileToFromDB: TFrmFileToFromDB;

implementation

{$R *.DFM}

procedure TFrmFileToFromDB.FormCreate(Sender: TObject);
begin
  Table1.Open;
  Memo1.Clear;
end;

procedure TFrmFileToFromDB.FormDestroy(Sender: TObject);
begin
  Table1.Close;
end;

procedure TFrmFileToFromDB.BtnToDBClick(Sender: TObject);
var
  curNdx: Integer;
  f: Integer;
  theBlobStream: TBlobStream;
  thisFile: TFileStream;
begin
  curNdx := LbxFileSelect.ItemIndex;
  LbxFileSelect.Items.BeginUpdate;
  try
    for f := 0 to LbxFileSelect.Items.Count - 1 do
    begin
      if LbxFileSelect.Selected[f] then
      begin
        LbxFileSelect.ItemIndex := f;
        Table1.Edit;
        theBlobStream := TBlobStream.Create(Table1B32_1, bmReadWrite);
        try
          thisFile := TFileStream.Create(LbxFileSelect.Filename, fmOpenRead);
          try
            theBlobStream.CopyFrom(thisFile, thisFile.Size);
          finally
            thisFile.Free;
          end;
        finally
          theBlobStream.Free;
        end;
        Table1.Post;
        Table1.Next;
      end;
    end;
    Table1.First;
  finally
    LbxFileSelect.ItemIndex := curNdx;
    LbxFileSelect.Items.EndUpdate;
  end;
end;

procedure TFrmFileToFromDB.BtnFromDBClick(Sender: TObject);
var
  hdr: array[1..3] of byte;
  thisFile: TMemoryStream;
  thisText: TMemoryStream;
  theBlobStream: TBlobStream;
begin
  thisFile := TMemoryStream.Create;
  Memo1.Clear;
  try
    theBlobStream := TBlobStream.Create(TBlobField(Table1.FieldByName('B32_1')), bmRead);
    try
      thisFile.CopyFrom(theBlobStream, theBlobStream.Size);
      thisFile.Seek(0, soFromBeginning);
    finally
      theBlobStream.Free;
    end;
    thisFile.Read(hdr[1], 3);
    thisFile.Seek(0, soFromBeginning);
    if (hdr[1] = $FF) and (hdr[2] = $0A) and (hdr[3] = $0) then
    begin
      thisText := TMemoryStream.Create;
      try
        try
          ObjectResourceToText(thisFile, thisText); {DFM to text.}
          thisText.Seek(0, soFromBeginning); {Text stream to beginning}
          Memo1.Lines.LoadFromStream(thisText); {Text to memo1}
        except
          Memo1.Lines.Add('Invalid DFM image');
        end;
      finally
        thisText.Free;
      end
    end
    else
    begin
      Memo1.Lines.Add('Invalid DFM image');
    end;
  finally
    thisFile.Free;
  end;
end;

procedure TFrmFileToFromDB.LbxFileSelectChange(Sender: TObject);
begin
  BtnToDB.Enabled := LbxFileSelect.SelCount > 0;
end;

procedure TFrmFileToFromDB.DataSource1DataChange(Sender: TObject; Field: TField);
begin
  BtnFromDB.Click;
end;

end.

Solve 2:

If you develop a database related software, then very popular task is to save some files (documents/images/reports/etc) in some BLOB field of table and use these saved data later.

In this tip I want to show how this task could be solved.

To save a file to BLOB:

blob := yourDataset.CreateBlobStream(yourDataset.FieldByName('YOUR_BLOB'),
  bmWrite);
try
  blob.Seek(0, soFromBeginning);

  fs := TFileStream.Create('c:\your_name.doc', fmOpenRead or
    fmShareDenyWrite);
  try
    blob.CopyFrom(fs, fs.Size)
  finally
    fs.Free
  end;
finally
  blob.Free
end;

to load from BLOB:

blob := yourDataset.CreateBlobStream(yourDataset.FieldByName('YOUR_BLOB'),
  bmRead);
try
  blob.Seek(0, soFromBeginning);

  with TFileStream.Create('c:\your_name.doc', fmCreate) do
  try
    CopyFrom(blob, blob.Size)
  finally
    Free
  end;
finally
  blob.Free
end;

Using this code you can work with any database engine (BDE/ADO/DAO/ODBC/etc) and any file format (document of MS Word, spreadsheet of MS Excel, bitmap or jpeg pictures, wav-files etc)

<< Back to main page