Solved

How do i open oracle blob field with word using stream

Posted on 2011-03-03
12
2,455 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

789 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