Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Opposite of OLE Control "InsertFile" method

  1. Issue
0
Votes
Undo
  1. Dan Harrel
    Dan Harrel
  2. PowerBuilder
  3. Wednesday, 31 January 2018 15:38 PM UTC

We use the olecontrol.insertFile (...) method to populate an olecontrol object with a file system document - for example, "foo.xlsx". 

How can we now extract the document out of the olecontrol, and write it back into a file system document? 

We know we cannot do the following, at least for complex documents such as XLSX spreadsheets:

  blob    blob_objectData
  int     i_rc, i_file
  long    l_rc

  i_rc = ole_1.insertFile ("C:\temp\foo1.xlsx")

  blob_objectData = ole_1.objectdata

  i_file = fileOpen ("C:\temp\foo2.xlsx", StreamMode!, Write!, LockWrite!, Replace!)
  l_rc = filewriteEx (i_file, blob_objectData)
  i_rc = fileClose (i_file)

Basically we are looking for something that is conceptually an "olecontrol.extractFile (...)" method.  Does anyone have a suggestion on how to do this? 

The real-world problem we are trying to solve is as follows: Application users embed document files such as spreadsheets in our database.  How can we automate exporting these database-stored documents back into document files without having to activate each one interactively and performing a "Save As..."? 

Thanks!

  1. OLE
help installing PowerServer Tookit in Old PB Versi...
Powerbuilder IDE stops working during build of app...
Responses (2)
Dan Harrel
Dan Harrel Accepted Answer Pending Moderation
  1. Friday, 23 February 2018 17:47 PM UTC
  2. PowerBuilder
  3. # 1

I can now report that this need can be met by the tool I mentioned in this thread: "SQL Blob Viewer" by Yohz software: http://www.yohz.com/sbv_details.htm.  If you need to extract OLE documents from your database into native files, run, don't walk, to this tool!  We successfully and switfly exported over 30,000 documents from a database. 

Support from this tool is out-of-this-world.  A number of document types were not recognized by the tool.  We used the tool to extract binaries of representative documents, and sent them to Yohz support.  We had a new version of SQL Blob Viewer that recognized the type within hours.  We found a couple of minor behavior issues with the tool, and they were fixed within hours too. 

Comment
There are no comments made yet.
René Ullrich
René Ullrich Accepted Answer Pending Moderation
  1. Thursday, 1 February 2018 07:15 AM UTC
  2. PowerBuilder
  3. # 2

Hi Dan,

To get the file from OLE control you have to use the Save methods provided by OLE server application (in your example Excel). I don't know a way to get it directly from OLE control.

To get the files from database the SELECTBLOB statement maybe what you are looking for.

HTH,

René

Comment
Load more comments
Brad Mettee
  1. Brad Mettee
  2. Tuesday, 6 February 2018 14:01 PM UTC
Are the OLE controls used to edit the information in any way? If not, you can read the file into a blob directly, and store that blob to the DB, without using the OLE controls at all.



You may also want to try a binary compare of an original file to the ole_1.objectdata version, so you can see if there's any way to strip off the header before writing the file out (by using blobmid or similar).

  1. Helpful
Dan Harrel
  1. Dan Harrel
  2. Tuesday, 6 February 2018 16:05 PM UTC
Indeed the OLE controls are used to edit the information, and then store the edited information back into the database.  Otherwise, you are correct, we could just write the Excel binary into the database, and then retrieve it, write it to a temporary file, and launch. 



I think we tried a binary compare and it was not so clear cut how to strip off the "OLE stuff", but I will look again. 



I wish this all were easier (i.e. opposite of ole_1.insertfile (...)), but it appears not to be. 



That said - I found one product out there that may do the trick: http://www.yohz.com/sbv_details.htm



Thanks!

  1. Helpful
Dan Harrel
  1. Dan Harrel
  2. Wednesday, 14 February 2018 19:29 PM UTC
Hi Rene,



We are trying your suggestion with incomplete success, and hope you can help further. 



If we "insertfile" an XLSX spreadsheet into an OLE control ole_1, and then do the following:



oleobject  obj_oleobj



obj_oleobj = ole_1.object

// 51 =  (without macro's in 2007-2016, xlsx)

b_rc = obj_oleobj.saveas ("c:\temp\bla.xlsx", 51)



We get a file "c:\temp\bla.xlsx" that, when double-clicked, starts up Excel, but shows as blank.  A lighter excel viewer will show us the spreadsheet contents. 



What we are actually doing, however, is after "insertfile" an XLSX spreadsheet into ole_1, we write ole_1.objectdata into a blob database column. 



Later, we selectblob the database value into blob_foo, and then:



oleobject obj_oleobj



ole_1.objectdata = blob_foo

obj_oleobj = ole_1.object



// 51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)

b_rc = obj_oleobj.saveas ("c:\temp\bla.xlsx", 51)



However the "saveas" verb now produces an error. 



Any insights you have would be appreciated into:



1) Why the first code block produced a XLSX file that seemed to be accepted by Excel but did not display



2) Why the second code block did not behave the same as the first.  (is "obj_oleobj = ole_1.object" OK and usable for automation verbs?). 



Thanks,



Dan.

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.