Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How do I insert an OLE object into a database using ADO?

Posted on 2001-06-04
9
310 Views
Last Modified: 2013-11-26
I wish to insert an OLE object/ Microsoft Word Object into an SQL Server Database. I then want to be able to extract it into another OLE object, and open it.  Only problem is that OLE objects don't seem to support ADO.  Any iteas Tigers??

Cheers,

CJ.
 
0
Comment
Question by:CJHarrap
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6155467
You can't actually store an object in a database.  The whole idea of object-orientated programming is that the object hides its internal state from you (encapsulation).
The only way you could store something like this (that I know of) is if the object is kind enough to serialise all it's internal data into some form of array / file that you can store.
Luckily, programs like Word and Excel do exactly that - save their state into a File!
Problem is, you will have to deal with each item individually.

There's another question around (something about Word and MTS) in which I recommended the chap to save a Word document into a file, then use VB file access to load it into a byte array.  You can then do whatever you want - pass it around the network, store it in a DB, etc....
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6158201
Thanks Morgan,
I have done this many time in Access and Access Projects... Storing Pictures or Documents in users tables.

Anyways...
Ok I have managed to insert a Word Document file into the recordset using the appendchunk method.  It is now a long binary type.  How do I now get that binary file back into word format?

If I set the rs field to another ole object control will this do it?

Cheers,

CJ.

   
0
 
LVL 1

Expert Comment

by:morgan_peat
ID: 6159759
Same sort of way as you do pictures etc.
Load the RS, use GetChunk (I think) to access the data.
Save this to a file using normal VB file access, then load the file using Word automation.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Author Comment

by:CJHarrap
ID: 6162159
No problem getting the file back out of the recordset using getchunk, but it is now a Binary file.  When Word opens it, the document is corrupt.  Any idea how to stop this from happening??

Cheers,

CJ.
0
 
LVL 1

Accepted Solution

by:
morgan_peat earned 100 total points
ID: 6163002
Here's a sample:

    ' Create & do stuff to a word document
    Set oDoc = New Word.Document
   
   
    ' Get temporary file path
    sTempPath = Space$(MAX_PATH)
    lResult = GetTempPath(MAX_PATH, sTempPath)
    sTempPath = Left$(sTempPath, lResult)
   
    ' Get temporary file name
    sTempFile = Space$(MAX_PATH)
    lResult = GetTempFileName(sTempPath, "mp", 0, sTempFile)
    sTempFile = Left$(sTempFile, InStr(sTempFile, Chr$(0)) - 1)
   
    ' Save word doc to temporary file
    oDoc.SaveAs sTempFile
   
   
    ' Load file into byte array
    iFile = FreeFile
    Open sTempFile For Binary Access Read As #iFile
    lFileLen = LOF(iFile)
    ReDim bData(lFileLen)   ' Keep extra byte just in case...
    Get #iFile, , bData
    Close #iFile
   
   
    ' Save into DB
    Set oCn = New ADODB.Connection
    oCn.Open "FILE NAME=c:\mp\test.udl"
   
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM files WHERE file_id = -1", oCn, adOpenKeyset, adLockOptimistic
    oRs.AddNew
    oRs.Fields("file_blob").AppendChunk bData()
    oRs.Update
    lDbId = oRs.Fields("file_id")
    oRs.Close
    Set oRs = Nothing
   
    oDoc.Close
    Set oDoc = Nothing
   
   
   
    ' Load from DB
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM files WHERE file_id=" & lDbId, oCn, adOpenKeyset, adLockReadOnly
   
    ' Get temporary file name again.
    ' I'll keep the same name, just delete
    ' the file that was there before
    Kill sTempFile
   
    iFile = FreeFile
    Open sTempFile For Binary As #iFile
    lFileLen = LenB(oRs.Fields("file_blob"))
    bData = oRs.Fields("file_blob").GetChunk(lFileLen)
    Put #iFile, , bData()
    Close #iFile
   
    oRs.Close
    Set oRs = Nothing
   
    oCn.Close
    Set oCn = Nothing
   
   
    ' Load into word
    Set oWord = New Word.Application
    Set oDoc = oWord.Documents.Open(sTempFile)


There's no error handling, and my Word automation is a bit rusty, but it should give you an idea.  Namely:
- Save Word doc to a temporary file.
- Load doc into a byte array using LOF and Get.
- Save into DB using AppendChunk.
- Get BLOB size by using LenB on recordset Field.
- Use GetChunk to retrieve BLOB into a byte array.
- Save into a temporary file using Put.
- Load back into Word using automation.
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6166293
Beauty!
That works....  I was doing it slightly differently and was only getting half the binary field.

Thanks Morgan, Champ!
0
 
LVL 1

Author Comment

by:CJHarrap
ID: 6166294
Beauty!
That works....  I was doing it slightly differently and was only getting half the binary field.

Thanks Morgan, Champ!
0
 

Expert Comment

by:PremierK
ID: 10618144
CHHarrap are you using this in VB.NET?  If so, can you paste your code
0
 

Expert Comment

by:PremierK
ID: 10618153
I need to do this same thing with VB.NET any sample code?
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

860 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