DelphiFAQ Home Search:
General :: Programming :: Delphi :: Database
Database development with Delphi. Includes issues with BDE, ADO and InterBase.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Only the 40 most recently viewed articles are shown.
You can see the full list here.

Featured Article

Connect to an Access Database with ADO and write a BlobStream value

Question:

How can I connect to an Access Database with ADO and write a BlobStream value?

Answer:

Use the functions from below: ConnectToADODB() to connect to the database and UpdateBlob() to update your data.

unit Unit1;
 
 interface
 
 uses
   Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
   Dialogs, StdCtrls, ADODB, DB, DBTables, ComObj;
 
 type
   TForm1 = class(TForm)
     Button1: TButton;
     procedure Button1Click(Sender: TObject);
   private
     { Private declarations }
   public
     { Public declarations }
   end;
 
 function ConnectToADODB(var Query: TADOQuery; ConnectStr: string) : boolean;
 overload;
 
 function UpdateBlob(Connection: TADOConnection; Spalte: string; Tabelle: string;
                     Where: string; var ms: TMemoryStream) : boolean;
 
 procedure ShowEOleException(AExc: EOleException; Query: string);
 
 var
   Form1: TForm1;
 
 implementation
 
 {$R *.dfm}
 
 procedure TForm1.Button1Click(Sender: TObject);
 var
   Query: TADOQuery;
   ms: TMemoryStream;
   ConnectStr: String;
 begin { TForm1.Button1Click }
   ms := TMemoryStream.Create;
   ms.LoadFromFile('d:\a.txt');
   Query := TADOQuery.Create(nil);
 
   // You must connect to AccessDB first.
  // See: Query.Connection, TADOConection or Query.ConnectionString
  //my function to connect to DB
  ConnectStr := 'Provider=Microsoft.Jet.OLEDB.4.0;'+// provider for Access2000
  'Data Source=C:\db1.mdb;'+// databasefile
  'Mode=ReadWrite|Share Deny None;'+// set to ReadWrite
  'Persist Security Info=False';
   if not ConnectToADODB(Query, ConnectStr) then
     ShowMessage('Connecting to DB failed.');
 
   // data is my row and email the table
  UpdateBlob(Query.Connection, 'blobfieldname', 'Tabelle1',
              'id=1', ms);
   ms.Free;
 
   // disconnect from DB
  Query.Connection.Close;
 
   Query.Free;
 end; { TForm1.Button1Click }
 
 function ConnectToADODB(var Query: TADOQuery; ConnectStr: string) : boolean;
 overload;
 begin { ConnectToADODB }
   Query.Connection := TADOConnection.Create(nil);
   Query.Connection.LoginPrompt := true;
   Query.Connection.ConnectionString := ConnectStr;
   Query.Connection.Open;
   Result := Query.Connection.Connected;
 end; { ConnectToADODB }
 
 function UpdateBlob(Connection: TADOConnection; Spalte: string; Tabelle: string;
                     Where: string; var ms: TMemoryStream) : boolean;
 var
   BlobField: TBlobField;
   Table: TADOTable;
 begin { UpdateBlob }
   Result := true;
   try
     ms.Seek(0, soFromBeginning);
     Table := TADOTable.Create(nil);
     Table.Connection := Connection;
     Table.TableName := Tabelle;
     Table.Filtered := false;
     // Set Filter like SQL-Command '... WHERE id=1'
    Table.Filter := Where;
     Table.Filtered := true;
     Table.Open;
     Table.First;
 
     if not Table.FieldByName(Spalte).IsBlob then
       raise EOleException.Create('The field '+Spalte+' is not a blob-field.',
                                  S_FALSE, 'ITSQL.UpdateBlob',
                                  '', 0);
 
     BlobField := TBlobField(Table.FieldByName(Spalte));
     Table.Edit;
     BlobField.LoadFromStream(ms);
     Table.Post;
     Table.Free;
   except
     on E : EOleException do
       begin
         ShowEOleException(E, 'UPDATE BLOB FROM: SELECT '+Spalte+
                           ' FROM '+Tabelle+' WHERE '+Where);
         Result := false;
       end;
   end; { try }
 end; { UpdateBlob }
 
 procedure ShowEOleException(AExc: EOleException; Query: string);
 var
   ErrShowFrm: TForm;
   Memo: TMemo;
 begin { ShowEOleException }
   ErrShowFrm := TForm.Create(nil);
   ErrShowFrm.Position := poScreenCenter;
   ErrShowFrm.Width := 640;
   ErrShowFrm.Height := 480;
   Memo := TMemo.Create(ErrShowFrm);
   Memo.Parent := ErrShowFrm;
   Memo.Align := alClient;
 
   Memo.Lines.Clear;
   Memo.Lines.Add('Message: '+AExc.Message);
   Memo.Lines.Add('   Source: '+AExc.Source);
   Memo.Lines.Add('   ClassName: '+AExc.ClassName);
   Memo.Lines.Add('   Error Code: '+IntToStr(AExc.ErrorCode));
   Memo.Lines.Add('   Query: '+Query);
 
   ErrShowFrm.ShowModal;
   Memo.Free;
   ErrShowFrm.Free;
 end; { ShowEOleException }
 
 end.

Generated 12:01:26 on Jul 20, 2019