Skip to content

Instantly share code, notes, and snippets.

@NielsLiisberg
Last active July 15, 2021 12:54
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save NielsLiisberg/9ca47191bae9f301c0f00310201aaab6 to your computer and use it in GitHub Desktop.
Save NielsLiisberg/9ca47191bae9f301c0f00310201aaab6 to your computer and use it in GitHub Desktop.
SQL append to IFS file
----------------------------------------------------------------------------------------------
-- 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
--
-- 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 ('ADDLIBLE LIB(QSYSINC)');
call qcmdexc ('CRTSRCPF FILE(QTEMP/C) MBR(C) CCSID(37)');
delete from qtemp.c;
insert into qtemp.c (srcdta) values
('{'),
('#include <sys/types.h>'),
('#include <sys/stat.h>'),
('#include <fcntl.h>'),
('int f,l,o;'),
('mode_t mode = S_IRUSR | S_IWUSR | S_IXUSR;'),
('o = O_WRONLY | O_CREAT | O_APPEND | O_CCSID ;'),
('IFS_APPEND.NAME.DAT[IFS_APPEND.NAME.LEN] =0;'),
('f = open(IFS_APPEND.NAME.DAT, o, mode, 1208);'),
('l = write(f, IFS_APPEND.BUF.DAT,IFS_APPEND.BUF.LEN);'),
('close (f);'),
('}')
;
create or replace procedure qusrsys.ifs_append(name varchar(256), buf varchar(32700) ccsid 1208)
external action
modifies sql data
set option output=*print, commit=*none, dbgview = *source
begin
if buf is not null then
include qtemp/c(c);
end if;
end;
-- Test it like this - Yes it supports national characters
call qusrsys.ifs_append('/tmp/test2.txt' , 'Hello ÆØÅ こんにちは');
@daframe
Copy link

daframe commented Dec 5, 2019

Same here.. :)
Line #10

('#includ<sys/stat.h>'),

@NielsLiisberg
Copy link
Author

Sorry! My fault- you need QSYSINC library installed ... I can make a version without this requirement..

@NielsLiisberg
Copy link
Author

The early version did not have that dependency - so please use this if you don't have QSYSINC installed:

https://gist.github.com/NielsLiisberg/9ca47191bae9f301c0f00310201aaab6/eb5ff1c1364a82117e0937d580489730720a3286

@chrjorgensen
Copy link

Seems like the ifs_append procedure has been overwritten by the corresponding ifs_write procedure!

Any chance to get the correct version of ifs_append back?

@NielsLiisberg
Copy link
Author

It's back - sorry !!

@chrjorgensen
Copy link

Thanks! :-)

@emidioporziella
Copy link

Hi,
create procedure error:
ID MES SEV RECORD TESTO
SQL0007 20 95 Posizione 1 Carattere '{' (HEX X'44') non valido
nell'istruzione SQL.
SQL0007 20 98 Posizione 20 Carattere '[' (HEX X'90') non valido
nell'istruzione SQL.
SQL0007 20 98 Posizione 40 Carattere ']' (HEX X'51') non valido
nell'istruzione SQL.
SQL0007 20 102 Posizione 1 Carattere '}' (HEX X'54') non valido
nell'istruzione SQL.

@NielsLiisberg
Copy link
Author

Hello @emidioporziella, I think you need to press the "raw" button in the gist before you copy the source into your ACS. If not maybe you have to set the ccsid on the source file in line 2. So try this updated version.

@emidioporziella
Copy link

Hi Niels,
I've solved using ACS.
Thank's a lot.

@NielsLiisberg
Copy link
Author

Great !! My pleasure :)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment