Displaying PDF BLOB Data From Within a WebFOCUS Web Application

Relational databases can store entire PDF’s within a database record. These PDF’s are often stored in a column with a ‘BLOB’ format type. BLOB is an acronym for Binary Large OBject. In our example web application to follow, Human Resources information is stored within a Microsoft SQL Server database. A ‘Performance Appraisal’ table contains completed performance appraisals for each of our fictitious company’s employees. The performance appraisals are in PDF format.

By David Briars, Sr. Applications Developer
National Futures Association

Introduction

Relational databases can store entire PDF’s within a database record. These PDF’s are often stored in a column with a ‘BLOB’ format type. BLOB is an acronym for Binary Large OBject. In our example web application to follow, Human Resources information is stored within a Microsoft SQL Server database. A ‘Performance Appraisal’ table contains completed performance appraisals for each of our fictitious company’s employees. The performance appraisals are in PDF format.

'Performance Appraisal Display' Web Application

Our ‘Performance Appraisal Display’ web application consists of the following two (2) focexecs:

  1. ‘PerfAppReport’ – Show a list of employees and offer a link to open a given employee’s performance appraisal within a popup window. 
  2. ‘PerAppPDFDisplay’ – Display the Performance Appraisal PDF within the new popup window. 

Each focexec results in a web page displayed to the user. 
 

Figure 1: Web application from the user’s point of view. 

Figure 1



WebFOCUS Synonyms

Before developing our web application, we built synonyms (metadata) for our ‘Performance Appraisal’ MS SQL Server table.

For this task we used WebFOCUS Developer Studio’s Synonym Builder tool. 
 

Figure 2: MS SQL Server Table, Performance Appraisal, Synonyms – Access and Master Files.

Figure 2


WebFOCUS Code - Performance Appraisal Report


Figure 3: Performance Appraisal report focexec. 

Figure 3


The Performance Appraisal Report (PerfAppReport) focexec uses the highlighted TABLE FILE command to create a report containing employee appraisal data and links. 

The links, built with the powerful WebFOCUS StyleSheet drill down syntax, call the Performance Appraisal PDF Display focexec (PerfAppPDFDisplay). 

Given the StyleSheet drill down commands highlighted, we see that the link will:

  1. Be effective for the ‘NAME’ column. 
  2. Target a new blank window.
  3. Call focexec ‘perfapppdfdisplay’ to fill the window. 
  4. Pass a variable called ‘PERFAPPID to the called report, with its value taken from the first (Performance Appraisal ID) column. 
     

WebFOCUS Code - Performance Appraisal PDF Display

The Performance Appraisal PDF Display focexec (PerfAppPDFDisplay) has the following three main sections:

  1. Read the BLOB MS SQL Server Column in order to download the PDF and report the filename assigned to the PDF.
  2. Create variables containing metadata about the PDF file and its location.
  3. Copy the downloaded file to a file with a .pdf extension, and display to the user. 

The following three figures will display each of the three focexec sections. 
 

Figure 4: Read MS SQL Server data and report filename. 

Figure 4


Figure 5: Create variables containing metadata about the downloaded file. 

Figure 5


Figure 6: Copy the downloaded file to a file with a .pdf extension, and display to the user. 

Figure 6

Note: This example stores a copy of the PDF file into FOCCACHE.  FOCCACHE is accessible via the intranet, in this example, via a ‘share folder’.  You may want to check to see if your installation has a preferred method/location for storing/accessing temporary files. 
 

Addendum

WebFOCUS Code Text - PerfAppReport.

-*

-* File PerfAppReport.fex

-*

DEFINE FILE PERFAPP

RAND/D9cL WITH NAME = ABS(RDNORM(RAND)) * 1000;

END

 

TABLE FILE PERFAPP

"Employee Performance"

"Appraisal Report"

PRINT PERFAPPID  AS 'Performance App,ID'

      NAME       AS 'Employee,Name'

         RAND       NOPRINT

ON TABLE SET PAGE OFF

ON TABLE PCHOLD FORMAT HTML

ON TABLE SET HTMLCSS ON

ON TABLE SET STYLE *

  INCLUDE = eniatruecolors, $

 TYPE=TITLE, JUSTIFY=CENTER,$

 TYPE=DATA,  COLUMN=PERFAPPID, JUSTIFY=CENTER,$

 TYPE=DATA,  COLUMN=NAME,

              TARGET='_blank',

              FOCEXEC=perfapppdfdisplay( \

              PERFAPPID=N1 \

                       RND=RAND

              ),$

ENDSTYLE

END

 

WebFOCUS Code Text – PerfAppPDFDisplay.

-*
-* File PerfAppReport.fex
-*
DEFINE FILE PERFAPP
RAND/D9cL WITH NAME = ABS(RDNORM(RAND)) * 1000;
END

TABLE FILE PERFAPP
"Employee Performance"
"Appraisal Report"
PRINT PERFAPPID  AS 'Performance App,ID'
      NAME       AS 'Employee,Name'
         RAND       NOPRINT
ON TABLE SET PAGE OFF
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
  INCLUDE = eniatruecolors, $
 TYPE=TITLE, JUSTIFY=CENTER,$
 TYPE=DATA,  COLUMN=PERFAPPID, JUSTIFY=CENTER,$
 TYPE=DATA,  COLUMN=NAME,
              TARGET='_blank',
              FOCEXEC=perfapppdfdisplay( \
              PERFAPPID=N1 \
                       RND=RAND
              ),$
ENDSTYLE
END

Loading BLOB Columns via MS SQL Server - Example
Figure 7


Development Environment for this Article
The ‘Performance Appraisal Display’ web application example was developed, executed, and presented using:

  • WebFOCUS Developer Studio – Release 8.0.06
  • Apache Tomcat Server 7.0.33 – Web and Application Server
  • Microsoft Windows 7
  • Microsoft Internet Explorer 11
  • Microsoft SQL Server 2014 Management Studio
  • Adobe Reader XI
     

Disclaimer
The information contained in this article is for information purposes only, and may not apply to your situation.  All data, text, and links are for information purposes only and are not warranted for content, accuracy, or any other implied or explicit purpose.

Due to the nature of this educational material, this article may refer to numerous hardware and software products by their trademarks.  In most, if not all cases, these designations are claimed as trademarks or registered trademarks by their respective companies.  It is not this author’s intent to use any of these names generically.  The reader is therefore cautioned to investigate all claimed trademark rights before using any of these names other than to refer to the product described.