Solved

How do i open oracle blob field with word using stream

Posted on 2011-03-03
12
2,427 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
 
LVL 36

Expert Comment

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

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now