SQL runs bash script and returns the stdout as a table

bash-pipe-stdout.sql

-- Run a bash command or script and returns the stdout as a table.
-- It assumes bash is installed by YUM so 
-- it will be in the default location /QOpenSys/pkgs/bin/bash
-- You can use this aproach to other shells like sh, qsh setc.
-- You need to have the "ifs_write" procedure found on my gist
-- you need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
-- I doubt this method is a good idea to build huge applications, 
-- however it is a cool example how far you can go with SQL:
----------------------------------------------------------------------------------------------
call qusrsys.ifs_write('/tmp/openfile.c' , '
#include <sys/stat.h>
#include <fcntl.h>
#include <string.h>
#include <QP2SHELL.h> 
{
    char cmd [32000];
    memcpy ( cmd , BASH_TABLE.COMMAND.DAT   ,  BASH_TABLE.COMMAND.LEN);
    strcpy ( cmd + BASH_TABLE.COMMAND.LEN   , ">"); 
    strcpy ( cmd + BASH_TABLE.COMMAND.LEN+1 , MAIN.TEMPNAME);  
    QP2SHELL ("/QOpenSys/pkgs/bin/bash" , "-c" , cmd);
    MAIN.STMF =  open (MAIN.TEMPNAME , O_RDONLY );
}
');
call qusrsys.ifs_write('/tmp/readfile.c' , '
    MAIN.BUF.LEN =  read (MAIN.STMF, MAIN.BUF.DAT, sizeof(MAIN.BUF.DAT)); 
');
call qusrsys.ifs_write('/tmp/closefile.c' , '
    close  (MAIN.STMF);
    unlink (MAIN.TEMPNAME); 
');

create or replace function qusrsys.bash_table (
    command varchar(32000)
) 
returns table ( stdout  varchar(32000) ccsid 1208 )
set option output=*print, commit=*none, dbgview = *source --list
begin

  main:begin
      declare stmf int ;
      declare endpos int;
      declare buf varchar(4096) ccsid 1208 default '';
      declare rest varchar(4096) ccsid 1208 default'';
      declare tempname char(64);
      set  tempname =  '/tmp/f' || now() || '.txt'  || x'00';
  
      include '/tmp/openfile.c';
      include '/tmp/readfile.c';
      while length(buf) > 0 do
          repeat
             set endpos = position(x'25' , buf);
             if endpos > 0 then
                pipe (rest || substring(buf, 1 , endpos));
                set buf = substring(buf, endpos + 1);
                set rest = '';
             else
                set rest = rest || buf;
                set buf  = '';
             end if;
          until endpos = 0          
          end repeat;
          include '/tmp/readfile.c';
      end while;
      if length(rest) > 0 then
        pipe (rest);
      end if;
      include '/tmp/closefile.c';
  end;
  
  return;
end;

-- Usecases:
---------------------------------------------

-- List content of the IFS users homedirectory
select * from table (qusrsys.bash_table ('cd /home;ls')) a; 

-- Read a text file
select * from table (qusrsys.bash_table ('cat /qopensys/pkgs/LICENSE.md')) a; 
                           
-- List all files for all users in their home directory
select 
    AUTHORIZATION_NAME, 
    HOME_DIRECTORY  , 
    BASH.* 
from qsys2.user_info, 
lateral ( 
    Select stdout as "Files in home dir" 
    from table (
        bash_table ('cd ' || HOME_DIRECTORY || ';ls')
    ) x  
) bash; 

Cool stuff my friend :)

For anyone trying this out, depending on your connection config, you might need to fully qualify your calls?
ie.: ifs_write => qusrsys.ifs_write and bash => qusrsys.bash

I see you unlink in closefile.c, which is nice, but I have an idea that'll I get back to once I'm done with work :)

Correct - fully qualify is the best way to go.

Thanx @jenschurchill for the feedback - Now fixed !! and also added a cool use case.

_Cool stuff my friend :)
I see you unlink in closefile.c, which is nice, but I have an idea that'll I get back to once I'm done with work :) _

I guess your idea Jens is to simply reopen stdout - I have tried that but could not make it work - maybe the border between ILE and PASE is the disturbing part .. I don't know. Any input is welcome

Sneaky, I like that use-case! - I'll get back to you :)