Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert Binary Data to PDF

Posted on 2016-07-19
8
Medium Priority
?
79 Views
Last Modified: 2016-09-28
Good day all,

I have a field in a SQL table that contains binary data that I need to convert to a PDF. All recommendations are appreciated.
0
Comment
Question by:EMCIT
  • 3
  • 3
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41719533
and what is that binary data?
and do you plan to "display" the data/pdf?
0
 
LVL 11

Author Comment

by:EMCIT
ID: 41719550
The binary is a converted PDF.  I need to convert it back to pdf to save as a .pdf.
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 41719565
What is the data type of the field that the binary data (PDF data) is stored in?

~bp
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Author Comment

by:EMCIT
ID: 41719567
The field is varbinary(MAX)
0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 41719597
Okay, what do you have so far?  Are you working in Access using VBA, or in VBScript?  Can you share the code you have that connects to the data base and does a query, etc?

~bp
0
 
LVL 11

Author Comment

by:EMCIT
ID: 41719603
I'm building an Access front end (VB) that will connect to SQL via ODBC. A variable will be passed that will identify the record to be retrieved. The PDF (Binary) will be saved to a network location.
0
 
LVL 59

Accepted Solution

by:
Bill Prew earned 2000 total points (awarded by participants)
ID: 41719628
Okay, here are a couple of examples of what you need to do I believe. The basic idea is to query the table as you would normally to get the column that has the binary data, then store that in a file using an ADO stream.  Hope these help.

See routines shared here:

https://usefulgyaan.wordpress.com/2014/09/30/store-and-fetch-files-sql-server-tables/

I also found this code snippet that is the same idea:

Dim cn  As ADODB.Connection
Dim rs  As ADODB.Recordset
Dim sql As String
Dim oStream As ADODB.Stream

Set cn = New ADODB.Connection

'Here I use default admin user 'sa' and password is blank
cn.Open "Provider = sqloledb;" & _
        "Data Source=ServerNameOrIP;" & _
        "Initial Catalog=DBName;" & _
        "User ID=sa;" & _
        "Password=;"""

'your sql statment including varbinary max field here it is FILEDATA
sql = " select EMAILID,EMAILFROM,EMAILTO,EMAILSUBJECT,FILEDATA from Tbl "


Set rs = New ADODB.Recordset

rs.Open sql, cn

Do Until rs.EOF
    Set oStream = New ADODB.Stream
    With oStream
         .Type = adTypeBinary
         .Open
         .Write rs.Fields(4).Value
         'Here I use 1st field value as file name i.e. rs.fiedls(0).value 
         'In addition you can join drive and/or folder path to save another location
         .SaveToFile rs.Fields(0).Value & ".pdf", adSaveCreateOverWrite
         .Close
    End With
    Set oStream = Nothing
    rs.MoveNext
Loop

Open in new window

~bp
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

877 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