RE: Embedded SQL, first steps

Adam

The Sorcerer's Guide to RPGIV has excellent examples.  

The example that you have provided below has the following
characteristics that you need to be aware of.

1)  In it's current form if more that 1 row is returned then you will
get a SQL error unless you have the clause "FETCH FIRST 1 ROWS ONLY"
2)  If you want more that one row to be available to your program then
you need to define a cursor and have open, fetch, and close statements.
I refer you to The Sorcerer's Guide to RPGIV for examples.

Andrew 

-----Original Message-----
From: rpg400-l-bounces+amckeown=hrblock.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+amckeown=hrblock.com@xxxxxxxxxxxx] On Behalf Of
AGlauser@xxxxxxxxxxxx
Sent: Wednesday, September 27, 2006 1:27 PM
To: RPG programming on the AS400 / iSeries
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.
Note: This message, including attachments, is from H&R Block Financial Advisors,
Inc., Member NYSE, SIPC. This is not a solicitation of any order to buy or 
sell. The
information contained herein is deemed to be reliable but is in no way 
warranted by us as
to accuracy or completeness. Messages are monitored and retained by the 
Company, but
the company cannot guarantee the security of this message. The company, and its
associates, may not accept orders to affect purchase or sale transactions, 
accept funds,
securities, or similar instructions via email. If you are not the intended 
recipient of this
message promptly delete this message and notify the sender of the delivery 
error by
return email or calling 1.800.HRBLOCK and selecting the option for 'Financial 
Services'
You may not forward, print, copy, distribute, or use the information in this 
message if
you are not the intended recipient.