Re: SQL for beginners

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