Solved

How do i open oracle blob field with word using stream

Posted on 2011-03-03
12
2,448 Views
Last Modified: 2013-11-23
Hello Experts
I have Oracle database that stores .doc files as blob.
Today I fetch those files and save them to temp directory before I open then with
ShellExecute( self.Handle, nil, PCHAR(dir), Nil, Nil, SW_SHOWNORMAL );

Is there any way to save the file into stream and open it with MIcrosoft Word 2007 WITHOUT saving it to my hard disk first?


I am using Delphi 2007 and Oracle 10g.

Best Regards
Albert.
0
Comment
Question by:Programm
  • 5
  • 5
  • 2
12 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35028367

I don't believe its possible unless you are using an OleContainer then you can call CreateObjectFromStream
0
 

Author Comment

by:Programm
ID: 35028680
Hi ewanqoya
Can you show me an example of that function?
My DB function return TLOBLocator.



0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35033073

Note, using this method you are opening the file in a TOleContainer and not calling Mocrosoft Word

Add this method to the OleContainer as public
procedure CreateObjectFromStream(Stream: TStream);

Drop a TOleContainer component on your form

Then, pass your stream straight to the method instead of creating a file
eg

var
  Stream: TStream;
  Field: TField;
begin
 Field := Dataset.FieldByName('Name of field with Blob');
 Stream := Dataset.CreateBlobStream(Field, bmRead);
 try
   OleContainer1.CreateObjectFromStream(Stream);
 finally
   FreeAndNil(Stream);
 end;
end;

procedure TOleContainer.CreateObjectFromStream(Stream: TStream);
var
  DataHandle: HGLOBAL;
  Buffer: Pointer;
begin
  DataHandle := GlobalAlloc(GMEM_MOVEABLE, Stream.Size);
  if DataHandle = 0 then OutOfMemoryError;
  try
    Buffer := GlobalLock(DataHandle);
    try
      Stream.Read(Buffer^, Stream.Size);
    finally
      GlobalUnlock(DataHandle);
    end;
    OleCheck(CreateILockBytesOnHGlobal(DataHandle, True, FLockBytes));
    DataHandle := 0;
    OleCheck(StgOpenStorageOnILockBytes(FLockBytes, nil, STGM_READWRITE or
      STGM_SHARE_EXCLUSIVE, nil, 0, FStorage));
    OleCheck(OleLoad(FStorage, IOleObject, self, FOleObject));
    FDrawAspect := DVASPECT_CONTENT;
    InitObject;
    FOleObject.SetExtent(DVASPECT_CONTENT, PixelsToHimetric(Point(ClientWidth, ClientHeight)));
    SetDrawAspect(False, 0);
    UpdateView;
  except
    if DataHandle <> 0 then GlobalFree(DataHandle);
    DestroyObject;
    raise;
  end;
end;

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 35034511
the TOleContainer will call Word ...
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 35034522
from oracle inside blob, you will always to have to save it on local disk

things like olecontainer will save it in temp folder behind the scenes
so what is the point in taking a detour ?
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35036505

Actually Geert, many applications create temporary files behind the scene. Does that mean we should never use load from stream methods?
0
 

Author Comment

by:Programm
ID: 35037069
Thanks for the code ewanqoya. It's working great. It dosn't create a .doc file in the temp folder, only .tmp file and that is allright.
Can you please show me how to save it back to the db from the Stream? That would be great.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35046630

You can use LoadFromStream


var
  F: TField;
  Stream: TStream;
begin
  F := Dataset.FindField('Your field name');
  Stream := TStream.Create;
  try
    OleContainer1.SaveToStream(Stream);
    Stream.Seek(0, soFromBeginning);
    TBlobField(F).LoadFromStream(Stream);
  finally
    FreeAndNil(Stream);
  end;
end;

Open in new window

0
 

Assisted Solution

by:Programm
Programm earned 0 total points
ID: 35057981
Ok, that almost worked. But I go a Abstract error because TStream is abstract and SaveToStream(Stream) doesn't work.
I tried to change it to TMemoryStream but my blob field was corrupted when I tried to open it again.

 
var
  Stream : TMemoryStream;
  Field : TField;
  iSizeAfter : integer;
begin
    m_qry.Edit;
    Field := m_qry.FindField('BSKRA');
    Stream := TMemoryStream.Create;

    OleContainerP1.SaveToStream(Stream);
    Stream.Seek(0, soFromBeginning);
    TBlobField(Field).LoadFromStream(Stream);
    m_qry.Post;
    Self.Close;
end;

Open in new window


0
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
ID: 35066114

Add check in the procedure to look for the OLE header


procedure TOleContainer.CreateObjectFromStream(Stream: TStream);
var
  DataHandle: HGLOBAL;
  Buffer: Pointer;
  Header: TStreamHeader;
begin
  Stream.ReadBuffer(Header, SizeOf(Header));
  if (Header.Signature = StreamSignature) then 
  begin
    //if it has OLE header then read the normal way
    Stream.Seek(0, soFromBeginning);
    LoadFromStream(Stream);
    Exit;
  end;

  Stream.Seek(0, soFromBeginning);
  DataHandle := GlobalAlloc(GMEM_MOVEABLE, Stream.Size);
  if DataHandle = 0 then OutOfMemoryError;
  try
    Buffer := GlobalLock(DataHandle);
    try
      Stream.Read(Buffer^, Stream.Size);
    finally
      GlobalUnlock(DataHandle);
    end;
    OleCheck(CreateILockBytesOnHGlobal(DataHandle, True, FLockBytes));
    DataHandle := 0;
    OleCheck(StgOpenStorageOnILockBytes(FLockBytes, nil, STGM_READWRITE or
      STGM_SHARE_EXCLUSIVE, nil, 0, FStorage));
    OleCheck(OleLoad(FStorage, IOleObject, self, FOleObject));
    FDrawAspect := DVASPECT_CONTENT;
    InitObject;
    FOleObject.SetExtent(DVASPECT_CONTENT, PixelsToHimetric(Point(ClientWidth, ClientHeight)));
    SetDrawAspect(False, 0);
    UpdateView;
  except
    if DataHandle <> 0 then GlobalFree(DataHandle);
    DestroyObject;
    raise;
  end;
end;

Open in new window

0
 

Author Comment

by:Programm
ID: 35067884
Great !!!!!! That worked.
Thank you so much ewanqoya for your help.
0
 

Author Closing Comment

by:Programm
ID: 35120545
Great work by ewanqoya
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question