|
midrange.com
Brad,
From what I can see, Phil's example has the same problem that Bruce
originally pointed out. The only thing that he tests for after a fetch is an
EOF condition, and he keeps looping until he finds it.
In addition to EOF (SQLCOD=100), you need to test for SQL errors (SQLCOD<0),
and other SQL warnings (SQLCOD>0). Here is a snippet from one of the
subroutines I use this stuff in:
* When SQL operation resulted in an EOF warning
* do something to exit your loop
C Select
C When (SqlCod = 100)
C Leave
* When SQL operation resulted in a different warning
* prepare the message ID returned by SQL
* send the message as an diagnostic to current procedure
C When (SqlCod > 0)
C Eval GenError = *On
C Eval GenErrorID = SqErPrpMsg( SqlCod )
C CallP SnMsgPrcQ1(GenErrorID: #MsgfSQL: '*DIAG':
C '*': '*NONE': '*NONE':
C 3: *Omit: SQLErM)
* When SQL operation resulted in an error
* prepare the message ID returned by SQL
* send the message as an diagnostic to current procedure
* do something to exit your loop
C When (SqlCod < 0)
C Eval GenError = *On
C Eval GenErrorID = SqErPrpMsg( SqlCod )
C CallP SnMsgPrcQ1(GenErrorID: #MsgfSQL: '*DIAG':
C '*': '*NONE': '*NONE':
C 3: *Omit: SQLErM)
C Leave
C EndSl
John Taylor
Canada
----- Original Message -----
From: "Stone, Brad V (TC)" <bvstone@taylorcorp.com>
To: <RPG400-L@midrange.com>
Sent: Friday, May 04, 2001 15:00
Subject: RE: SQL, the best way for looping
> 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
> +---
+---
| 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 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.
