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


  1.  Home
  2. RPG400-L
  3. May 2001

RE: SQL, the best way for looping

  • Subject: RE: SQL, the best way for looping
  • From: "Stone, Brad V (TC)" <bvstone@xxxxxxxxxxxxxx>
  • Date: Fri, 4 May 2001 16:00:15 -0500 
Ahh... so you make sure everything else works before even fetching.

Do you really split your SQL stuff into subroutines?  I think I tried it
once and it wouldn't work.  It wanted them all "in order" in the same
section of code...

Brad

> -----Original Message-----
> From: Phil [mailto:sublime78ska@yahoo.com]
> Sent: Friday, May 04, 2001 3:00 PM
> To: RPG400-L@midrange.com
> Subject: RE: SQL, the best way for looping
> 
> 
> I check sqlcod = 0 for connect, disconnect, open, close, 
> declare, prepare
> etc.
> 
> With Fetch and select I check for SQLCOD = 100.
> 
> Something like
> 
> Dou eof
> Exsr SQLFetch
> If eof = *on
> Leave
> Endif
> Enddo
> 
> SQLFetch begsr
> Fetch
> If sqlcod = 100
> Eval eof = *on
> Endif
> Endsr
> 
> Phil
> -----Original Message-----
> From: owner-rpg400-l@midrange.com 
> [mailto:owner-rpg400-l@midrange.com]On
> Behalf Of Stone, Brad V (TC)
> Sent: Friday, May 04, 2001 3:35 PM
> To: 'RPG400-L@midrange.com'
> Subject: SQL, the best way for looping
> 
> This brings up an interesting question.  What do people use 
> for looping in
> SQL.  I know that if you check for 100, if your cursor 
> doesn't open for some
> reason you will loop forever, as Bruce mentioned.
> 
> What is the "best" way to do this?
> 
> Brad
> 
> > -----Original Message-----
> > From: R. Bruce Hoffman, Jr. [mailto:rbruceh@attglobal.net]
> > Sent: Friday, May 04, 2001 2:16 PM
> > To: RPG400-L@midrange.com
> > Subject: Re: SQL - just looking for a little guidance.
> >
> >
> > Not a comment on the SQL, but why do you first test not equal
> > to 100 and
> > then test equal to zeros on the SQLCODE?
> >
> > If you are looking for efficiency, those tests don't make a
> > whole lot of
> > sense.
> >
> > Also be aware that since your loops are based on the SQLCODE
> > being 100, that
> > another serious error returning something other than 100,
> > will cause the
> > program to loop.
> >
> > ===========================================================
> > R. Bruce Hoffman, Jr.
> >  -- IBM Certified Specialist - AS/400 Administrator
> >  -- IBM Certified Specialist - RPG IV Developer
> >
> > "America is the land that fought for freedom and then
> >   began passing laws to get rid of it."
> >
> >      - Alfred E. Neuman
> >
> > -----Original Message-----
> > From: Smith, Mike <Mike_Smith@RGCResources.com>
> > To: RPG400-L@midrange.com <RPG400-L@midrange.com>
> > Date: Friday, May 04, 2001 9:46 AM
> > Subject: SQL - just looking for a little guidance.
> >
> >
> > >I would just like for you to look at this program, and tell
> > me if it should
> > >have been done differently.
> > >It doesn't do a whole lot, but i would like to make it as
> > efficient as
> > >possible.
> > >Should i have done a multirow fetch?
> > >could i have done a join on the update and made the whole
> > thing work in 1
> > or
> > >2 sql statements.?
> > >
> > >I'm just trying to figure out how to do things better.
> > >
> > >Oour shop is exploring SQL and trying to use it as much as
> > possible and i'm
> > >the guy that gets to experiment with it.  So any help is 
> appreciated.
> > >
> > >I have this program that updates 2 files based on the data
> > from the first
> > >file
> > >  * DATA STRUCTURES
> > > D UPRMDS       E DS                  EXTNAME(UPRM) INZ
> > > D UCBSDS        E DS                  EXTNAME(UCBS) INZ
> > > D RTUPDWFDS E DS                  EXTNAME(RTUPDWF) INZ
> > >
> > >C/EXEC SQL
> > >C+  Declare WRKCURS CURSOR FOR
> > >C+  SELECT * FROM RTUPDWF
> > >C/END-EXEC
> > >
> > > * OPEN THE CURSOR - ROUTE CHANGE WORK FILE
> > >
> > >C/EXEC SQL
> > >C+  OPEN WRKCURS
> > >C/END-EXEC
> > >
> > > * READ THROUGH THE FILE UNTIL EOF
> > >
> > >C                   DOU       SQLCOD = 100
> > >C/EXEC SQL
> > >C+  FETCH WRKCURS INTO :RTUPDWFDS
> > >C/END-EXEC
> > >
> > >
> > > C                   IF        SQLCOD <> 100
> > > C                   IF        SQLCOD =  00000
> > >
> > >  * GET ALL RECORDS IN UPRM FOR THE ROUTE SELECTED IN RTUPDWF
> > >
> > > C/EXEC SQL
> > > C+  Declare PRMCURS CURSOR FOR
> > > C+  SELECT * FROM UPRM
> > > C+  WHERE  UPRTE = :RTORT
> > > C/END-EXEC
> > >
> > >  * OPEN THE CURSOR - PREMISE FILE
> > >
> > > C/EXEC SQL
> > > C+  OPEN PRMCURS
> > > C/END-EXEC
> > >
> > >  * READ THROUGH THE FILE UNTIL EOF
> > >
> > > C                   DOU       SQLCOD = 100
> > >
> > > C/EXEC SQL
> > > C+  FETCH PRMCURS INTO :UPRMDS
> > > C/END-EXEC
> > >
> > > C                   IF        SQLCOD <> 100
> > > C                   IF        SQLCOD =  00000
> > > C                   EXSR      $UPDATEPRM
> > > C                   ENDIF
> > > C                   ENDIF
> > > C                   ENDDO
> > >
> > > C                   EXSR      $CLOSEPRM
> > >
> > >  * GET ALL RECORDS IN UCBS FOR THE ROUTE SELECTED IN RTUPDWF
> > >
> > > C/EXEC SQL
> > > C+  Declare CBSCURS CURSOR FOR
> > > C+  SELECT * FROM UCBS
> > > C+  WHERE  USORT = :RTORT
> > > C/END-EXEC
> > >  * OPEN THE CURSOR - ACCOUNT BILLED SERVICES FILE
> > >
> > > C/EXEC SQL
> > > C+  OPEN CBSCURS
> > > C/END-EXEC
> > >
> > >  * READ THROUGH THE FILE UNTIL EOF
> > >
> > > C                   DOU       SQLCOD = 100
> > > C/EXEC SQL
> > > C+  FETCH CBSCURS INTO :UCBSDS
> > > C/END-EXEC
> > >
> > > C                   IF        SQLCOD <> 100
> > > C                   IF        SQLCOD =  00000
> > > C                   EXSR      $UPDATECBS
> > > C                   ENDIF
> > > C                   ENDIF
> > > C                   ENDDO
> > >
> > > C                   EXSR      $CLOSECBS
> > >
> > > C                   ENDIF
> > > C                   ENDIF
> > >                      ENDDO
> > > C                   EXSR      $CLOSEWRK
> > >                      EVAL      *INLR = *ON
> > > *================================================================
> > >  *  PROGRAM SUBROUTINES
> > >  *----------------------------------------------------------------
> > > *****************************************************************
> > >C     $UPDATEPRM    BEGSR
> > >C/EXEC SQL
> > >C+                  UPDATE UPRM SET
> > >C+                     UPRTE = :RTNRT
> > >C/END-EXEC
> > >
> > > C                   ENDSR
> > > *****************************************************************
> > >                                                                RTU
> > > C     $UPDATECBS    BEGSR
> > > C/EXEC SQL
> > > C+                  UPDATE UCBS SET
> > > C+                     USORT = :RTNRT
> > > C/END-EXEC
> > >
> > > C                   ENDSR
> > > *****************************************************************
> > > C     $CLOSEWRK     BEGSR
> > > C/EXEC SQL
> > >  + CLOSE WRKCURS
> > > C/END-EXEC
> > > C                   ENDSR
> > > *****************************************************************
> > > C     $CLOSEPRM     BEGSR
> > > C/EXEC SQL
> > >  + CLOSE PRMCURS
> > > C/END-EXEC
> > > C                   ENDSR
> > > *****************************************************************
> > >
> > > *************************************
> > >C     $CLOSECBS     BEGSR
> > >C/EXEC SQL
> > > + CLOSE CBSCURS
> > >C/END-EXEC
> > >C                   ENDSR
> > > *===================================
> > >
> > >
> > >Michael Smith
> > >
> > >+---
> > >| This is the RPG/400 Mailing List!
> > >| To submit a new message, send your mail to RPG400-L@midrange.com.
> > >| To subscribe to this list send email to 
> RPG400-L-SUB@midrange.com.
> > >| To unsubscribe from this list send email to
> > RPG400-L-UNSUB@midrange.com.
> > >| Questions should be directed to the list owner/operator:
> > david@midrange.com
> > >+---
> >
> > +---
> > | This is the RPG/400 Mailing List!
> > | To submit a new message, send your mail to RPG400-L@midrange.com.
> > | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
> > RPG400-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > +---
> >
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to 
> RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> 
> 
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
> 
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to 
> RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: 
> david@midrange.com
> +---
> 
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.