midrange.com code scratchpad
Name:
GETMSG UDTF
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
11/05/2015 02:26:57 pm
IP:
Logged
Description:
GETMSG is a table function (UDTF) which retrieves message information from message files. See the program comments for how to create the program, the UDTF, and sample SQL for using the UDTF.

(Substitute the library of your choice for MCUTIL)
Code:
  1.      h  nomain
  2.  
  3.       //***********************************************************
  4.       //     Author: S.Landess
  5.       //       Date: 11/29/2010
  6.       //    Purpose: Display messages in message files
  7.  
  8.       //  Procedure: GETMESSAGES
  9.       //   Function: Retrieve message descriptions for all messages in a
  10.       //             given message file. Data is returned as an SQL table
  11.       //             function.
  12.       //
  13.       //  1) To create the service program, perform the following two steps:
  14.  
  15.       //      a) Create module from this source member:
  16.  
  17.       //          CRTRPGMOD MODULE(MCUTIL/PLISTMSGF)  SRCFILE(MCUTIL/QRPGLESRC)
  18.       //            SRCMBR(PLISTMSGF) REPLACE(*YES)
  19.  
  20.       //      b)  Create service program from the module:
  21.  
  22.       //          CRTSRVPGM SRVPGM(MCUTIL/PLISTMSGF)  EXPORT(*ALL)
  23.  
  24.       //  2) Use STRSQL or RUNSQLSTM to create the SQL User-defined table function:
  25.  
  26.       //       CREATE FUNCTION
  27.       //       MCUTIL/GETMSG(Library VARCHAR (10), MessageFile VARCHAR(10))
  28.       //       RETURNS TABLE ( MsgID CHAR (7),
  29.       //                       MsgSev DECIMAL (2 , 0),
  30.       //                       MsgDesc VARCHAR (500 ),
  31.       //                       MsgHelp VARCHAR (500 ))
  32.       //        LANGUAGE RPGLE
  33.       //        EXTERNAL NAME 'MCUTIL/PLISTMSGF(GETMESSAGES)'
  34.       //        PARAMETER STYLE DB2SQL
  35.       //        DISALLOW PARALLEL
  36.       //        NO SQL
  37.       //        NO FINAL CALL
  38.  
  39.       //  3) Examples of how to use the user-defined table function
  40.       //     to list messages in message file QSYS/QCPFMSG.
  41.  
  42.       //      Show messages on screen:
  43.  
  44.       //         Select *from
  45.       //         table(MCUTIL/GETMSG('QSYS','QCPFMSG'))
  46.       //          as messages
  47.       //
  48.       //      Create outfile TEMP in QTEMP library containing messages:
  49.       //
  50.       //         create table qtemp/temp as
  51.       //         (select *from
  52.       //         table(MCUTIL/GETMSG('QSYS','QCPFMSG')) as messages) with data
  53.  
  54.       //***************************************************************
  55.  
  56.       // SQL message codes
  57.      d SQL_OK          c                   '00000'
  58.      d SQL_EOF         c                   '02000'
  59.      d SQL_FILE_NOT_FOUND...
  60.      d                 c                   '00204'
  61.  
  62.      d ErrorDS         ds                  Qualified
  63.      d BytesReturned                 10i 0 Inz(%Size(ErrorDS))
  64.      d BytesAvailable                10i 0
  65.      d MsgID                          7a
  66.      d                                1a
  67.      d MsgText                      512a
  68.  
  69.      d GetMessages     pr
  70.  
  71.        // Input Parameters
  72.  
  73.      d   inLibrary                   10a   Const varying
  74.      d   inMsgF                      10a   Const varying
  75.  
  76.        // Output Parameters
  77.  
  78.      d   outMsgId                     7a
  79.      d   outMsgSev                    2  0
  80.      d   outMsgDesc                 500a   varying
  81.      d   outMsgHelp                 500a   varying
  82.  
  83.        // NULL Indicators
  84.  
  85.      d   niLibr                       5i 0
  86.      d   niMsgf                       5i 0
  87.      d   niMsgId                      5i 0
  88.      d   niMsgSev                     5i 0
  89.      d   niMsgDesc                    5i 0
  90.      d   niMsgHelp                    5i 0
  91.  
  92.        // SQL Info
  93.  
  94.      d   SQLState                     5
  95.      d   FunctName                  517a   varying
  96.      d   SpecName                   128a   varying
  97.      d   SQLMsg                      70a   varying
  98.      d   CallType                    10I 0
  99.  
  100.      p getMessages     b                   EXPORT
  101.  
  102.      d getMessages     pi
  103.  
  104.        // Input Parameters
  105.      d   inLibrary                   10a   Const varying
  106.      d   inMsgF                      10a   Const varying
  107.  
  108.        // Output Parameters
  109.      d   outMsgId                     7a
  110.      d   outMsgSev                    2  0
  111.      d   outMsgDesc                 500a   varying
  112.      d   outMsgHelp                 500a   varying
  113.  
  114.        // NULL Indicators
  115.      d   niLibr                       5i 0
  116.      d   niMsgf                       5i 0
  117.      d   niMsgId                      5i 0
  118.      d   niMsgSev                     5i 0
  119.      d   niMsgDesc                    5i 0
  120.      d   niMsgHelp                    5i 0
  121.  
  122.        // SQL Info
  123.      d   SQLState                     5
  124.      d   FunctName                  517a   varying
  125.      d   SpecName                   128a   varying
  126.      d   SQLMsg                      70a   varying
  127.      d   CallType                    10I 0
  128.  
  129.        // Prototype for the retrieve message API
  130.  
  131.      dRetrieveMsg      pr                  ExtPgm('QMHRTVM')
  132.      d MsgInfo                    32767a   Options(*varsize)
  133.      d RtnLength                     10i 0 Const
  134.      d RtnFormat                      8a   Const
  135.      d MsgID                          7a   Const
  136.      d QualMsgF                      20a   Const
  137.      d RplData                    32767a   Options(*varsize) Const
  138.      d RplDataLen                    10i 0 Const
  139.      d RplSubValue                   10a   Const
  140.      d RtnCtlChar                    10a   Const
  141.      d ErrorCode                    500a
  142.      d RtvOption                     10a   Const
  143.      d CCSIDCnvTo                    10i 0 Const Options(*Omit)
  144.      d CCSIDRplDta                   10i 0 Const Options(*Omit)
  145.  
  146.      dRTVM0300         ds                  Qualified
  147.      d BytesReturned                 10i 0
  148.      d BytesAvailable                10i 0
  149.      d MsgSev                        10i 0
  150.      d AlertIndex                    10i 0
  151.      d AlertOption                    9a
  152.      d LogPrb                         1a
  153.      d MsgID                          7a
  154.      d                                3a
  155.      d NbrSubValueFormat...
  156.      d                               10i 0
  157.      d CCSIDConvStsText...
  158.      d                               10i 0
  159.      d CCSIDConvRplDta...
  160.      d                               10i 0
  161.      d CCSIDRtnText                  10i 0
  162.      d OffsetDftRpy                  10i 0
  163.      d DftRpyRtnLen                  10i 0
  164.      d DftRpyAvlLen                  10i 0
  165.      d OffsetMsg                     10i 0
  166.      d MsgLenRtn                     10i 0
  167.      d MsgLenAvl                     10i 0
  168.      d OffsetMsgHlp                  10i 0
  169.      d MsgHlpRtnLen                  10i 0
  170.      d MsgHlpAvlLen                  10i 0
  171.      d OffsetSubVar...
  172.      d                               10i 0
  173.      d SubVarRtnLen...
  174.      d                               10i 0
  175.      d SubVarAvlLen...
  176.      d                               10i 0
  177.      d SubVarElement...
  178.      d                               10i 0
  179.      d                              500a   Varying
  180.      d DftRpy                       500a   Varying
  181.      d Msg                          500a   Varying
  182.      d MsgHlp                       500a   Varying
  183.      d SubVar                       500a   Varying
  184.  
  185.      d SQL_OK          c                   '00000'
  186.      d SQL_EOF         c                   '02000'
  187.      d SQL_FILE_NOT_FOUND...
  188.      d                 c                   '00204'
  189.  
  190.      d ErrorDS         ds                  Qualified
  191.      d BytesReturned                 10i 0 Inz(%Size(ErrorDS))
  192.      d BytesAvailable                10i 0
  193.      d MsgID                          7a
  194.      d                                1a
  195.      d MsgText                      512a
  196.  
  197.      d MsgID           s              7a
  198.      d LogPrb          s              4a
  199.      d msgRtv          s             10a   STATIC
  200.      d lastMsg         s              7a   STATIC
  201.  
  202.      d MessageFile     s             10a
  203.      d MessageFileLibrary...
  204.      d                 s             10a
  205.  
  206.       /free
  207.           MessageFile = inMsgf;
  208.           MessageFileLibrary = inlibrary;
  209.  
  210.           SQLState = SQL_OK;
  211.           If calltype = -1;
  212.             If niLibr = 1 OR niMsgf = 1;
  213.               SQLState=SQL_FILE_NOT_FOUND;
  214.               msgRtv = '';
  215.               *INLR = *ON;
  216.               Return;
  217.             EndIf;
  218.             lastMsg = '';
  219.             msgRtv = '*FIRST';
  220.           else;
  221.             msgRtv = '*NEXT';
  222.           EndIf;
  223.  
  224.           RetrieveMsg( RTVM0300
  225.                      : %Len(RTVM0300)
  226.                      : 'RTVM0300'
  227.                      : LastMsg
  228.                      : MessageFile + MessageFileLibrary
  229.                      : ' '
  230.                      : 1
  231.                      : '*NO'
  232.                      : '*NO'
  233.                      : ErrorDS
  234.                      : msgRtv
  235.                      : 0
  236.                      : 0 );
  237.  
  238.           If ErrorDS.BytesAvailable <> 0;
  239.               exsr setEOF;
  240.           Else;
  241.             If RTVM0300.MsgID <> '';
  242.               outMsgId = RTVM0300.MsgId;
  243.               outMsgSev = RTVM0300.MsgSev;
  244.               outMsgDesc = %subst( RTVM0300
  245.                                  : RTVM0300.OffsetMsg+1
  246.                                  : RTVM0300.MsgLenRtn);
  247.               outMsgHelp = %subst( RTVM0300
  248.                                  : RTVM0300.OffsetMsgHlp+1
  249.                                  : RTVM0300.MsgHlpRtnLen);
  250.               lastMsg = outMsgId;
  251.             else;
  252.               exsr setEOF;
  253.             EndIf;
  254.           EndIf;
  255.  
  256.           Return;
  257.  
  258.           BegSr setEOF;
  259.  
  260.             outMsgId = '';
  261.             outMsgSev = 0;
  262.             outMsgDesc = '';
  263.             outMsgHelp = '';
  264.             SQLState = SQL_EOF;
  265.             msgRtv = '';
  266.             *InLR = *On;
  267.             niMsgID = 1;
  268.             niMsgID = 1;
  269.             niMsgID = 1;
  270.             niMsgID = 1;
  271.             lastMsg = '';
  272.           EndSr;
  273.  
  274.       /end-free
  275.      p getMessages     e
  276.  
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css