×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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)
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
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.