Save and retrieve a file to/ from a blob field (Views: 28)
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) |