Embedded SQL, first steps

Originally posted on Midrange-L, David suggested it would be more suited 
to this list.

----- Forwarded by Adam Glauser/erb on 27/09/2006 01:26 PM -----

Hi all,

I'm dipping my toes in the embedded SQL waters, because I thought it would 
be more fun than adding a logical to an existing module (which requires 
renaming record format and fields and seems like overkill when it will 
likely only be used for one procedure).  I am at V5R3.  I was unable to 
find any examples in the archives or a quick skim of the relevant Redbooks 
(Embedded SQL Guide, SQL Reference).  Maybe I missed it due to my limited 
knowledge of embedded SQL.

So, I have a very simple procedure:

     P*--------------------------------------------------
     P* Procedure name: DSRMUNITS_getUnitByAssocUnit
     P* Purpose:        Retrieve the unit number with which a given unit 
is...
     P*                           associated
     P* Returns:        The unit to which the passed unit is associated
     P* Parameter:      assocUnit => The associated unit
     P* Parameter:      errorInfo => Return error information to the 
caller
     P*--------------------------------------------------
     P DSRMUNITS_getUnitByAssocUnit...
     P                 B                   EXPORT
     D DSRMUNITS_getUnitByAssocUnit...
     D                 PI                  LIKE(unit)
     D  assocUnit                          LIKE(unit)
     D                                     CONST
     D  errorInfo                          LIKEDS(UTTERRDS_errorDS)
 
 
     D* Local fields
     D retField        S                   LIKE(unit)
 
     C/EXEC SQL
     C+ SELECT vuUnit INTO :retField
     C+ FROM   unitFile
     C+ WHERE  vuAss = (SELECT shopUnit
     C+                 FROM   unitFile
     C+                 WHERE  unit = :assocUnit
     C+                 )
     C/END-EXEC
 
      /FREE
       errorInfo.errorCode = DSRMUNITS_NO_ERROR;
 
       if (SQLER3 = *zeros);
           errorInfo.errorCode = DSRMUNITS_UNIT_NOT_FOUND_ERROR;
           retField = *blanks;
 
       endif;
 
       return retField;
 
      /END-FREE
     P DSRMUNITS_getUnitByAssocUnit...
     P                 E

You may notice a problem with this - SQLER3 is not set for simple SELECT 
statements.  My question is this: is there a way to determine the number 
of rows selected?  In this case, I can probably just say 'if (retField = 
*blanks)' instead of 'if (SQLER3 = *zeros)', but in future I may want to 
handle the logical error where there are two matching records.

Any other pointers would also be appreciated,
Adam

#####################################################################################
Attention:
The above message and/or attachment(s) is private and confidential and is 
intended 
only for the people for which it is addressed. If you are not named in the 
address 
fields, ignore the contents and delete all the material. Thank you. Have a nice 
day.

For more information on email virus scanning, security and content
management, please contact administrator@xxxxxxxxxxxx
#####################################################################################