Using value from the spool file as the IFS file name

I was asked how it was possible to use a string within a spool file as the file name when it was copied to the IFS as a PDF. I have written in separate posts the parts that would be needed to achieve this, and this post will pull it all together.

In my scenario I have spool files of invoices. I want to copy these to create individual PDFs in an IFS folder.

I created a simple RPG program to create a couple of "invoice" spool files. These invoices were generated using the QSYSPRT printer file, and the user data of INVOICE. They both had the same layout, the only difference being the invoice number itself. For example:

+ + + Invoice : 81719 + + +

The first "challenge" would be to extract the invoice number from the spool file. I would need to use the SPOOLED_FILE_DATA table function to list the contents of the spool file. This is the statement I would use to view the contents of the spool file I showed above:

SELECT * FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( JOB_NAME => '649959/SIMON/QPADEV000C', SPOOLED_FILE_NAME => 'QSYSPRT', SPOOLED_FILE_NUMBER => 14))

Here I have used the parameter names so you can understand what each of the table function's parameters are.

The results are returned in two columns:

ORDINAL_ POSITION SPOOLED_DATA -------- ------------ 1 + 2 + 3 + 4 Invoice : 81719 5 + 6 + 7 +

The ORDINAL_POSITION is a generated column that sequences the lines in the spool file. If I wanted the invoice number I would use a similar statement and select the row that starts with "Invoice :", as that is the unique string that identifies the invoice line.

01 SELECT SUBSTR(SPOOLED_DATA,11,10) AS "Invoice No." 02 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA('649959/SIMON/QPADEV000C','QSYSPRT',14)) 03 WHERE SUBSTR(SPOOLED_DATA,1,9) = 'Invoice :'

Line 1: The only result I want is the invoice number, that I use a substring to extract from that row.

Line 3: This is how I select the row for the invoice number.

The result is, as I expected, only the invoice number.

Invoice No. ----------- 81719

The next "challenge" is to produce a list of the invoice spool files. As I only want limited information for these spool files I can use the OUTPUT_QUEUE_ENTRIES_BASIC View, this returns a subset of the columns that is returned from the OUTPUT_QUEUE_ENTRIES View:

01 SELECT JOB_NAME,FILE_NUMBER 02 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 03 WHERE SPOOLED_FILE_NAME = 'QSYSPRT' 04 AND USER_DATA = 'INVOICE' 05 AND STATUS = 'READY'

Line 1: I only want the job name of the job that generated the invoices, and the file number of the invoice spool file. By selecting all spool files with the spool file name of QSYSPRT, the user data of INVOICE, and that are ready to be printed, I have selected all the spool files I desire.

JOB_NAME FILE_NUMBER ----------------------- ----------- 649959/SIMON/QPADEV000C 14 375214/SIMON/QPADEV000D 15

Now I can show the RPG program I wrote to put all of this together. I am going to break this into parts as it will be too awkward not to do so.

01 **free 02 ctl-opt option(*srcstmt) dftactgrp(*no) ; 03 dcl-ds Data qualified dim(*auto:1000) ; 04 JobName char(28) ; 05 FileNbr packed(6:0) ; 06 end-ds ; 07 dcl-ds ForData likeds(Data) ; 08 dcl-s Name char(10) ; 09 exec sql SET OPTION CLOSQLCSR = *ENDMOD ; 10 GetData() ; 11 MakePDF() ; 12 *inlr = *on ;

Line 1: If it not in modern RPG totally free format then it is not worth writing.

Line 2: My favorite control options, I put in all my programs.

Lines 3 – 6: This is the data structure array I will be using to contain the list of spool files. This is an auto-expanding array, you can tell by the *AUTO in the DIM keyword. The more I have used these the more I like them. I can define this one with one thousand elements, but only the array is only as big as the number of elements I use.

Line 7: I have defined another data structure to be the same as the data structure array by using the LIKEDS keyword.

line 8: This variable will be used for building the PDF file name.

Line 9: I always add the SQL set option statement into my programs to ensure that if someone else compiles the program they do not miss any of the compile options I want this program to have.

Lines 10 and 11: I always use subprocedures rather than subroutines.

Onto the first subprocedure, GetData, when I get the data for the list of spool files I need to process.

13 dcl-proc GetData ; 14 dcl-s Rows uns(5) inz(%elem(Data:*max)) ; 15 %elem(Data) = 0 ; 16 exec sql DECLARE C0 CURSOR FOR 17 SELECT JOB_NAME,FILE_NUMBER 18 FROM QSYS2.OUTPUT_QUEUE_ENTRIES_BASIC 19 WHERE SPOOLED_FILE_NAME = 'QSYSPRT' 20 AND USER_DATA = 'INVOICE' 21 AND STATUS = 'READY' 22 FOR READ ONLY ; 23 exec sql OPEN C0 ; 24 exec sql FETCH C0 FOR :Rows INTO :Data ; 25 exec sql CLOSE C0 ; 26 end-proc ;

Line 14: I want to define a variable that contains the maximum number of elements of the Data data structure. Notice that I need to have *MAX in the %ELEM built in function to get that value.

