![]() |
![]() |
![]() |
Faced with the need to attach external supporting documentation to business transactions, the University of Arkansas turned to ADABAS Large Objects. The required attachments are uploaded and displayed on the web in conjunction with the entry of the transactions using Natural and the Com-plete HTTP Server. This presentation demonstrates the Personal Reimbursement Request application where this is used, and explores the techniques involved and the issues overcome. The presentation is composed of the following sections.
The first two sections require no special knowledge or background on the part of the reader, while the remaining sections presume some understanding of Natural, ADABAS, HTML, and JavaScript.
The University of Arkansas has been using Natural and ADABAS since 1986. We have been using the Com-plete HTTP Server since 2001. Most of our non-student administrative systems have been developed in-house with these tools, with the emphasis shifting from the 3270 terminal interface to a web interface – but still using Natural and Com-plete. We have named our suite of applications BASIS for Business and Administrative Strategic Information Systems. A significant portion (if not most) of our Natural code is not written, but rather is generated via custom Natural ISPF Macros. All HTML for our web applications is generated by Natural programs, and much of that code is generated by our macros. This presentation reviews our first use of ADABAS Large Objects. It relates what we have done and what has worked for us – your mileage may vary.
A personal reimbursement may be requested by any employee via webBASIS – our employee and administrator web portal for accessing BASIS. The employee completes a simple HTML form (Figure 1), selects a link to upload any attachments that substantiate their request, a browser window opens where they select one or more attachments/files (Figure 2), clicks Upload (Figure 3), Closes that window which validates their request and displays the attachments which may be selected for view (Figure 4), and Saves their request (Figure 5).
Figure 1.
Figure 2.
Figure 3.
Figure 4.
Figure 5.
The employee's request is electronically routed to an administrator in the employee's department for an initial review. This review is performed via a 3270 terminal interface (Figure 6), but the attachments are available via an HTML link (Figure 7). The administrator merely double clicks the link to open a browser window showing the associated attachments, which are available for selection (Figure 8). When an attachment is selected, a dialogue box is presented for the selection of the application to use to open the attachment (Figure 9), which will then present the attachment (Figure 10). If all information is in order, the departmental administrator will add the necessary financial cost coding (identify what monies will fund the reimbursement) and approve the request. The request is then electronically routed for review and possible approval by the cost center managers and any higher organizational level management as needed.
Figure 6.
Figure 7.
Figure 8.
Figure 9.
Figure 10.
The management level reviews may be performed via the web (Figure 11) or a 3270 terminal (Figure 12), based upon the preference of the individual. In either environment, the attachments may be viewed via a browser window (Figures 8, 9 and 10).
Figure 11.
Figure 12.
After final approval, a batch job runs nightly that will create an approved Accounts Payable Invoice. If the employee revisits his request, he sees the planned payment as show in Figure 13. Payment processing normally occurs the next business day, generating either an ACH transaction (using the employee's payroll bank account of choice) or a check to be mailed. The Reimbursement Request will then reflect this activity as shown in Figure 14.
Figure 13.
Figure 14.
My initial effort was to perform a very simple proof of concept – could I upload a file, store it on ADABAS, and present it back on the web? I started with a single LA
(long alpha) field, knowing it was limited to 16k bytes and required spanned records (for us). I knew LOBs had been added to ADABAS, but found this unknown area very intimidating (even though I knew they were added just for this purpose). In considering how to handle larger files, I came up with 4 separate options: use separate records each with an LA field containing a chunk of the data, use a series of LA fields within the same record either via an MU or PE group (or a combination of these two as Software AG does on their FUSER and FNAT System files), use LOBs, or use the Entire System Server to write and read the file to/from z/OS data sets. We asked Becky Albin which might be best. Her recommendation was overwhelmingly for LOBs. Her arguments were that they provide 2 gigabytes per field versus 16k for an LA field, they are faster to retrieve than a series of LA fields, and ADABAS provides complete data integrity versus writing to sequential files. She also volunteered to assist with any questions we had. This was the right choice. Although there were stumbling blocks, there were really only a few questions. Don Barnett, our DBA and Software AG product System Engineer, provided the following inputs for me to share regarding the use of LOBs.
I would suggest reading the section Migrating From Previous ADABAS Versions in the Planning for ADABAS 8 manual to see if you need to increase any buffer sizes. We did not increase any because we are not heavily using LOBs. An optional read is Large Object (LB) FIles and Fields in the DBA Task manual.
So far we have only one application using LOBs, but we hope to use them in other areas. Since we are always designing our systems for expansion, and trying to leverage our limited development resources, we came up with one file for various LOBs – our Attachment file. It is a record typed file, as we have used in other situations where various applications share the same need. (This is what we did for extended text – store it all in one place and provide common utilities for access and management.) We see using the same file and programs for managing attachments in various applications. To further this idea, our LOB field is within a Periodic Group along with its other attributes. In this way, the same entity, such as a reimbursement request, can have several associated attachments. The coding is a little trickier, but it only has to be written and tested once.
User WDW - List DDM ATTACHMENT - Library UPS DDM DBID 1 DDM FNR 318 VSAM Name Default Sequence Page 1 T L DB Name F Leng S D Remark - - -- -------------------------------- - ----- - - ------------------------ 1 AA APPLICATION-AREA A 2 F 1 AB ATTACHMENT-RECORD-TYPE A 3 F 1 AD ATTACHMENT-ID A 20 N P 1 AE ATTACHMENT-GROUP 2 AF ATTACHMENT-CONTENT-TYPE A 120 N 2 AG ATTACHMENT-FILE-NAME A 120 N 2 AH ATTACHMENT-LENGTH I 4 N 2 AI UPDATE-TIME T 12 N 2 AJ UPDATE-USER-ID A 8 N 2 AK L@ATTACHMENT-CONTENT I 4 2 AK ATTACHMENT-CONTENT A N LE=4096 OPTIONS=LB 1 AC TIME-STAMP B 8 F 1 S1 AA-RT-ID-KEY A 25 N S
ADABAS Large Objects are a type of ADABAS field, also commonly referred to by the acronym LOBs. The LOB field values are not stored on the same ADABAS file with which they are defined, but require a separate associated ADABAS file number. Our attachments are defined on our ATTACHMENT-ADA file number 318, with the associated LOB File Number of 319. Here is a portion of the Physical Attributes definition from Predict for this file.
Loading attributes Max recl .............. 2000 ISN reusage ........... Y User ISN .............. N LOB file number ....... 319
LOB fields are defined in Predict using the format/field type LO, and require a length which represents a segment (chunk or block) of data. You read and write the LOB field value in these segments, one after the other until the complete field value has been read or updated. We arbitrarily choose our segment size to be 4096, as seen in the following Predict definition for the Attachment-Content field. You can also see that we allow multiple LOBs per record by placing that field (and additional descriptive fields) in an ADABAS PE group.
> + Fi: ATTACHMENT-ADA L: 1 Ty L Field ID F Cs Length Occ D U DB S *- - -------------------------------- *- * -------- ----- * * -- * ** Primary Identifiers 1 APPLICATION-AREA A 2.0 AA F 1 ATTACHMENT-RECORD-TYPE A 3.0 AB F 1 TIME-STAMP B 8.0 AC F 1 ATTACHMENT-ID A 20.0 AD N ** Attachment Data PE 1 ATTACHMENT-GROUP 10 AE 2 ATTACHMENT-CONTENT-TYPE A 120.0 AF N 2 ATTACHMENT-FILE-NAME A 120.0 AG N 2 ATTACHMENT-LENGTH I 4.0 AH N 2 UPDATE-TIME T AI N 2 UPDATE-USER-ID A 8.0 AJ N 2 ATTACHMENT-CONTENT LO 4096.0 AK N ** Indexes SP 1 AA-RT-ID-KEY A 25.0 D U S1 N
In the ADABAS Field Description Table (FDT), LOBs show up as format A (alphanumeric) with a zero length and the option LB. Here is how this same file appears in SYSAOS.
Field Description Table: File 318 (ATTACHMENT-ADA) ======================== Total Fields without SDT ... 11 *************** T o p of F D T *************** Lev I Name I Leng I Form I Options I Predict Fld Name or DT / SY -----I------I-------I-------I----------------- I---------------------------- 1 I AA I 002 I A I FI I APPLICATION-AREA 1 I AB I 003 I A I FI I ATTACHMENT-RECORD-TYPE 1 I AC I 008 I B I FI I TIME-STAMP 1 I AD I 020 I A I NU I ATTACHMENT-ID 1 I AE I I I PE I ATTACHMENT-GROUP 2 I AF I 120 I A I NU I ATTACHMENT-CONTENT-TYPE 2 I AG I 120 I A I NU I ATTACHMENT-FILE-NAME 2 I AH I 004 I F I NU I ATTACHMENT-LENGTH 2 I AI I 007 I P I NU I UPDATE-TIME 2 I AJ I 008 I A I NU I UPDATE-USER-ID 2 I AK I 000 I A I LB NB NU NV I ATTACHMENT-CONTENT
Since our attachments may contain anything, we have also specified the LOB field to have No Blank Compression (option NB) and No Conversion to be performed in the record buffer (option NV). No Conversion is specified by entering an N in the CNV column while editing the field in Predict.
Ty L Field ID F Cs Length Occ D U DB S NAT-l Cnv *- - -------------------------------- *- * -------- ----- * * -- * ----- --- 2 ATTACHMENT-CONTENT LO 4096.0 AK N N
No Blank Compression is specified in Predict by selecting Additional attributes ..* Y, and then X * 12 Platform compatibilities, in order to get to the option as shown below.
Platform compatibilities High order first ..... (Y) PF option ............ (Y) No Blank Compression . Y (Y)
The following are code examples showing how the attachment upload process works, resulting in local file content being stored in ADABAS.
<a href="#" target="Upload" tabindex="6" title="Upload an attachment" onclick="window.open( 'uwoatt-u?pSI=D095A12E155FB40A&AA_RT=UWESR&Key=900251', 'Upload', 'resizable,scrollbars,width=650,height=600');return false " > Upload an Attachment supporting your request</a>
<form name='webForm' method='post' action='UWOATT-U' enctype='multipart/form-data'>
The enctype='multipart/form-data'
is required for a file upload and specifies that no characters are encoded.
<input type='file' name='fileContent' size='32' onChange='ckFileSize(this.files)'>
We wrote the JavaScript function ckFileSize
to check and ensure the file is not too large for us to load. More about that in the Issues and Obstacles section below.
HAANUPR
is the call interface to the Com-plete HTTP Server. This first call is to obtain information about the selected file (if any).
/* See what the particulars are #LENGTH := #MIME-LENGTH := 132 #HTML-NAME := 'fileContent' CALL 'HAANUPR' #CGI-STATUS 'GET-FILE-INFO' #HTML-NAME #FILE-LENGTH #FILE-NAME #LENGTH #MIME-TYPE #MIME-LENGTH
The second call reads the file content into a Natural dynamic variable, #CONTENT
.
/* INTERFACE4 is required for Dynamic variables, and the length /* (output) must be an I4. CALL INTERFACE4 'HAANUPR' #CGI-STATUS 'GET-DATA' #HTML-NAME #CONTENT #FILE-LENGTH
The base ADABAS record must exist before you can write to the Large Object. Since we allow multiple attachments in the same record (via the PE group), we may be creating a new record or a new occurrence (attachment) on an existing record. Here, any existing record has previously been found and the count of the number of current attachments set in #AG-CNT
.
/* Decide if we are creating the base record or updating it IF #AG-CNT = 0 MOVE BY NAME SK TO ATT SATT. STORE ATT #ISN := *ISN(SATT.) #AG-CNT := #AG-X := 1 ELSE #ISN := *ISN(FATT.) #AG-CNT := #AG-X := ATT.C*ATTACHMENT-GROUP + 1 END-IF
Put the record on hold, and update the attributes of the attachment.
GATT. GET ATT #ISN ATT.TIME-STAMP := *TIMESTMP ATT.ATTACHMENT-CONTENT-TYPE(#AG-X) := #MIME-TYPE ATT.ATTACHMENT-FILE-NAME (#AG-X) := #FILE-NAME ATT.ATTACHMENT-LENGTH (#AG-X) := #FILE-LENGTH ATT.UPDATE-TIME (#AG-X) := *TIMX ATT.UPDATE-USER-ID (#AG-X) := WSI.USER-ID UPDATE (GATT.)
All access to LOBs must be performed through a view whose only field is the LOB. This is reminiscent of the view required for a HISTOGRAM
, and makes sense since the LOB is actually stored on a separate ADABAS file.
1 ATT-LOB VIEW OF ATTACHMENT 2 ATTACHMENT-CONTENT (#AG-X) /* AG-X defines the occ, but the * code reference must be #1
We write the Dynamic variable content in segments to the LOB field using the Natural UPDATELOB
statement.
/* Load the LOB Segments RESET #OFFSET #LAST REPEAT WHILE #LAST LT #FILE-LENGTH ADD 4096 TO #LAST IF #LAST GT #FILE-LENGTH #LAST := #FILE-LENGTH END-IF #CNT := #LAST - #OFFSET #FIRST := #OFFSET + 1 ATT-LOB.ATTACHMENT-CONTENT(#1) := SUBSTR(#CONTENT,#FIRST,#CNT) ULOB. UPDATELOB (GATT.) IN FILE ATT-LOB STARTING AT OFFSET = #OFFSET #OFFSET := *NUMBER(ULOB.) /* ADD 4096 TO #OFFSET END-REPEAT
All updates are in 4096 byte segments including the last one (due to the LOB field/segment size in the view), even if you do not want another 4096 bytes. You then have to truncate the LOB value at the exact length desired. When finished, we commit the changes.
IF #CNT LT 4096 /* There were extra bytes added UPDATELOB (GATT.) IN FILE ATT-LOB STARTING AT OFFSET = #FILE-LENGTH TRUNCATE AT OFFSET END-IF END TRANSACTION
The first step in viewing an attachment is to produce a list of the attachments for a request (the entries in the PE group, Figure 8 above). A link is built around each file name to allow for selection and downloading. An example of the HTML for that link follows.
<a href='uwoatt-d?AA_RT=UPPRR&Key=260&Occ=1' target='_blank'> LunchAndBowlReceipt.JPG</a>
The Natural program UWOATT-D
reads the parameters passed in the url using HAANUPR
to communicate with the Com-plete HTTP Server. Those values identify the attachment to be displayed, including the occurrence from the PE group. This program also requires a separate view for the LOB field, as did the upload program. Note the L@
notation in the regular file view, which will return the length of the LOB value (something we choose to capture and store ourselves, but admittedly redundant).
1 #AG-X (I1) /* The desired Attachment-Group occ. * 1 ATT VIEW OF ATTACHMENT 2 APPLICATION-AREA 2 ATTACHMENT-RECORD-TYPE 2 ATTACHMENT-ID 2 ATTACHMENT-CONTENT-TYPE (10) 2 ATTACHMENT-FILE-NAME (10) 2 ATTACHMENT-LENGTH (10) 2 L@ATTACHMENT-CONTENT (10) * 1 ATT-LOB VIEW OF ATTACHMENT 2 ATTACHMENT-CONTENT (#AG-X)
Once the record is accessed, an HTML header is generated for the attachment and sent to the browser.
/* Output the content type & disposition COMPRESS 'Content-Type: ' ATT.ATTACHMENT-CONTENT-TYPE(#AG-X) '; charset=ISO-8859-1' H'0D15' /* CrLf /* Attachment forces the dialog box for opening/saving to disk /* File type of .xls causes it to default to Excel without /* browsing for the program to use/open 'Content-disposition: attachment; ' 'filename=' ATT.ATTACHMENT-FILE-NAME(#AG-X) H'0D150D15' INTO #HEADER LEAVING NO SPACE /* EXAMINE #HEADER FOR ' ' /* Escape coding REPLACE WITH '%20' /* for spaces in FileName GIVING LENGTH IN #LENGTH CALL 'HAANUPR' #CGI-STATUS 'PUT-TEXT' #HEADER #LENGTH
Next, the LOB is read and sent to the browser one segment (chunk) at a time.
/* Now send the LOB RESET #OFFSET READLOB ATT-LOB STARTING AT OFFSET = #OFFSET IF #OFFSET + 4096 LE ATT.L@ATTACHMENT-CONTENT(#AG-X) #LENGTH := 4096 ELSE /* Less than a full chunk returned, correct the length #LENGTH := ATT.L@ATTACHMENT-CONTENT(#AG-X) - #OFFSET END-IF /* CALL 'HAANUPR' #CGI-STATUS 'PUT-BINARY' ATT-LOB.ATTACHMENT-CONTENT(#1) #LENGTH #OFFSET := *NUMBER END-READLOB
We could not retain the dynamic variable containing the uploaded attachment across stateless interactive web page I/Os, so we chose to commit the uploaded file to ADABAS with key fields identifying it as staged for the employee that uploaded it. The Attachment file record identifiers used are Application-Area 'UW', Attachment-Record-Type 'ESR', and the employee ID used as the Attachment-ID. When the employee's Reimbursement Request is saved, we change the key structure on the Attachment record to reference the employee's completed request. The staged attachment may get abandoned, but that is ok with us. Upon initiating an upload, if we find a staged attachment record not created the same day we just delete it.
We also pass the updated Attachment Time-Stamp
back to a hidden HTML field of the Reimbursement Request page, in order to protect from simultaneous update by the employee from different browser windows (the same way we detect and disallow concurrent updates everywhere). This is done via the following JavaScript.
<body onLoad='document.webForm.fileContent.focus(); opener.document.webForm.ATTACHMENT_TIME_STAMP.value="D0960F5516B8E20A";'>
Initially I was compressing segments from the READLOB
back into a dynamic variable, and then sending that variable to the browser. I failed to account for the fact that there might be trailing blanks (hex 40s) that were being thrown away, and saw some strange results (lines appearing across images). The same was true for the very last segment. You do not want to send anything other than what was uploaded – nothing extra at all (see the anecdote below). Finally I realized I did not need to reconstruct the entire file, but could just send out chunks of the file the same way READLOB
gave them to me.
After we were in production, a user uploaded a pdf file that could not be opened in Adobe but would open in other viewers and browsers. This took quit an effort to diagnose. I found that most pdf files seemed to be terminated with "%%EOF". The ones that were causing the problem were terminated with "%%EOFCrLf" and I believe created by a later Adobe product version. It was the extra CrLf (carriage return/line feed) in combination with my extra h'40's that caused the problem.
The Reimbursement Request page shows a list of the currently staged attachments that will be associated with the request. Immediately after uploading any attachments, we needed a way to refresh that list in the main browser page. The following JavaScript is used for this purpose, and is executed when the Close button is pressed. A Validate is like pressing ENTER on a 3270 terminal, it causes the inputs to be read and validated. The program also checks for and lists any staged attachments it finds.
<a href='#' tabindex='1' title='Close window' onclick='opener.document.webForm.actionReq.value="Validate"; opener.document.webForm.submit(); window.close();'>
Use of Content-disposition: attachment
forces a dialogue box to always open where you can say what you want done with the download. Use of filename=
helps the browser pick the right application to open the attachment, as long as the file type is properly identified in the file name extension.
I failed to read the definition for this system variable closely, which resulted in an initial bug. I assumed it was the last byte read from the LOB. Here is what the documentation says.
After the call: If data was found (that is, the offset was less than the LOB field length), it receives the offset plus the segment length. This may lead to a *NUMBER value which is higher than the length of the entire LOB field.
I use it, but it seems useless since I could have just as well added 4096 to the #OFFSET
each time through the loop. But, while preparing this paper I read its definition for Before the call
.
Before the call: It specifies the byte offset in the LOB field from which the segment is to be read. Value zero (0) represents the leftmost byte in the LOB field. This does not apply for the first loop iteration. In this case the read offset is determined by the STARTING AT OFFSET
clause.
I was surprised to see its use as an input to the READLOB
, and now see that I really should not be specifying an OFFSET
at all. My code works, but I really should fix it.
We went a long time before we realized there was a problem if we tried to upload a large file. We found that with larger files we would receive a 403 error in the browser, but we didn't know why. Eventually we also found an error in the Com-plete APSLOG: COMHTP0006-SHTTPTST Content length exceeds maximum (1,024K Bytes)
. We asked for help from Software AG support, who pointed out that all content submitted by the browser to the Com-plete HTTP server had to fit within the parameter MaxContentLength
. That not only includes the file content, but everything sent by the browser including headers. We increased this parameter, and officially limit uploaded files to 1MB. (All subject to change as needed.)
The challenge was then how to keep the user from submitting a file larger than 1MB. Turning to google as I do for all of my HTML and JavaScript questions, I came up with the following technique.
function ckFileSize(file) { if (file.length == 1 & file[0].size > 1048576) { alert('File '+file[0].name+' is too large to upload.'); webForm.ulButton.disabled = true; } else { webForm.ulButton.disabled = false; } }
However, there is no actual way to limit what the browser may submit due to the various ways the data stream may be tampered with (or JavaScript fliped off and on). Not that we are sympathetic to someone doing that, but we did find that we could alter the text presented for a 403 error message issued by the Com-plete HTTP Server. We ended up itemizing in that text the three separate reasons we knew of that might create that error – excessive content being one.
This did not solve all of our file size problems though. I happened to test with a file very close to the 1MB limit, and received a Natural 1222 error, Memory required for statement execution not available
. The error was on the statement reading the file into the Natural dynamic variable. This lead to us increasing our Natural thread size in Com-plete.
You can use a phone browser to access webBASIS and enter a reimbursement request, including uploading a picture. However, on my iPhone the pictures all seem to be larger than our 1MB limit and so are not allowed. I found the Photo Shrinker app, which will reduce the size of pictures. I used it to reduce the size of the picture I took of the Lunch and Bowl receipt, which started out at 2MB. I am sure there are lots of tools and techniques for doing this. (Another one I found is CamScanner which can generate a multi-page pdf.)
This was very odd, and would only be of interest to someone using the Com-plete HTTP Server. My initial efforts, during the proof of concept phase, to download an image all failed. It was not until I happened to add a GET-DATA
(read for an HTML parameter, even though it was not needed in my test program) that it worked. I have no explanation, but know that this was very frustrating at the time.
The following presentations may be of interest since they relate to the Com-plete HTTP Server, our web application development using that server, and our use of Natural ISPF Macros to leverage and standardize our Natural program development.