Replace source date member fetching with new procedure

[worksofliam]

This will solve member CCSID issues with SQL mode once and for all.

@chrjorgensen and I have been going on for months about replacing our logic to fetch member aliases with our runner, with new procedure that either returns the rows or writes to a file. Sadly, returning the rows from the procedure doesn't solve our ever ending CCSID issues with source members.

For those who are reading this and wondering why we don't use CPYTOSTMF or CPYTOIMPF: neither of those commands return the source dates, which is what we need.

So, Christian wrote this procedure, which generates the alias into QTEMP and then writes the file to the IFS. The implementation is good, but inside of Code for IBM i we already create the aliases in the temporary library so we can re-use them when saving members again.

call NCCJ.Get_Member_Content( LIB => 'nccj', FIL => 'qclsrc', MBR => 'burncpu', STMF => 'abc.txt',  INC_SEQ => 'Y', INC_DATE => 'Y' )

Source

-- Get member content, optionally write to streamfile and include seq and date

set schema NCCJ;

create or replace procedure Get_Member_Content(
  lib varchar( 10 )
, fil varchar( 10 )
, mbr varchar( 10 )
, stmf varchar( 1024 ) default ''
, inc_seq char( 1 ) default 'N'
, inc_date char( 1 ) default 'N'
)
specific GETMBRCONT
program type sub
result sets 1

begin
  declare LF char(1) ccsid 1208;

  declare v_Sql varchar( 1024 );
  declare v_SourceLine varchar( 512 );
  declare v_StmfOut clob( 2147483647 ) ccsid 1208 default '';
  declare v_LineCount int default 0;

  declare at_end int default 0;

  declare C cursor for S;

    declare continue handler for SQLSTATE '02000'
    begin
      set at_end = 1;
    end;

  set LF =  chr( 10 );    
  
  set ( lib, fil, mbr ) = ( trim( upper( lib ) ), trim( upper( fil ) ), trim( upper( mbr ) ) );  
  set v_Sql = 'create or replace alias QTEMP.' concat mbr concat ' for ' concat lib concat '.' concat fil concat '(' concat mbr concat ')';
  execute immediate v_Sql;

  set v_Sql = 'select '  concat case when inc_seq = 'Y'  then 'SRCSEQ concat '','' concat ' else '' end concat
                                case when inc_date = 'Y' then 'SRCDAT concat '','' concat ' else '' end concat
              'rtrim( SRCDTA ) from QTEMP.' concat mbr;
  prepare S from v_Sql;
  open C;
  
  if ( trim( stmf ) <> '' ) then
    set at_end = 0;
    
    while ( at_end = 0 ) do
      fetch from C into v_SourceLine;
      if ( at_end = 0 ) then
        set v_StmfOut = v_StmfOut concat v_SourceLine concat LF;
        set v_LineCount = v_lineCount + 1;
      end if;
    end while;
    
    call QSYS2.IFS_WRITE_UTF8( PATH_NAME => stmf, LINE => coalesce( v_StmfOut, '' ), OVERWRITE => 'REPLACE', END_OF_LINE => 'NONE' );
    close C;
  end if;
  
  return;
  
end

So I went ahead and wrote an uglier one, which does everything that Christian's does, but instead reuses the already created alias:

call liama.writeMemberContent('ILEDITOR', 'ILEDITOR_TEST273_THEMEMBER', '/home/liama/ileditor_test273_member.txt');

Source

create or replace procedure liama.writeMemberContent(lib varchar(10), alias varchar(50), outfile varchar(256))
begin
  declare list varchar(20000) default '';

  declare v_Sql varchar( 128 );
  DECLARE END_C INT DEFAULT 0;

  declare LINE_DAT varchar(10) default '';
  declare LINE_TXT varchar(200) default '';

  declare c cursor for S;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_C = 1;

  set v_Sql = 'select SRCDAT, rtrim( SRCDTA ) as SRCDTA from ' concat lib concat '.' concat alias;

  prepare S from v_Sql;

  open c;

  FETCH c INTO LINE_DAT, LINE_TXT ;
  WHILE END_C = 0 DO
    set list = list concat LINE_DAT concat ',' concat LINE_TXT concat ux'000A';
    FETCH c INTO LINE_DAT, LINE_TXT ;
  END WHILE;

  call qsys2.ifs_write_utf8(outfile, list, overwrite => 'REPLACE');

end;

---

Moral of this issue, is that we need to change downloadMemberContentWithDates to call this procedure after creating the alias (we need to create the alias for reusability) and then download the resulting temporary IFS file.

I think #1968 and #1975 need to get merged before this.

P.s.

This is going to sound like a crazy idea, but we have managed to get around a bunch of CCSID issues in the database extension through the use of the Server Component. We start a single job up and we don't have to worry about encoding stuff. It's actually pretty decent. I am starting to feel more inclined to see where that fits in here as it may solve a lot of encoding issues for us in the future.

[worksofliam]

Partly solved in #1985

[worksofliam]

This was created to solve the CCSID issue, but we've actually solved it already with cqsh, so I am closing.