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.
Thank you Simon for this neat example and illustration, as usual in your blog. As an information that might be useful I noticed that this API doesn't return anything if the xml data exceed the size reserved to it, even the QUSEC doesn't return anything. Then if you receive blank and no error, increase the XMLdata 32767 size in the DS.
ReplyDeleteI had not noticed that. Thank you for sharing.
Delete