× 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.



This link is a PDF file that shows how you can create an SQL Stored Procedure, using the iSeries Navigator, in minutes. You can develop and test the stored procedure, then link it to your client program (the C# program).

http://www.web400.com/pdf/Mod4_CS2008_StoredProc_NET.pdf

Your RPG program is really complicated, for what is essentially an SQL statement with a parameter. The SQL Stored Procedure is designed for the type of requirement that you're facing.

At least work through the iSeries Navigator example, you'll then be in a better position to understand what you're trying to do.

Craig Pelkie




----- Original Message ----- From: <DLee@xxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, April 29, 2009 12:51 PM
Subject: Stored Procedure with Embedded SQL


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 thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.