NielsLiisberg

-- Write a BLOB to IFS 
-- Note1: This also works for save files
-- Note2: This also compiles on vanilla systems without QSYSINCL installed
-- Note3: I am using library QUSRSYS. I suggest you put it into your tool library
-- 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
----------------------------------------------------------------------------------------------
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');
delete from qtemp.c;

NielsLiisberg

-- Get a stream file via the HTTP/HTTPS protocol and store it on the IFS.
-- This was inspired by the "wget" AIX command, that is not always available
-- Note1: This also works for save files
-- Note2: This also compiles on vanilla systems without QSYSINCL installed
-- Note3: I am using library QUSRSYS. I suggest you put it into your tool library
-- 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
----------------------------------------------------------------------------------------------
call qcmdexc ('crtsrcpf FILE(QTEMP/C) MBR(C)');

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

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

-- Let SQL create a command and CLLE program to run PASE shell
-- scripts or commands - much like QSH but for PASE. Ex. like this:
--
--    SH SCRIPT('ls')
--
-- Simply paste 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
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020

NielsLiisberg

-- SQL procedure to get a quick system catalog of files, tables and views.
-- I use this from ACS all the time to get a catalog overview.
--
-- Simply paste 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
-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2020
-------------------------------------------------------------------------------------
create or replace procedure  qusrsys.syscat  (

NielsLiisberg

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

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

-- Any_sort solves the problem where you have an alphanumeric column 
-- that contains mixed numeric and alphanumeric data and you need to 
-- order both alphanumeric AND numeric at the same time 
-- 
-- Any sort returns a varchar that left adjust alpha and right adjust numeric values
--
-- Simply copy this gist and paste it into ACS SQL prompt. Select "run all" to build this feature.
--
-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library
--

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