Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Produce CSV file
-- 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
(
in sql_statement clob,
in output_file varchar(256)
)
language sql
modifies SQL data
set option dbgview = *source , output=*print , commit=*none, datfmt=*iso
begin atomic
declare sqlcode int default 0;
declare cols int;
declare colLen int;
declare colPrec int;
declare colScale int;
declare colNo int;
declare colType int;
declare colName varchar(256);
declare colLabel varchar(256);
declare colValue varchar(32000);
declare CRLF varchar(2) default x'0d25';
declare newline varchar (2) default '';
declare comma varchar (1) default '';
declare c1 cursor for stmt;
allocate descriptor local 'original' with max 256 ;
allocate descriptor local 'modified' with max 256 ;
Prepare stmt from sql_statement ;
describe stmt using sql descriptor local 'original';
describe stmt using sql descriptor local 'modified';
-- First run the query and get our meta data
Open c1;
get descriptor 'original' cols = count;
-- Produce an empty stram in 1208 - UTF-8
call qusrsys.ifs_write(output_file, '');
-- Cast data to varchar, and build column heading
set comma = '';
set colNo = 1;
while colNo <= cols do
set descriptor 'modified' value colNo
LENGTH = 32000,
TYPE = 12;
get descriptor 'original' value colNo
colLen = LENGTH,
colScale = SCALE,
colPrec = PRECISION,
colName = NAME,
colLabel = DB2_LABEL;
call qusrsys.ifs_append (output_file, comma || '"' || REGEXP_REPLACE(colLabel,'( ){2,}', ' ') || '"');
set comma = ';';
set colNo = colNo + 1;
end while;
-- Now produce the rows
fetch c1 into sql descriptor 'modified';
while sqlcode = 0 do
set comma = '';
set colNo = 1;
call qusrsys.ifs_append (output_file, CRLF);
while colNo <= cols do
get descriptor 'original' value colNo
colLen = LENGTH,
colScale = SCALE,
colPrec = PRECISION,
colName = NAME,
colLabel = DB2_LABEL,
colType = TYPE;
get descriptor 'modified' value colNo
colValue = DATA;
if colType in (1, 12) then -- char or varchar
call qusrsys.ifs_append (output_file, comma || '"' || replace(trim(colValue), '"' , '""') || '"');
elseif colType in (2 , 3) then -- decimal
call qusrsys.ifs_append (output_file, comma || replace(trim(colValue),'.',','));
else
call qusrsys.ifs_append (output_file, comma || trim(colValue));
end if;
set comma = ';';
set colNo = colNo + 1;
end while;
fetch c1 into sql descriptor 'modified';
end while;
close c1;
deallocate descriptor local 'modified';
deallocate descriptor local 'original';
end;
-- Usecase:
call qusrsys.sql_to_csv(
sql_statement => 'SELECT * FROM QIWS.QCUSTCDT',
output_file => '/tmp/test.csv'
);
@emidioporziella

This comment has been minimized.

Copy link

@emidioporziella emidioporziella commented Jun 26, 2020

Hi,
I've tried to use a call to a procedure into my SQLRPGLE, but I've noticed that the procedure doesn't read the list of user library.
Example:
sqlstring = 'SELECT * FROM table1';
exec sql call qusrsys.sql_to_csv(:sqlstring,'/tmp/file.csv');
error SQL204 file not found

sqlstring = 'SELECT * FROM mylib.table1';
exec sql call qusrsys.sql_to_csv(:sqlstring,'/tmp/file.csv');
no error and file was created

mylib is in the list of library

@NielsLiisberg

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Jun 29, 2020

Hello @emidioporziella - Thank you for your feedback.

My guess is you have compiled you RPG with "NAMING=*SQL" It has this side effect that librarylist is not used. If you change it to NAMING=*SYS" it will use the library list and you a still allowed to use dots in schema and table separation. If you have NAMING=*SQL the I can confirm I have the same issue, and it will be a question for IBM. I think it works as designed.

An other solution could be: Add the following line
*set path = LIBL;
Just before the the open statement.

@emidioporziella

This comment has been minimized.

Copy link

@emidioporziella emidioporziella commented Jun 29, 2020

@NielsLiisberg

This comment has been minimized.

Copy link
Owner Author

@NielsLiisberg NielsLiisberg commented Jun 29, 2020

OK then try this: On CRTSQLRPGI specify SQLPATH(*LIBL) ..

You idea of using a set path in your RPG will not work. if you will change path you need to change my procedure to at the set path in line 40.

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