Use ADO to connect to an Access Database and write a BlobStream value

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
  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;

function ConnectToADODB(var Query: TADOQuery; ConnectStr: String): Boolean; Overload;
begin
  Query.Connection := TADOConnection.Create(nil);
  Query.Connection.LoginPrompt := True;
  Query.Connection.ConnectionString := ConnectStr;
  Query.Connection.Open;
  result := Query.Connection.Connected;
end;

function UpdateBlob(Connection: TADOConnection; Spalte: String; Tabelle: String; Where: String; var ms: TMemoryStream): Boolean;
var
  BlobField: TBlobField;
  Table: TADOTable;
begin
  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;
end;

procedure ShowEOleException(AExc: EOleException; Query: String);
var
  ErrShowFrm: TForm;
  Memo: TMemo;
begin
  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;

end.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

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

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;

function ConnectToADODB(var Query: TADOQuery; ConnectStr: String): Boolean; Overload;

begin

Query.Connection := TADOConnection.Create(nil);

Query.Connection.LoginPrompt := True;

Query.Connection.ConnectionString := ConnectStr;

Query.Connection.Open;

result := Query.Connection.Connected;

end;

function UpdateBlob(Connection: TADOConnection; Spalte: String; Tabelle: String; Where: String; var ms: TMemoryStream): Boolean;

var

BlobField: TBlobField;

Table: TADOTable;

begin

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;

procedure ShowEOleException(AExc: EOleException; Query: String);

var

ErrShowFrm: TForm;

Memo: TMemo;

begin

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;

end.

Make a question and you'll have an answer…

Make a question and you'll have an answer…

Use ADO to connect to an Access Database and write a BlobStream value