Format messages ID and messages data into text

format_message.sql

-- Format a message from messag ID and messages data 
-- This also showcase how to integrate the C code directly into your UDTF
--
-- Simply paste this gist into ACS SQL and step through the example.
--
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2021
--
-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
----------------------------------------------------------------------------------------------
call qsys2.ifs_write(
    path_name => '/tmp/main.c' , 
    file_ccsid => 1208, 
    overwrite => 'REPLACE',
    line =>'
{
    #include "QSYSINC/H/QMHRTVM"
    QMHRTVM ( 
          MAIN.OUTPUT    
        , MAIN.LEN                
        , "RTVM0100"
        , MAIN.MSGID                 
        , MAIN.MSGF         
        , MAIN.MSGDATA
        , MAIN.MSGDATA_LEN
        , "*YES      "
        , "*NO       "
        , MAIN.ERR 
    );  
} 
');

create or replace function qusrsys.format_message (
    message_id_and_data varchar(1024) default null,
    message_id char(7) default null,
    message_file_And_Lib char(20) default 'QCPFMSG   QSYS      ',
    message_data varchar(1024) default ''
) 
returns varchar (1024)
set option output=*print, commit=*none, dbgview = *source --list
main:begin
    
    declare output char(1024) for bit data;
    declare len    int; 
    declare msgid  char(7);    
    declare msgf   char(20);    
    declare msgdata char(1024);
    declare msgdata_len int; 
    declare err    char(8) for bit data;
    
    -- If the data from the API feedback structure is passerd as parameter
    if message_id_and_data is not null then 
        set  message_id   = substr( message_id_and_data , 1, 7);
        set  message_data = substr( message_id_and_data , 9);
    end if;
        
    set main.output  = '';        
    set main.msgid   = message_id;        
    set main.msgf    = message_file_and_lib;        
    set main.msgdata = message_data;
    set main.msgdata_len = length(message_data);
    set main.len     = length(output);
    set MAIN.err     = x'0000000000000000'; 
    include '/tmp/main.c';
    
    return substring( OUTPUT , 25 , interpret( substr(OUTPUT , 1,  4) as int));
end;

-- unit test 
values ( 
   qusrsys.format_message  ( 
       message_id_and_data => 'CPF2204 JOHN'
   )                                                                                                                    
);