NielsLiisberg

-- Simple way to write a stream file to the IFS, by using C runtime as inline code
-- This will produce stream files UTF-8 encoded 
-- 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:
----------------------------------------------------------------------------------------------
call qcmdexc ('addlible qsysinc');
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');
delete  from qtemp.c;
insert into qtemp.c (srcdta) values 
    ('{'),

NielsLiisberg

----------------------------------------------------------------------------------------------
-- Easy way to append text to a stream file in the IFS, by using C runtime as inline code
--
-- Simply paste the raw version of this gist into ACS SQL and select "run all" 
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--
-- You need library QSYSINC installed:
-- https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/apiref/conQSYSINC.htm
--

NielsLiisberg

begin
    declare cmd varchar(4096);
    declare comma varchar(1) default ''; 
    call qusrsys.ifs_write('/tmp/test.json'  , '[');  
    for vl as c1 cursor for select cusnum , lstnam from qiws.qcustcdt a do
        call qusrsys.ifs_append('/tmp/test.json'  , 
            comma concat  
            json_object (
                'customerNumber' : CUSNUM,
                'name' : LSTNAM

NielsLiisberg

------------------------------------------------------- 
-- Implements LUHN modulus 10 for credit card checksum
-- Niels Liisberg 
------------------------------------------------------- 
create or replace function qusrsys.modulus10 (inString varchar(32))                                       
    returns int   
    language sql                                                                           
    reads sql data                                                                          
    returns null on null input                                                               
    no external action                                                                      

NielsLiisberg

-- install bash
cl: qsh cmd('yum install bash');

-- If you don't have a profil yet, then add the open source path to your default path  
cl: qsh cmd('touch $HOME/.profile');
cl: qsh cmd('setccsid 1252 $HOME/.profile');
cl: qsh cmd('echo ''PATH=/QOpenSys/pkgs/bin:$PATH'' >> $HOME/.profile');
cl: qsh cmd('echo ''PS1="\\h-\\$PWD:\\n"'' >> $HOME/.profile');

-- Finally set bash as the default shell

NielsLiisberg

-- This will produce a CSV file on the IFS using ifs_append and ifs_write 
-- also found here on my "gist"
-- Simply give it a select statement or a procedure call
-- and it will produce a CSV file in the IFS path of your choice 
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
--------------------------------------------------------------------------------------------------
create or replace procedure qusrsys.sql_to_csv
(

NielsLiisberg

--
-- Writes UTF-8 CLOB to IFS 
-- Simple way to write a stream file to the IFS, by using C runtime as inline code
--
-- 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.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

NielsLiisberg

-- Run a bash command or script.
-- 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.
-- I use qusrsys here however I suggest that you use your own toolibrary
-- 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:
----------------------------------------------------------------------------------------------

NielsLiisberg

-- 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:
----------------------------------------------------------------------------------------------

NielsLiisberg

-- This is a tool to export all source physical filemembers on IBM i
-- to a git repo to keep track of changes over time
-- This requires the BASH stored procedure found on my gist
-------------------------------------------------------------------

-- Change the names to your names/repos/mail addr and Run this once:

call qusrsys.bash ('
    mkdir /passivegit;
    cd /passivegit;