I received a message from Gary Patterson in response to a comment I made on my post Adding Column Headings to a file in the IFS. I said that "I did try the SQL SYSCOLUMNS at first. The problem was with the way SQL interprets the Column Headings. Rather than break them out into 3 fields, it interprets them, as 1 very long field. The very long field was just too unwieldy to use."
In his message Gary explained "SYSCOLUMNS COLUMN_HEADING is a 60 byte column containing up to three rows of column heading information. Each row is exactly 20 bytes long and is populated by either the DDS COLHDG keyword, or the SQL LABEL ON DDL statement." And included an example of the SQL code to "build a column heading row dynamically".
The table/file SYSCOLUMNS in library QSYS2 contains a list of all of the fields/columns in all the files/tables on your IBM i, and their attributes. His example demonstrated how to use the use the 'Trim right', RTRIM and 'Substring', SUBSTR, functions. I have taken his example and made it into the program shown below:
01 dcl-s Library char(10) inz('MYLIB') ;
02 dcl-s File char(10) inz('TESTFILE') ;
03 dcl-s HeadingRow char(5000) ;
04 dcl-s ColumnHeading char(62) ;
05 exec sql DECLARE cols CURSOR FOR
SELECT RTRIM(SUBSTR(s.column_heading,1,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,21,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,41,20))
AS column_heading
FROM syscolumns s
WHERE sys_dname = :Library
AND sys_tname = :File
ORDER BY colno
FOR READ ONLY ;
06 exec sql OPEN cols ;
07 dow (1 = 1) ;
08 exec sql FETCH cols INTO :ColumnHeading ;
09 if (SQLCOD <> 0) ;
10 dsply ('SQLCOD = ' + %char(SQLCOD)) ;
11 leave ;
12 endif ;
|
I found the SELECT part of the SQL DECLARE statement, line 5, really interesting as it substrings the COLUMN_HEADING into 3 parts, removes any blanks from the end of the string, and then puts the 3 parts together using the double pipe (||) symbols. The WHERE defines that I want to extract the data only for the file, and library, I want. I need to order the extract by the file’s column number, COLNO, i.e. the order in which the columns appear in the file. As I am opening SYSCOLUMNS as input only I use 'FOR READ ONLY'.
Having declared the cursor I need to open the file, line 6.
As files can contain multiple fields I have the FETCH, the equivalent of read, in a do loop, line 7-17.
The FETCH is similar to a read, when I fetch the cursor the columns/fields I have defined in the SELECT are moved to the variable(s) that follow the INTO, see line 8. In this example I have only defined 1 field, and its value will be moved to the variable ColumnHeading.
By checking the SQL code, SQLCOD, on line 9 I can determine if the FETCH executed without error. If it executed without an error than the code is zero. If the code is not zero then an error was encountered. As a rule you can determine what the error means by using the last 4 numbers of the SQL code, combining it with 'SQL' to give SQL9999. You can then "look" this up in the SQL message file, QSQLMSG. For example if the error I get is a SQL code of -300, I would "look" up the description of the message SQL0300 using the DSPMSGD command:
DSPMSGD RANGE(SQL0332) MSGF(QSQLMSG) |
Alas, when I execute the code above I encounter a SQL code of -332, CCSID mismatch error, when I FETCH the data. This is probably caused by the way the IBM i I am using is configured. What I have to do is to convert the CCSID of the COLUMN_HEADING to one I know I can use without error. As I am in the USA the best CCSID is 37, USA English. I use the SQL CAST specification to convert the data in the COLUMN_HEADING field to the CCSID I want, and my DECLARE statement becomes (additional code in red):
05 exec sql DECLARE cols CURSOR FOR
SELECT CAST(
RTRIM(SUBSTR(s.column_heading,1,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,21,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,41,20))
as char(62) ccsid 37)
AS column_heading
FROM syscolumns s
WHERE sys_dname = :Library
AND sys_tname = :File
ORDER BY colno
FOR READ ONLY ;
|
You may not experience a CCSID error, if so you can leave the DECLARE unchanged. If you do and you are not in the USA you will probably have to find the CCSID that works best for your language and alphabet.
The rest of the program is concerned with adding a comma ( , ) if this is not the first column added to the HeadingRow variable, lines 13-15. Add the current ColumnHeading to the HeadingRow, line 16. And after I have left the do loop, no more records for this file in SYSCOLUMNS, I close the SQL cursor, line 18.
13 if (HeadingRow <> ' ') ;
14 HeadingRow = %trimr(HeadingRow) + ',' ;
15 endif ;
16 HeadingRow = %trimr(HeadingRow) + '"' +
%trimr(Column Heading) + '"' ;
17 enddo ;
18 exec sql CLOSE cols ;
19 *inlr = *on ;
|
The result of this program is give me a HeadingRow containing the column heading of all the fields/columns like this:
"First test field","Second test field","Third test field"... |
With the addition of the CAST to the SQL DECLARE the entire program looks like:
01 dcl-s Library char(10) inz('MYLIB') ;
02 dcl-s File char(10) inz('TESTFILE') ;
03 dcl-s HeadingRow char(5000) ;
04 dcl-s ColumnHeading char(62) ;
05 exec sql DECLARE cols CURSOR FOR
SELECT CAST(
RTRIM(SUBSTR(s.column_heading,1,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,21,20)) || ' ' ||
RTRIM(SUBSTR(s.column_heading,41,20))
as char(60) ccsid 37)
AS column_heading
FROM syscolumns s
WHERE sys_dname = :Library
AND sys_tname = :File
ORDER BY colno
FOR READ ONLY ;
06 exec sql OPEN cols ;
07 dow (1 = 1) ;
08 exec sql FETCH cols INTO :ColumnHeading ;
09 if (SQLCOD <> 0) ;
10 dsply ('SQLCOD = ' + %char(SQLCOD)) ;
11 leave ;
12 endif ;
13 if (HeadingRow <> ' ') ;
14 HeadingRow = %trimr(HeadingRow) + ',' ;
15 endif ;
16 HeadingRow = %trimr(HeadingRow) + '"' +
%trimr(Column Heading) + '"' ;
17 enddo ;
18 exec sql CLOSE cols ;
19 *inlr = *on ;
|
I would like to thank Gary for his example. If you have another way to do the same please use the Contact form on the right to send it to me.
You can learn more about these from the IBM web site:
This article was written for IBM i 7.1.
Very nice. I have had a utility for a long time that handles the CPYTOIMPF and FTP script creation, but the headings were always a bugbear. I had kludged it by writing a heading row with qshell, but it had to be custom written every new program. This looks much more promising.
ReplyDeleteIt probably didn't exist when you wrote this post, but nowadays the removal of the extraneous spaces can be done thus: regexp_replace(s.column_heading,'\s+',' ')
ReplyDelete