Line 15: As I am using an auto-expanding array I need to set it to empty, zero elements, before I load it here.

Lines 16 - 22: Define the SQL cursor I will be using to get the data from OUTPUT_QUEUE_ENTRIES_BASIC. As I am sure you have noticed it is identical to the SQL statement I used before.

Line 23: I open the cursor.

Line 24: I fetch the data from the cursor into my data structure array.

As this is an auto-expanding array the array will only have the number of elements that there were results from the cursor. Therefore, I do not need to retrieve the number of rows using GET DIAGNOSTICS statement.

Line 25: I close the cursor, and then leave the subprocedure.

The next subprocedures creates the PDF in the IFS.

27 dcl-proc MakePDF ; 28 dcl-s Message varchar(100) ; 29 dcl-s Path char(35) ; 30 dcl-s ReturnCode packed(1) ; 31 for-each ForData in Data ; 32 MakePDFGetName() ; 33 if (Name = '*N') ; 34 iter ; 35 endif ; 36 Path = '/home/SIMON/invoice_' + %trim(Name) + '.pdf' ; 37 exec sql SELECT SYSTOOLS.GENERATE_PDF( 38 :ForData.JobName,'QSYSPRT',:ForData.FileNbr,:Path) 39 INTO :ReturnCode 40 FROM SYSIBM.SYSDUMMY1 ; 41 if (ReturnCode = 1) ; 42 Message = 'PDF file ' + %trimr(Path) + ' was created' ; 43 else ; 44 Message = 'PDF file ' + %trimr(Path) + ' could not be created' ; 45 endif ; 46 exec sql CALL SYSTOOLS.LPRINTF(:Message) ; 47 endfor ; 48 end-proc ; 49 dcl-proc MakePDFGetName ; 50 Name = '*N' ; 51 exec sql SELECT SUBSTR(SPOOLED_DATA,11,10) INTO :Name 52 FROM TABLE(SYSTOOLS.SPOOLED_FILE_DATA( 53 :ForData.JobName,'QSYSPRT',:ForData.FileNbr)) 54 WHERE SUBSTR(SPOOLED_DATA,1,9) = 'Invoice :' ; 55 end-proc ;

It is a lot of code, but what it does is fairly straight forward.

Line 31: The FOR-EACH operation code reads every element of the array. This is ideal for an auto-expanding array as it can only read the number of elements that were created by the SQL fetch. I have to read the data structure array, Data into the data structure ForData, which I then use the data from.

Line 32: I call the subprocedure to get the invoice number from the spool file. I will explain more below.

Line 33 – 35: If the invoice number could not be retrieved from the spool file then the ITER will mean that the next element from Data is read.

Line 36: I make the path name from the variable returned by the MakePDFGetName subprocedure.

Lines 37 – 40: The SPOOLED_FILE_DATA scalar function is what generates the PDF. I pass the job name, spool file name, file number, and the path name to it. The scalar function returns a return code, into the variable ReturnCode, which allows me to know if it was successful or not.

Lines 41 – 45: If the return code is '1' then it was successful. If it is '-1' then the scalar function failed. Depending on what happened I place a message into the variable Message.

Line 46: I use the LPRINTF SQL function to write the contents of the Message variable to the job log.

Line 47: And the end of the For each group is reached, and the logic loops back up to the start of the For each.

The MakePDFGetName subprocedure retrieves the invoice number from the spool file with the statement I showed earlier in this post.

Line 50: I put this value into the variable Name. If the following SQL statement does not find an invoice number row, the value is unchanged and returned to the calling subprocedure.

Lines 51 – 54: This is the same statement that I used before to get the invoice number from the spool file.

When the program finished I checked the job log for the messages from the program to know whether the PDF were generated.

01 SELECT MESSAGE_TEXT 02 FROM TABLE(QSYS2.JOBLOG_INFO('649959/SIMON/QPADEV000C')) 03 WHERE SUBSTR(MESSAGE_TEXT,1,3) = 'PDF' 04 ORDER BY ORDINAL_POSITION DESC

To view the job log I use the JOBLOG_INFO table function.

Line 1: I am only interested in the message text column as it contains the message that LPRINTF wrote to it.

Line 2: I am using Run SQL Scripts so I need to pass the job name of the job that ran this program.

Line 3: I am only interested in the rows where the message text starts with PDF as those are the lines I wrote to the job log.

Line 4: The results are sorted in descending order of the ordinal position so the most recent results comes first.

My results are:

MESSAGE_TEXT -------------------------------------------------- PDF file /home/SIMON/invoice_81902.pdf was created PDF file /home/SIMON/invoice_81719.pdf was created

I can use the IFS_OBJECT_STATISTICS table function to check if the PDF are in the folder I wanted them in.

SELECT PATH_NAME FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/SIMON','NO','*STMF'))

I have passed three parameters to the table function:

1. The folder's path
2. Do I wish to include subdirectories?
3. Type of objects to return

The results show that both invoices have been copied.

PATH_NAME ----------------------------- /home/SIMON/invoice_81719.pdf /home/SIMON/invoice_81902.pdf

This is a quick example of how I would use a value from the spool file as part of the PDF name.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.