Re: dynamic sql - receive fetched row values into host variables

"RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx> wrote on 10/16/2017 04:56:36
PM:

Thx. Dave. You really make it sound easy. I'm not sure the code you
have provided me is necessary for my solution. But thx. I'll
eventually get it. I'd like to hear from others and any code
demonstrations would be nice. To back up the theory behind what you
explain. Because honestly there is a hell of a lot of concept
explained on this out there but not enough concrete examples. Write
my code for me? Not hardly. I'm sure most of you learn best from
examples also.

I threw this together and tested it in debug. It fetched multiple
rows and the data was stored in the host_data array as expected.

**free
ctl-opt AlwNull(*USRCTL) DatFmt(*ISO) TimFmt(*ISO)
DftActGrp(*NO) ActGrp(*CALLER)
Debug Option(*SRCSTMT:*NODEBUGIO);

dcl-c var_num 20;
dcl-s sqlstmt varchar(900)
inz('Select * from SHR460/ACSYAPPV02 +
where Application_Code > ''CO'' +
order by Application_Code, Abbreviation_Code'
);
dcl-s idx packed(3:0);
dcl-s host_data char(255) dim(var_num);
dcl-s host_ind packed(5:0) dim(var_num) inz(*zero);

dcl-ds MySqlVar_t qualified template;
Type int(5:0);
Len int(5:0);
Rsrvd char(12);
Info1 char(16);
Data pointer overlay(Info1);
Rslt_Loca int(20:0) overlay(Info1);
Info2 char(16);
Ind pointer overlay(Info2);
Row_Change int(10:0) overlay(Info2);
Rslt_Rows int(10:0) overlay(Info2);
NameLen int(5:0);
Name char(30);
ExtdVar char(80) pos(1);
LongL int(10:0) overlay(ExtdVar:1);
RsvdL char(28) overlay(ExtdVar:*next);
InfoL char(16) overlay(ExtdVar:*next);
DataL pointer overlay(InfoL);
TNameLen int(5:0) overlay(ExtdVar:*next);
TName char(30) overlay(ExtdVar:*next);
end-ds;

dcl-ds MySqlDA qualified based(MySqlDA_p);
Id char(8);
Size int(10:0);
Ncnt int(5:0);
Dcnt int(5:0);
Var likeds(MySqlVar_t) dim(var_num);
end-ds;

exec sql set option ALWBLK = *NONE, CLOSQLCSR = *ENDACTGRP,
COMMIT = *NONE, DATFMT = *ISO, EXTIND = *YES,
LANGID = ENU, SRTSEQ = *HEX,
NAMING = *SYS, USRPRF = *USER;

exec sql declare DLCTEST_Cursor
cursor for DLCTEST_Stmt;

MySqlDA_p = %alloc(%size(MySqlDA));
MySqlDA.Size = %size(MySqlDA);
MySqlDA.Ncnt = %elem(MySqlDA.Var);

exec sql prepare DLCTEST_Stmt
into :MySqlDa
using system names
from :sqlstmt;
if sqlcode <> 0;
return;
endif;

for idx = 1 to MySqlDA.Dcnt;
MySqlDA.Var(idx).Data = %addr(host_data(idx));
MySqlDA.Var(idx).Ind = %addr(host_ind(idx));
endfor;

exec sql open DLCTEST_Cursor;
if sqlcode <> 0;
return;
endif;

DoW sqlcode = 0;
exec sql fetch next from DLCTEST_Cursor
using descriptor :MySqlDa;
if sqlcode = 0;
// process sql fetched cursor and put values in a host ds
endif;
enddo;

return;


Sincerely,

Dave Clark