Home » Summer 2009 » Storing Images in BLOB Fields in WebFOCUS PDF Reports and Other 7.6.9 New Features

Storing Images in BLOB Fields in WebFOCUS PDF Reports and Other 7.6.9 New Features

By Susan Trommer

WebFOCUS Release 7.6.9 includes a frequently requested new feature to insert images stored in BLOB fields in a WebFOCUS report column, heading, footing, subhead or subfoot.  

In Release 7.6.9 this enhancement is implemented for:

  • WebFOCUS PDF format reports, including components in Compound Layout Reports and Coordinated Compound Layout Reports. Support for additional WebFOCUS formats is being discussed for implementation in a future WebFOCUS new feature release.
  • Images stored in BLOB fields in the following data sources that supported the Binary Large Object (BLOB) data type: MS SQL Server, DB2, Oracle, Informix, and PostgreSQL using PostgreSQL’s BYTEA datatype.
  • BLOB fields can be used as an image source when an instance of the BLOB field contains an exact binary copy of a GIF or JPEG image. Images of different formats (GIF, JPEG) can be mixed within the same BLOB field. WebFOCUS determines the format from the image’s header.

How to Insert a BLOB Image in Report Output
The images stored in BLOB fields are incorporated into a WebFOCUS PDF report using the PRINT or LIST command and WebFOCUS Style Sheets. The BLOB field must be referenced in the request (FEX) in a PRINT or LIST command (aggregation is not supported), not just the WebFOCUS Style Sheet.  

To include an image stored in a BLOB field in a heading, footing, subhead, or subfoot:

TYPE=headtype, [BY=byfield,] IMAGE=(blobfield),
POSITION=(+xpos +ypos), SIZE=(width height),[PRESERVERATIO={ON|OFF}],$

For a report column:

TYPE=DATA, COLUMN=bloboutputfield, IMAGE=(blobfield),
SIZE=(width height),[PRESERVERATIO={ON|OFF}] $

Where:

Headtype is heading, footing, subhead or subfoot.

Byfield is the sort field that generated the subhead or subfoot.

Blobfieldis any valid column reference for the BLOB field that contains the image. The BLOB field must be referenced in a PRINT or LIST command in the request. If the BLOB field is embedded in a heading, subheading, footing or subfooting, rather than a column, the Style Sheet declaration is responsible for placing the image in the heading or footing. To make the BLOB image accessible to the Style Sheet, the BLOB field must be referenced in the PRINT or LIST command with the NOPRINT option. Do not reference the BLOB field name in the heading or footing itself.

xpos ypos are the offsets from the top left corner of the heading or footing component in the units specified by the UNITS parameter (default is inches). The default is no offset.

These numbers are specified with a leading plus (+) sign to indicate that they are relative to the top left corner of the report component, as opposed to absolute page coordinates.

width height specifies  the dimensions to which the image is to be scaled in the units specified by the UNITS parameter (default is inches).

For a heading or footing component: If omitted, the original dimensions of the
image are used (any GIF or JPEG image has an original, un-scaled size based on the
dimensions of its bitmap).

You must allow an adequate offset (POSITION) for the image to begin past the text of the heading or footing (if any) and also must allow for adequate space to prevent the image from overlapping the lines following the heading component. Reserve the space for them with blank lines; this space is accounted for when computing the heading or footing height.

There are no special considerations for FOOTING BOTTOM. The space required by the footing is computed in advance based on the height of the footing, and should not be affected by BLOB images in the columns, subheads or other components.

For a report column: If omitted, the default size is 1 inch by 1 inch. The width of the column and the spacing between the lines is automatically adjusted to accommodate the image.

Bloboutputfielddesignates the column to be replaced with an image, which usually will be the image field itself. Multiple report columns can contain images. Note that BLOB columns are left justified by default.

PRESERVERATIO={ON|OFF}
PRESERVERATIO=ON specifies that the aspect ratio (ratio of height to width) of the image should be preserved when scaled to the specified SIZE. This prevents distorting the appearance of the image. The image is scaled to the largest size possible within the bounds specified by SIZE, for which the aspect ratio can be maintained.

File Size and Compression Considerations
The actual size of an image stored in the BLOB field may vary from image to image, and scaling the images to a designated size allows them to better fit into a columnar report.
PDF files that contain many images can be large. Scaling the images to a smaller size using the SIZE attribute does not decrease the size of the file. Note also that using SET
FILECOMPRESS=ON will not reduce the size of images in a PDF file, since images are already saved in compressed form.

Displaying an Image From a BLOB Field in a Report Column
The following Master File describes the MS SQL Server data source named retaildetail, which contains product information for a sports clothing and shoe retailer:

FILENAME=RETAILDET, SUFFIX=SQLMSS , $
SEGMENT=SEG01, SEGTYPE=S0, $
FIELDNAME=FOCLIST, ALIAS=FOCLIST, USAGE=I5, ACTUAL=I4, $
FIELDNAME=PRODUCTID, ALIAS=ProductId, USAGE=A5, ACTUAL=A5, MISSING=ON, $
FIELDNAME=DEPARTMENT, ALIAS=Department, USAGE=A10, ACTUAL=A10, MISSING=ON, $
FIELDNAME=CATEGORY, ALIAS=Category, USAGE=A30, ACTUAL=A30, MISSING=ON, $
FIELDNAME=SPORTS, ALIAS=Sports, USAGE=A30, ACTUAL=A30, MISSING=ON, $
FIELDNAME=GENDER, ALIAS=Gender, USAGE=A10, ACTUAL=A10, MISSING=ON, $
FIELDNAME=BRAND, ALIAS=Brand, USAGE=A25, ACTUAL=A25, MISSING=ON, $
FIELDNAME=STYLE, ALIAS=Style, USAGE=A25, ACTUAL=A25, MISSING=ON, $
FIELDNAME=COLOR, ALIAS=Color, USAGE=A25, ACTUAL=A25, MISSING=ON, $
FIELDNAME=NAME, ALIAS=Name, USAGE=A80, ACTUAL=A80, MISSING=ON, $
FIELDNAME=DESCRIPTION, ALIAS=Description, USAGE=A1000, ACTUAL=A1000, MISSING=ON, $
FIELDNAME=PRICE, ALIAS=Price, USAGE=D7.2, ACTUAL=D8, MISSING=ON, $

