Title: Saving and loading binary data to/from an MSSQL Image (Blob) field.
Question: How to get binary data in a workable format into or out of an MSSQL Image (Blob) field using ADO components.
Answer:
The main problem I faced when trying to do this was to deal with the fact that TField.Value returns a varOleStr no matter what was written into it, so the data needed to be converted into a more usable format.
Note that there is no checking here that the TField is in fact of the correct type, and that the stream must be created and free-ed elsewhere manually. Also, additional memory equal to the size of the stream/blob is required, so be cautious if large amounts of data are involved.
For ease of use in my own application, I incorporated this functionality into my descendent of TADOQuery.
function LoadFromBlob(const AField: TField; const Stream: TStream): boolean;
var
ResultStr: string;
PResultStr: PChar;
begin
Result := false;
if (Assigned(AField)) and (Assigned(Stream)) then begin
try
ResultStr := AField.Value;
PResultStr := PChar(ResultStr);
Stream.Write(PResultStr^, Length(ResultStr));
Stream.Seek(0,0);
Result := true;
except
end;
end;
end;
function SaveToBlob(const Stream: TStream; const AField: TField): boolean;
var
FieldStr: string;
PFieldStr: PChar;
begin
Result := false;
if (Assigned(AField)) and (Assigned(Stream)) then begin
try
Stream.Seek(0,0);
SetLength(FieldStr, Stream.Size);
PFieldStr := PChar(FieldStr);
Stream.Read(PFieldStr^, Stream.Size);
AField.Value := FieldStr;
Result := true;
except
end;
end;
end;
-------------------------------------------------------
Examples:
If you have an ADO query "qryBlobTest" with the following fields: nFileIcon: Image; nFileData: Image;
// Store an icon in an Image field
function StoreFileIcon: boolean;
var
AFileIcon: TIcon;
MS: TMemoryStream;
begin
Result := false;
AFileIcon := TIcon.Create;
MS := TMemoryStream.Create;
try
AFileIcon.handle := ExtractAssociatedIcon('c:\temp\Test.doc'); // Pseudocode !!
AFileIcon.SaveToStream(MS);
Result := SaveToBlob(MS, qryBlobTest.FieldByName('nFileIcon'));
finally
AFileIcon.Free;
MS.Free;
end;
end;
// Load an icon from an Image field
function LoadFileIcon: boolean;
var
AFileIcon: TIcon;
MS: TMemoryStream;
begin
Result := false;
AFileIcon := TIcon.Create;
MS := TMemoryStream.Create;
try
if (LoadFromBlob(qryBlobTest.FieldByName('nFileIcon'), MS)) then begin
AFileIcon.LoadFromStream(MS);
// Do something with the Icon?
Result := true;
end;
finally
AFileIcon.Free;
MS.Free;
end;
end;
// Save a binary file in an Image field
function StoreFileData: boolean;
var
FS: TFileStream;
begin
FS := TFileStream.Create('c:\temp\Test.doc', fmOpenRead);
Result := SaveToBlob(FS, qryBlobTest.FieldByName('nFileData'));
FS.Free;
end;
// Load a file from an Image field (save it to a file name)
function LoadFileData: boolean;
var
FS: TFileStream;
begin
FS := TFileStream.Create('c:\temp\Test2.doc', fmCreate);
LoadFromBlob(qryBlobTest.FieldByName('nFileData'), FS);
FS.Free;
end;