SQL Produce CSV file

sql_to_csv.sql

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

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

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.

Hi NielsLiisberg, I've tried to compile as you suggested. *exec sqlset option commit = *none, closqlcsr = *endmod, datfmt = *iso, naming = *sys;* *But not working.* *I also tried to insert the following specification * EXEC SQL set path = *LIBL; Not working. The only way to make it work is to insert a following specification: EXEC SQL set schema = xxxxx; When it doesn't work the message is the same:SQL0204 FILE NOT FOUND. Il giorno lun 29 giu 2020 alle ore 13:18 Niels Liisberg < notifications@github.com> ha scritto:

…

***@***.**** commented on this gist. ------------------------------ Hello @emidioporziella <https://github.com/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. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://gist.github.com/3bc04d45c39a816c4b52760d6f861c8b#gistcomment-3358027>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AQBSJ5UPAIVYN4QVKJXDPLLRZBZ7HANCNFSM4OJLNEEA> .

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.

Is it possible to set the stored procedure in order to create a CSV with string not delimited by " ? (STRDLM = *NONE)

I suppose you could change line 63 and 87 to not include the double quote or get if from a default parameter, but I am not 100% what you are asking for.

I suppose you could change line 63 and 87 to not include the double quote or get if from a default parameter, but I am not 100% what you are asking for.

Perfect, it works.
It was exactly what I was referring to (and it wasn't effectively so difficult :/ )

Thanks for your great work: easy but genius!

My pleasure :)

…

On Mon, May 9, 2022 at 5:45 PM Nope7 ***@***.***> wrote: ***@***.**** commented on this gist. ------------------------------ I suppose you could change line 63 and 87 to not include the double quote or get if from a default parameter, but I am not 100% what you are asking for. Perfect, it works. It was exactly what I was referring to (and it wasn't effectively so difficult :/ ) Thanks for your great work: easy but genius! — Reply to this email directly, view it on GitHub <https://gist.github.com/3bc04d45c39a816c4b52760d6f861c8b#gistcomment-4161001>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAVIPHQOQJ5ZO4HI7VRICELVJEXKPANCNFSM4OJLNEEA> . You are receiving this because you were mentioned.Message ID: ***@***.***>