SQL doing FTP simple PUT and GET of streamfiles

ftp_ifs.sql

-- FTP PUT and GET stream files from and to the IFS 
-- This is a wrapper arround the IBM i FTP command, making it easy to simply
-- call this stored procedure to put and get to and from a FTP server
-- I doubt it is a good idea to build huge applications this way, however it 
-- is a cool example how far you can go with SQL: Have fun :) 
-- (C) Niels Liisberg 2020
----------------------------------------------------------------------------------------------

-- Need this template file for compile
create or replace table qtemp.ftplog(line char(240)) on replace delete rows;

create or replace procedure qusrsys.ftp_put_ifs
( 
    host            varchar(256),
    userid          varchar(256),
    password        varchar(256),
    from_file       varchar(4096),
    to_dir          varchar(256)
) 

begin 
    declare msg varchar(256); 

    create or replace table qtemp.ftpcmd(cmd char(240)) on replace delete rows;
    create or replace table qtemp.ftplog(line char(240)) on replace delete rows;
    
    insert into qtemp.ftpcmd(CMD) values 
       ( userid  || ' ' || password)
      ,('namefmt 1')
      ,('locsite namefmt 1')
      ,('cd /' || to_dir)
      ,('bin')
      ,('put '  || from_file)
      with nc;
    
    call qcmdexc ('ovrdbf file(input) tofile(qtemp/ftpcmd) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ovrdbf file(output) tofile(qtemp/ftplog) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ftp rmtsys(''' || host || ''')');
   
    -- put the FTP log into joblog if errors occurs ( Note joblog is elswhere on my gist) 
    -- note: you will receive a err 500 if namefmt is not supported on remoete FTP aka not an IBM 
    set msg = (select line from qtemp.ftplog where substr(line, 1 ,3) between '501' and '599' fetch first row only);  
    if msg is not null then
        for c1 cursor for Select line from qtemp.ftplog do
            call joblog (line);
        end for;
        signal sqlstate 'NL999' set message_text = msg;
    end if;
    
end;

-- This on takes a list of file names separated with ;
-- It requires my "word" found elsewhere on my gist
create or replace procedure qusrsys.ftp_put_ifs_files
( 
    host            varchar(256),
    userid          varchar(256),
    password        varchar(256),
    from_file       varchar(32000),
    to_dir          varchar(256)
) 

begin 
    declare msg varchar(256);
    declare fileName varchar ( 256 ) ;
    declare i int ;
 
    create or replace table qtemp.ftpcmd(cmd char(240)) on replace delete rows;
    create or replace table qtemp.ftplog(line char(240)) on replace delete rows;
    
    insert into qtemp.ftpcmd(CMD) values 
       ( userid  || ' ' || password)
      ,('namefmt 1')
      ,('locsite namefmt 1')
      ,('cd /' || to_dir)
      ,('bin')
      with nc;
    
    
    set i = 0 ;
    pickFile : loop
        set i = i + 1 ;
        set fileName = word ( from_file , i , ';' ) ;
        if fileName is null or fileName <= '' then
            leave pickFile;
        end if ;
        insert into qtemp.ftpcmd ( cmd ) 
        values ( 'put ' || fileName )
        with nc ;
    end loop ;
    call qcmdexc ('ovrdbf file(input) tofile(qtemp/ftpcmd) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ovrdbf file(output) tofile(qtemp/ftplog) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ftp rmtsys(''' || host || ''')');
   
    -- put the FTP log into joblog if errors occurs ( Note joblog is elswhere on my gist) 
    -- note: you will receive a err 500 if namefmt is not supported on remoete FTP aka not an IBM 
    set msg = (select line from qtemp.ftplog where substr(line, 1 ,3) between '501' and '599' fetch first row only);  
    if msg is not null then
        for c1 cursor for Select line from qtemp.ftplog do
            call joblog (line);
        end for;
        signal sqlstate 'NL999' set message_text = msg;
    end if;
    
end;


create or replace procedure qusrsys.ftp_get_ifs
( 
    host        varchar(256),
    userid      varchar(256),
    password    varchar(256),
    from_file   varchar(4096),
    to_dir      varchar(256)
) 

begin
    declare msg varchar(256); 
    create or replace table qtemp.ftpcmd(cmd char(240)) on replace delete rows;
    create or replace table qtemp.ftplog(line char(240)) on replace delete rows;
    
    insert into qtemp.ftpcmd(CMD) values 
       ( userid  || ' ' || password)
      ,('namefmt 1')
      ,('locsite namefmt 1')
      ,('lcd ' || to_dir )
      ,('bin')
      ,('get ' || from_file || ' (replace')
      with nc;
    
    call qcmdexc ('ovrdbf file(input) tofile(qtemp/ftpcmd) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ovrdbf file(output) tofile(qtemp/ftplog) mbr(*first) ovrscope(*job)');
    call qcmdexc ('ftp rmtsys(''' || host || ''')');
    
    -- put the FTP log into joblog if errors occurs ( Note joblog is elswhere on my gist) 
    -- note: you will receive a err 500 if namefmt is not supported on remoete FTP aka not an IBM 
    set msg = (select line from qtemp.ftplog where substr(line, 1 ,3) between '501' and '599' fetch first row only);  
    if msg is not null then
        for c1 cursor for Select line from qtemp.ftplog do
            call joblog (line);
        end for;
        signal sqlstate 'NL999' set message_text = msg;
    end if;
end;


-- Use cases
call qusrsys.ftp_put_ifs  (
    host      => 'myftpserver.com',     
    userid    => 'myuserid',
    password  => 'mypassword',
    from_file => '/tmp/test.txt', --<-- Local stream files on the IFS
    to_dir    => '/ftpfolder'     --<-- remote folder on the FTP server 
); 


call qusrsys.ftp_get_ifs  (
    host      => 'myftpserver.com',     
    userid    => 'myuserid',
    password  => 'mypassword',
    from_file => '/ftpfolder/test.txt', --<-- Remote file on the FTP server 
    to_dir    => '/tmp'                 --<-- Local IFS directory on your IBM i
);