Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@daframe daframe commented Dec 5, 2019

Same here.. :)
Line #10

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

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Dec 7, 2019

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

@NielsLiisberg

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Dec 9, 2019

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

This comment has been minimized.

Copy link

@chrjorgensen chrjorgensen commented Jan 28, 2020

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

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Jan 28, 2020

It's back - sorry !!

@chrjorgensen

This comment has been minimized.

Copy link

@chrjorgensen chrjorgensen commented Jan 29, 2020

Thanks! :-)

@emidioporziella

This comment has been minimized.

Copy link

@emidioporziella emidioporziella commented Jun 23, 2020

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

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Jun 25, 2020

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

This comment has been minimized.

Copy link

@emidioporziella emidioporziella commented Jun 25, 2020

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

@NielsLiisberg

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Jun 25, 2020

Great !! My pleasure :)

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