|
Hi
I have a requirement to return a list of all insured's policies and other
information when given a selected last name from a c#.net application.
This data would be pulled from the iSeries, and returned as a result set,
at least that's what I'm trying to do.
Toward this end, I created an external stored STR005SP procedure that
calls program STR005 to collect the requested data into a result set
array.
When I run this, thru sqlscript, I call STR005SP ('BARON, ' ', ' ') and
the sqlrpgle program STR005 gets called, and while debugging the program,
I can see that data is being stored in data structure array for the
requested insured with last name 'BARON" but, I don't know how to see in
the result set is return to the client application. (I'm trying to
simulate that thru sqlscript, but I never get any output that I'm
expecting. It just tells me completed successfully. I was expecting to
see the data displayed almost like a table, according to an example I saw
in the stored procedures redbook.
Also i'm outputing an array from STR005, which probably isn't going to
work with the SP parms as defined.
Need some suggestions on how to handle this. Maybe I should try using a
global temp file? Whatever that is.
Appreciate any help you can provide.
Here is the stored procedure;
EXEC SQL
CREATE PROCEDURE CRPRDLIBTS/STR005SP(INOUT LNAM CHAR ( 35), OUT
POLM CHAR ( 10), OUT INDTL CHAR ( 243)) RESULT SETS 3 LANGUAGE
RPGLE SPECIFIC CRPRDLIBTS/STR005SP NOT DETERMINISTIC MODIFIES SQL
DATA CALLED ON NULL INPUT EXTERNAL NAME CRPRDLIBTS/STR005
PARAMETER STYLE SQL
END-EXEC
EVAL *INLR = *ON
Here is the embedded sqlrpgle pgm:
The selected data is stored in array INDTL, and returned as a parm, which
I'm not sure is a correct way to handle that. In truth, I'm not sure how
I'm supposed to handle results sets, and that may be my problem.
Hdebug
H* Programmer:
H* Function..: SCAN FOR LAST NAME
H**************************************************************************
FCRTMST IF E K DISK
FCOVMST IF E K DISK
FCLMMST IF E K DISK
D INDTL E DS EXTNAME(INSN001)
D OCCURS(32765)
D count s 10u 0 inz(0)
D MySfw s 32740 varying
D StrQry s 130 INZ('SELECT * FROM
SPFILES/INSN001 +
D WHERE INLNAM = ''X''')
D StrOrdr s 23 INZ('ORDER BY INLNAM, INPOLM')
D startAt s 5 0
D slctToken c '''X'''
D quotToken s 1a inz('''')
D Str_Lnam S LIKE(INLNAM)
D WKAGNT S LIKE(INAGNT)
D WKPOLM S LIKE(INPOLM)
D WKPOLS S LIKE(INPOLS)
D WKFILE S LIKE(INFILE)
D WKLON# S LIKE(INLON#)
D WKINS# S LIKE(ININS#)
D WKLNAM S LIKE(INLNAM)
D WKFNAM S LIKE(INFNAM)
D PPLNAM S LIKE(INLNAM)
I**************************************************************************
C*----------------------------------------------------------------
C* PREPARE SQL CURSOR
C*----------------------------------------------------------------
/free
EXEC SQL
Set Option Commit = *NONE
, CloSQLCsr = *ENDMOD
, DatFmt = *ISO
, TimFmt = *ISO
, Naming = *SYS
;
begsr sqlprp;
//
// Setup select statement with selection criteria
//
startAt = %scan(slctToken : Strqry);
%subst(Strqry:startAt +1:35) = %trim(Str_Lnam);
%subst(Strqry:startAt +36:01) = quotToken;
%subst(Strqry:startAt +37:23) = StrOrdr;
MySfw = Strqry;
// This is what the select looks like
// SELECT * FROM SPFILES/INSN001 WHERE INLNAM = 'BARON
// 'ORDER BY INLNAM, INPOLM
//
// Prepare sql statememt
//
EXEC SQL prepare MYCSR from : MySfw ;
//
// Check for error
//
if SQLSTT <> *zero;
exsr *pssr ;
endif;
//
// Declare and open cursor
//
EXEC SQL declare RCDCSR cursor for MYCSR ;
EXEC SQL open RCDCSR ;
//
// Check for error
//
if SQLSTT <> *zero;
exsr *pssr ;
endif;
//
// FOR loop to retreive user selection
//
for count=1 to 32765;
%occur (INDTL )=count;
EXEC SQL fetch RCDCSR into : INDTL ;
// Load return parameter array
//
IF IMLNAM <> *BLANKS;
PPLDTL = INDTL;
ENDIF;
if sqlstt = '02000';
count-=1;
leave;
endif;
//
// Check for error
//
if sqlstt <> *zero;
exsr *pssr;
endif;
endfor;
//
// Output result set
//
EXEC SQL close RCDCSR ;
PPPOLM = %occur (INDTL )=1;
PPLDTL = %occur (INDTL )=1;
EXEC SQL Set result sets array : INDTL for : count
rows ;
*inlr = *on ;
endsr;
//
// Problem encountered
//
begsr *pssr ;
dump ;
endsr '*CANCL';
/end-free
C*----------------------------------------------------------------
C* LOOKING FOR RECORDS MATCHING USER SELECTION
C*----------------------------------------------------------------
C *INZSR BEGSR
C *ENTRY PLIST
C PARM PPLNAM 35
C PARM PPPOLM 10
C PARM PPLDTL 243
C CALL '$SYSDAT'
C PARM *ZEROS SEDATE 8 0
C PARM *BLANKS RTN 2
C* EVAL PPLNAM = 'KUNCE'
C EVAL Str_Lnam = PPLNAM
C EXSR SQLPRP
C ENDSR
This is what indtl and ppldtl looks like one record was selected,
which is correct.
INSTAT OF INDTL = ' '
INCOCD OF INDTL = 'EL'
INAGNT OF INDTL = '0134 '
INPOLM OF INDTL = '32639 '
INPOLS OF INDTL = 000.
INFILE OF INDTL = 00000000.
INLON# OF INDTL = ' '
ININS# OF INDTL = 01.
INLNAM OF INDTL = 'BARON '
INFNAM OF INDTL = 'MIRANDA '
INSIRN OF INDTL = ' '
INID# OF INDTL = ' '
Darrell Lee
Information Technology
Extension 17127
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
