Stored Procedure Example

CREATE PROCEDURE SPROCLIB.SELPGMARR(IN orhnbr CHAR(5) )  
RESULT SETS 1                          
LANGUAGE RPGLE                      
EXTERNAL NAME SPROCLIB.SELPGMARR         
READS SQL DATA                   
PARAMETER STYLE GENERAL

forderdtl  if   e           K disk    rename(orderdtl:norderdtl)   
di                s              3  0
dproduct          ds                  occurs(5)
dnumber                          5
c     *entry        plist    
c                   parm                    ordnbr            5
c                   eval      i=0
c     *LOVAL        SETLL     norderdtl
c                   read      norderdtl
c* in this loop -fetch all the rows in the resultant set into var:array
c                   dow       not(%eof)
c                   if        orhnbr=ordnbr
c                   eval      i=i+1
c     i             occur     product
c                   move      prdnbr        product
c                   endif         
c                   read      norderdtl         
c                   enddo                                           
c/exec sql
c+   SET RESULT SETS FOR RETURN TO CLIENT ARRAY :product FOR :i ROWS
c/end-exec   
c                   return

       String url = "jdbc:as400://myiseries;naming=sql";
       try {            
            DriverManager.registerDriver(
                 new com.ibm.as400.access.AS400JDBCDriver());
            connection = DriverManager.getConnection(url, userid, passwd);
            Statement stmt = connection.createStatement();            
            stmt.setInt(1,100); 
            ResultSet rs = stmt.executeQuery ("CALL SPROCLIB.SELPGMARR(?)");

            while (rs.next ()) {
                String product = rs.getString(1);
                System.out.println ("Product: " + product);
            }
        }
        catch (Exception e) {
            throw new ApplicationError(e.getMessage());
        }

        finally {
            try {
                if (connection != null)
                    connection.close ();
            }
            catch (SQLException e) {
                // Error is ignored.
            }
        }