The following Master File describes the MS SQL Server data source named retailimage, which has the same product ID field as retaildetail and has an image of each product stored in a field named prodimage whose data type is BLOB:

FILENAME=RETAILIMAGE, SUFFIX=SQLMSS , $
SEGMENT=RETAILIMAGE, SEGTYPE=S0, $
FIELDNAME=PRODUCTID, ALIAS=PRODUCTID, USAGE=A5, ACTUAL=A5, $
FIELDNAME=PRODIMAGE, ALIAS=F02BLOB50000, USAGE=BLOB, ACTUAL=BLOB,
MISSING=ON, $

The following request joins the two data sources, and prints product names and prices with the corresponding image.

JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product List"
" "
PRINT NAME/A30 PRICE PRODIMAGE AS 'PICTURE'
BY PRODUCTID NOPRINT
BY NAME NOPRINT
ON NAME UNDER-LINE
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,$
TYPE=HEADING, SIZE = 18, FONT = ARIAL, COLOR=RED,$
TYPE=DATA,COLUMN=PRODIMAGE,IMAGE=(PRODIMAGE),SIZE=(1 1),$
ENDSTYLE
END

The image is placed in the report column using the following StyleSheet declaration, which names the image field and establishes the size and position in the column for the image:

TYPE=DATA,COLUMN=PRODIMAGE,IMAGE=(PRODIMAGE),SIZE=(1 1),$


Screen 1 shows the partial PDF report output.

Displaying an Image From a BLOB Field in a SUBHEAD
The following example joins the RETAILIMAGE Master File to the RETAILDETAIL Master File and displays a product catalog in which all of the data is displayed in a subhead. The subhead has extra blank lines after the text of the subheading to accommodate the image, and the vertical offset of .6 inches allows the top of the image to fall below the text. Note that the image field is specified in the request with the NOPRINT option and is not referenced in the subheading at all:

JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
HEADING CENTER
"Product Catalog"
" "
PRINT NAME NOPRINT PRODIMAGE NOPRINT
BY PRODUCTID NOPRINT
ON PRODUCTID SUBHEAD
""
" ID: <10<PRODUCTID "
" Name: <10<NAME "
" Price: <7<PRICE "
" Image: "
""
""
""
""
""
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,$
TYPE=HEADING, COLOR = RED, SIZE = 16, FONT = ARIAL, JUSTIFY=CENTER,$
TYPE=SUBHEAD,BY=PRODUCTID,IMAGE=(PRODIMAGE),SIZE=(1 1), POSITION=(+2 +.6),$
END

The StyleSheet declaration that places the image in the subheading is the following:

TYPE=SUBHEAD,BY=PRODUCTID,IMAGE=(PRODIMAGE),SIZE=(1 1), POSITION=(+2 +.6),$


Screen 2 shows the partial PDF report output.

Adjusting Image Size
The following example joins the RETAILDETAIL Master File to the RETAILIMAGE Master File and displays the same images three different ways:

JOIN PRODUCTID IN RETAILDETAIL TO PRODUCTID IN RETAILIMAGE
TABLE FILE RETAILDETAIL
PRINT PRODIMAGE AS '' PRODIMAGE AS '' PRODIMAGE AS ''
BY STYLE NOPRINT
WHERE NAME CONTAINS 'Pant' OR 'Tank'
ON STYLE UNDER-LINE
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT,COLOR=BLUE,$
TYPE=DATA,COLUMN=P1,IMAGE=(PRODIMAGE),SIZE=(1 1),$
TYPE=DATA,COLUMN=P2,IMAGE=(PRODIMAGE),SIZE=(2 3),PRESERVERATIO=OFF,$
TYPE=DATA,COLUMN=P3,IMAGE=(PRODIMAGE),SIZE=(2 3),PRESERVERATIO=ON,$
END

The size of the first displayed image is 1 inch by 1 inch:

TYPE=DATA,COLUMN=P1,IMAGE=(PRODIMAGE),SIZE=(1 1),$

The size of the second displayed image is 2 inches by 3 inches with PRESERVRATIO=OFF. This image looks distorted on the output:

TYPE=DATA,COLUMN=P2,IMAGE=(PRODIMAGE),SIZE=(2 3),PRESERVERATIO=OFF,$

The size of the third displayed image is 2 inches by 3 inches with PRESERVERATIO=ON. This image is not distorted on the output, but it is not exactly the size specified. It is the largest size that fits in the bounding box and maintains the aspect ratio:

TYPE=DATA,COLUMN=P3,IMAGE=(PRODIMAGE),SIZE=(2 3),PRESERVERATIO=ON,$


Screen 3 shows the partial PDF report output.

The Release 7.6.9 Summary of New Features documentation will include one additional example of how to include images stored in BLOB fields in summary reports because summary reports utilize the SUM verb. In addition, as explained earlier in this article, in order to insert an image from a BLOB field in a WebFOCUS PDF report, the BLOB field must be referenced in a PRINT or LIST command.
Therefore, a summary report needs to have two display commands in the request: a SUM command for the summary information and a PRINT or LIST command for displaying the image and any other detail data.