Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Extract PDF from BLOB (Read 6245 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Extract PDF from BLOB
17.03.06 at 11:58:25
 
Mike from the UK asked the following question:
 
Question:
I am interested in being able to return a BLOB from the database(actaully a previously stored PDF) and writing it out to the windows filesystem. The query to return the PDF's would be based on a range of ID's in the excel spreadsheet
 
Answer:
The last recordset is available as SQLXL.Database.PreviousDynaset.
Note that this is my own recordset class clsADODynaset or clsOO4ODynaset and not the ADO or OO4O one.
You can use MoveFirst and MoveNext to navigate it should you wish. But from what I read you will return the BLOBs one by one.
 
Prepare your test SQL so it returns the blob as the only column. It will be something like this:
select myblob from mytable where id = 1234
 
When you have the SQL, run it through the SQL dialog while you record a macro.  
In the resultset options dialog, choose to not get output.
The code will look like this"

Sub Macro1()
  SQLXL.Sql.setText "select myblob from mytable where id=1234"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
End Sub

 
Running this macro will result in no output but it will have populated the PreviousDynaset property. You can use this to get access to your field:
SQLXL.Database.PreviousDynaset.Fields(0).Value
 
Now you only need to dump the contents in a file. I have added this feature for you in SQL*XL 4.0.42. through the SaveToFile method:
SQLXL.Database.PreviousDynaset.Fields(0).SaveToFile "c:\myfile.pdf"
 
Example:
As a simpel example to run even if you don't have pdf files in blobs, please look at the following macro. It assumes you have connected to the database. It will run select 1 from dual. If you do not have an Oracle database you can try it with a different SQL statement.
 

Sub Macro1()
  SQLXL.Sql.setText "select 1 from dual"
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(1).Execute
 
  SQLXL.database.PreviousDynaset.fields(0).SaveToFile "c:\mytest.txt"
End Sub
Back to top
 
« Last Edit: 17.03.06 at 12:02:39 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1