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

Resolved Extract file from OLE BLOB and save to disk

  1. Advice
0
Votes
Undo
  1. Mike Kochevar
    Mike Kochevar
  2. PowerBuilder
  3. Wednesday, 10 July 2024 14:23 PM UTC

Good day to you!

I am trying to extract the file contents from a blob saved to the database using an OLE object in PB12.6. The blob seems to save to the database as .CFB files. I see this when using PL/SQL dbms_lob.substr(document,2000,1) the value D0CF11E0A1B11AE1 are the first characters and from what I have read from google searches is that D0CF11E0A1B11AE1 is the header for OLE or .CFB (microsoft compound file binary) file.

Due to bad design I had to figure out the file extension and construct a file name from the blob, I am able to save the blob to disk but files of type .png, .jpg, or .jpeg  will not open (windows sees the files are corrupt). Word docs (.doc and .docx) will open but Word is able to fix any problems when opening. Excel spreadsheets (.xls and .xlsx) will open as Excel will fix any problems as Word does. Adobe (.pdf) opens without error (the only success I have had).

I have tried to save the file using PL/SQL scripts, .Net app, and now I am trying to parse through the blob using an OLE object in PowerBuilder 12.6 and save the file to disk but I am getting the same results as I mentioned above. I have seen a solution where the blobs can be saved as .zip files and then 7Zip can be used to open the files but this is not a practical solution for our client as there are 100k+ files to save.

Does anyone have an advice for me on how I can continue with this project?

Thank you for your time,

Mike

Get array types from PostgreSQL in Datawindow
import pdf files from my computer to dw report
Responses (3)
Mike Kochevar
Mike Kochevar Accepted Answer Pending Moderation
  1. Thursday, 11 July 2024 13:20 PM UTC
  2. PowerBuilder
  3. # 1

Thank you Andreas, I will continue.

Code that retrieve the blob from the database and saves to disk is below. After the code has completed the files created will not open.

Errors when opening (OS = Microsoft server 2008)...

Double Click: Photo Gallery can't open this picture or video. The file appears to be damaged or corrupted.

/*
Create the file from the blob in the database.
*/

Blob lblob_file


for ll_row = 1 to ll_rows
ll_doc_att_s = lds_datastore.getitemnumber( ll_row, "document_attachment_s" )
ls_table_name = lds_datastore.getitemstring( ll_row, "table_name" )
ls_file_ext = lds_datastore.getitemstring( ll_row, "file_extension" )

SELECTBLOB document
INTO :lblob_File
FROM document_attachment
WHERE document_attachment_s = :ll_doc_att_s
USING SQLCA;

ls_Path = "C:\PIMS Testing MK\FileExportDir\" + String(ll_doc_att_s) + "_" + ls_table_name + ls_file_ext

li_File = FileOpen(ls_Path, StreamMode!, Write!, Shared!, Replace!)

If li_File > 0 Then
// Determine how many times to call FileWrite
ll_FileLen = Len(lblob_File)
If ll_FileLen > 32765 Then
If Mod(ll_FileLen, 32765) = 0 Then
ll_Loops = ll_FileLen/32765
Else
ll_Loops = (ll_FileLen/32765) + 1
End If
Else
ll_Loops = 1
End If

For ll_Step = 1 To ll_Loops
FileWriteEx(li_File,BlobMid(lblob_File,((ll_Step - 1)*32765) + 1, 32765))
Next
Else
//log the error, or handle
End If
FileClose(li_File)
next

Comment
There are no comments made yet.
Mike Kochevar
Mike Kochevar Accepted Answer Pending Moderation
  1. Thursday, 11 July 2024 12:50 PM UTC
  2. PowerBuilder
  3. # 2

I should have entered this as a "how to" and not "advice" I apologize for that mistake. I feel that the next steps are for me to provide some code so everyone can see how I am saving the blob to disk. Should I continue with this topic as "advice" or should I enter a new "how to" question?

Comment
Andreas Mykonios
  1. Andreas Mykonios
  2. Thursday, 11 July 2024 12:57 PM UTC
No need to change your question type... You are ok.

Andreas.
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 10 July 2024 16:56 PM UTC
  2. PowerBuilder
  3. # 3

Hi Mike;

  We've been doing this type of processing since way back to the PB version 4/5 days. The key aspects to check are:

  • Make sure that the blob data stream is not being truncated on the SelectBlob() command. This is a DB client connection consideration depending on how you connect to the DBMS.
  • After the successful SelectBlob command, the next aspect is to ensure that the entire blob data stream is written to a file. The best approach here is to use the FileWriteEX() command.
  • The last consideration in writing the file is to make sure that your using the correct encoding scheme.

HTH 

Regards ... Chris 

Comment
Mike Kochevar
  1. Mike Kochevar
  2. Thursday, 11 July 2024 12:51 PM UTC
I should have entered this as a "how to" and not "advice" I apologize for that mistake. I feel that the next steps are for me to provide some code so everyone can see how I am saving the blob to disk. Should I continue with this topic as "advice" or should I enter a new "how to" question?
  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.