Re: SQL for beginners

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Dennis

It appears your cursor is not scrollable - I forget the exact syntax, it's
in the manual - so, once it hits EOF, it won't work anymore.

HTH

Vern

At 08:00 AM 11/4/02 -0500, you wrote:

> Hello, all:
>
> Where can I find good reference material for beginning the learning process
> for imbedded SQL?
>
> I've been programming IBM midrange systems since the inception of the
> System/3, but I've been lax about implementing some of the more modern
> approaches to retrieving data.
>
> I'd like to learn how to begin implementing imbedded SQL into applications.
> I want to start with something simple, and I've the perfect project for
> that now in my hot lttle hands.  I do understand SQL, having been an Oracle
> DBA for a number of years; just haven't put the two together to date.
>
> For this application, I merely want to read one table beginning to end (I
> have no problem with this ... the DECLARE CURSOR  stuff and the FETCH for
> that table all make sense to me).  This data has an employer and an
> employee number associated with it... and I want to use those to pull a
> couple of columns from the employee master file.
>
> I *THOUGHT* I understood this, but there's something I'm missing.  Once I
> get a "not found" condition, I can't seem to reset it.  So I know this is a
> fundamental gap, and I'd like to be able to overcome it.
>
> I've been looking at the DB2 UDB for iSeries SQL Programming ... book, and
> though it has some examples, there's nothing quite so simple, and I think I
> need simplicity at this point to understand why the following won't work:
>
> C/EXEC SQL   WHENEVER NOT FOUND Goto NotFound
> C/END-EXEC
> C/EXEC SQL   WHENEVER SQLERROR Goto SQL_Error
> C/END-EXEC
> C/EXEC SQL   DECLARE TransFile Cursor for
>   + Select <stuff omitted> From PRPRLCTER
> C/END-EXEC
> C/EXEC SQL   DECLARE MstrFile Cursor for
>  + Select    PRLNM, PRSTAT, PRLCOD
>  + From      PRPMS
>  + Where     PRER = :EMPLOYER
>  +      and  PREN = :EMPLOYEE
>  + FOR FETCH ONLY
> C/END-EXEC
> C/EXEC SQL   OPEN TransFile
> C/END-EXEC
> C/EXEC SQL   OPEN MstrFile
> C/END-EXEC
>
> C                   DoW       SQLCOD = 0
> C/EXEC SQL   FETCH TransFile INTO :TransBuff
> C/END-EXEC
> C                   If        SQLCOD = 0
> C/EXEC SQL   Fetch MstrFile  INTO :MstrBuff
> C/END-EXEC
>
> ...
>
> In initial testing, the above seems to work splendidly for the first cycle,
> where there is a match, but once the mismatch occurs (cycle 2), I'm out of
> luck.
>
> I'm not looking for anyone to "do my homework" here, but would like a good
> place to find fundamental examples, and learn where I might be going wrong.
>
> Thanks for indulging....
> Dennis