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



Okay,

Let me see if I'm following this....

You defined an external stored procedure pointing to STR005SP

Now STR005SP uses an cursor to build an array that you then want
returned as a results set?

WHY?

Just write an SQL stored procedure and be done with it....

CREATE PROCEDURE CRPRDLIBTS/STR005SP(IN LNAM CHAR ( 35))
RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
begin
declare c1 cursor with return for
SELECT * FROM SPFILES/INSN001 WHERE INLNAM = LNAM
ORDER BY INLNAM, INPOLM;

open c1;
end


**Note: SELECT * is a very bad practice for a production program. If
you add a column to the file, the client will start getting the new
column also. Depending on teh client and how it was coded, this might
cause a problem. You should explicitly name the fields you want.

HTH,
Charles







On Wed, Apr 29, 2009 at 3:51 PM, <DLee@xxxxxxxx> wrote:
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 ...

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.