
Last week I wrote about how to use an API to retrieve a command's defaults. I encountered a problem when the string returned by the API would be blank. I did not know what was causing this. Fortunately two people added comments to the post explaining the issue: if the string returned is longer than the size of the result variable blank is returned. I want to thank both of them for letting me know what was causing this problem.
Therefore, if I increased the size of the subfield in the API's result data structure I see the results for those commands I did not see before.
This caused another problem, as I could no longer use the field in the DDS file I had defined before. The maximum size of an alphanumeric field is 32,766 bytes, which proved too small for some of the commands I tested with. My first thought was to use a (SQL) DDL table instead, but the maximum size of a character column is one byte smaller than for the DDS alphanumeric field, 32,765. As these XML strings can be extremely long I will need to insert these into a CLOB, Character Large Object, column as this can contain up to 2,147,483,647 bytes, way more than I could ever need.
My SQL DDL table looks like:
01 CREATE OR REPLACE TABLE MYLIB.CMD_PARMS 02 ( MYCMD VARCHAR(20) NOT NULL WITH DEFAULT, 03 MYCLOB CLOB(65331) NOT NULL WITH DEFAULT ) ; |
My table has two columns:
Line 2: This will contain the parameter string used in the API. This is the name of the command, and the library where it is found.
Line 3: My CLOB column, with a size of 65,331 bytes.
The changes to the program I showed last week are all in the GetData procedure. Therefore, I am only going to show that below. If you are interested in the rest of the program go to the original post.
01 dcl-proc GetData ; 02 dcl-pi *n ; 03 Parm char(20) const ; 04 end-pi ; 05 dcl-ds CMDD0100data ; 06 BytesReturned int(10) ; 07 ByteAvailable int(10) ; 08 XMLdata char(65531) ccsid(1208) ; 09 end-ds ; 10 dcl-s wkCLOB sqltype(CLOB:65531) ; 11 dcl-pr QCDRCMDD extpgm ; 12 *n char(20) const ; //Command & library 13 *n int(10) const ; //Size of returned DS 14 *n char(8) const ; //Destination format name 15 *n char(32767) options(*varsize) ; //Returned DS 16 *n char(8) const ; //Receiver format name 17 *n likeds(QUSEC) options(*varsize) ; //API error DS 18 end-pr ; 19 /include qsysinc/qrpglesrc,qusec //API error DS 20 exec sql SET OPTION COMMIT = *NONE ; 21 XMLdata = ' ' ; 22 QCDRCMDD(Parm : %size(CMDD0100data) : 'DEST0100' : CMDD0100data : 'CMDD0100' : QUSEC) ; 23 wkCLOB_data = XMLdata ; 24 wkCLOB_len = %len(%trim(XMLdata)) ; 25 exec sql INSERT INTO MYLIB.CMD_PARMS VALUES(:Parm,:wkCLOB) ; 26 return ; 27 end-proc ; |
Line 8: I have increased the size of the subfield XMLdata from 1,024 to 65,531 bytes, which was long enough for the results of every command I could think of.
Line 10: This is the first new line. This is how to define a CLOB type field in RPG.
When the program is compiled the SQL precompiler translate this statement to be a data structure:
//* DCL-S WKCLOB SQLTYPE(CLOB:65531) ; DCL-DS WKCLOB; WKCLOB_LEN UNS(10); WKCLOB_DATA CHAR(65531) CCSID(*JOBRUNMIX); END-DS WKCLOB; |
Data structure contains two subfields:
- WKCLOB_LEN: Length of the string within the CLOB variable
- WKCLOB_DATA: Will contain the data in the CLOB itself
Line 20: I always add this to my SQL RPG programs as I do not want the output to the output file to be committed.
Line 23: Here I am moving the XML of the command defaults returned from the API, XMLdata, into the CLOB.
Line 24: I need to give the length of that string too. If I do not I will receive an error when I use the CLOB.
Line 25: This is the SQL statement that performs the actual insert into the output file.
I ran this program for the following two commands:
- CHGJOB the API had returned blanks in the previous program
- WRKJOB the full results were returned in the previous program
I can use the following SQL statement to see my results:
SELECT * FROM MYLIB.CMD_PARMS |
The following is a good example I why you should not be using STRSQL.
If I use the STRSQL command I see the following:
MYCMD MYCLOB --------------- -------- CHGJOB *LIBL *POINTER WRKJOB *LIBL *POINTER |
As I cannot see what the pointer is pointing to this tells me nothing useful, apart from there are two rows/records in the table.
I always use ACS's Run SQL scripts. When I run the statement using that tool I get to see the values in the second column.
MYCMD MYCLOB --------------- -------- CHGJOB *LIBL <QcdCLCmd DTDVersion="1.0"><Cmd CmdName="CHGJOB... WRKJOB *LIBL <QcdCLCmd DTDVersion="1.0"><Cmd CmdName="WRKJOB... |
This is another good example of why you should use Run SQL scripts for all of your IBM i SQL.
Using the larger CLOB column I am getting the results from the API in my output file/table.
This article was written for IBM i 7.4, and should work for some earlier releases too.
No comments:
Post a Comment
To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.
Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.