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

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: ***@***.***>