Re: Embedded SQL, first steps

Hi Adam,

in the following Redbook, Chapter 7 you'll find all or at least almost all, you 
need for embedding SQL in RPG. 
And even a lot of examples:

Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open

Mit freunlichen Gruessen / Best regards 

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok) 




----- Original Message ----- 
From: <AGlauser@xxxxxxxxxxxx>
To: " RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, September 27, 2006 19:26
Subject: 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
#####################################################################################
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.