RE: Array result sets problem

[Buck Calabro <Buck.Calabro@xxxxxxxxxxxx>]

>In RPG, as per IBM, we are defining a  multiple occurance 
>DS and send it back with the 
>SET RESULT SETS ARRAY :<MDS name> for :<index> ROWS. 

Here is some RPG:
h Debug Dftactgrp(*no) Actgrp('QILE')                       
 * dbgview(*source)                                         
                                                            
 * CREATE PROCEDURE BUCK/STOREDPRO1(IN returnRows INT)      
 *                  LANGUAGE RPGLE NOT DETERMINISTIC        
 *                  CONTAINS SQL CALLED ON NULL INPUT       
 *                  EXTERNAL NAME BUCK/STOREDPRO1           
 *                  PARAMETER STYLE GENERAL                 
 *                  RESULT SETS 50                          
                                                            
d Return          ds                  occurs(50)            
d  char                         30                          
d  int                          10i 0                       
d  decimal                      10p 2                       
                                                            
d ReqRowsInp      s             10i 0                       
d RowCount        s             10i 0 inz                   
                                                            
c     *entry        plist                                   
c                   parm                    ReqRowsInp      
                                                                   
c                   dou       RowCount = ReqRowsInp                
c                   eval      RowCount = RowCount + 1              
c     RowCount      occur     Return                               
c                   eval      char = 'Row ' +                      
c                                    %trim(%editc(RowCount: 'Z'))  
c                   eval      int = RowCount                       
c                   eval      decimal = RowCount                   
c                   endDo                                          
                                                                   
c/EXEC SQL INCLUDE SQLCA                                           
c/END-EXEC                                                         
                                                                   
c/EXEC SQL SET RESULT SETS ARRAY :Return FOR :RowCount ROWS        
c/END-EXEC                                                         
                                                                   
c                   eval      *InLR = *On                          



And the Java:
////////////////////////////////////////////////////////////////////////////
//////
//
// JDBC stored procedure example.  This program uses the IBM Toolbox for
Java JDBC driver to
// query a table and output its contents.
//
// Command syntax:
//    JDBCsp system
//
////////////////////////////////////////////////////////////////////////////
//////

import java.sql.*;

public class JDBCsp
{



    // Format a string so that it has the specified width.
    private static String format (String s, int width)
    {
        String formattedString;

        // The string is shorter than specified width,
        // so we need to pad with blanks.
        if (s.length() < width) {
            StringBuffer buffer = new StringBuffer (s);
            for (int i = s.length(); i < width; ++i)
                buffer.append (" ");
            formattedString = buffer.toString();
        }

        // Otherwise, we need to truncate the string.
        else
            formattedString = s.substring (0, width);

        return formattedString;
    }



    public static void main (String[] parameters)
    {
        // Check the input parameters.
        if (parameters.length < 1) {
            System.out.println("");
            System.out.println("Usage:");
            System.out.println("");
            System.out.println("   JDBCsp system");
            System.out.println("");
            System.out.println("");
            return;
        }

        String system           = parameters[0];

        Connection connection   = null;

        try {

            // Load the IBM Toolbox for Java JDBC driver.
            DriverManager.registerDriver(new
com.ibm.as400.access.AS400JDBCDriver());

            // Get a connection to the database.  Since we do not
            // provide a user id or password, a prompt will appear.
            connection = DriverManager.getConnection ("jdbc:as400://" +
system);
            DatabaseMetaData dmd = connection.getMetaData ();

            // Execute the query.
            Statement select = connection.createStatement ();
            ResultSet rs = select.executeQuery ("CALL BUCK.STOREDPRO1
(10)");

            // Get information about the result set.  Set the column
            // width to whichever is longer: the length of the label
            // or the length of the data.
            ResultSetMetaData rsmd = rs.getMetaData ();
            int columnCount = rsmd.getColumnCount ();
            String[] columnLabels = new String[columnCount];
            int[] columnWidths = new int[columnCount];
            for (int i = 1; i <= columnCount; ++i) {
                columnLabels[i-1] = rsmd.getColumnLabel (i);
                columnWidths[i-1] = Math.max (columnLabels[i-1].length(),
                    rsmd.getColumnDisplaySize (i));
            }

            // Output the column headings.
            for (int i = 1; i <= columnCount; ++i) {
                System.out.print (format (rsmd.getColumnLabel(i),
columnWidths[i-1]));
                System.out.print (" ");
            }
            System.out.println ();

            // Output a dashed line.
            StringBuffer dashedLine;
            for (int i = 1; i <= columnCount; ++i) {
                for (int j = 1; j <= columnWidths[i-1]; ++j)
                    System.out.print ("-");
                System.out.print (" ");
            }
            System.out.println ();

            // Iterate throught the rows in the result set and output
            // the columns for each row.
            while (rs.next ()) {
                for (int i = 1; i <= columnCount; ++i) {
                    String value = rs.getString (i);
                    if (rs.wasNull ())
                        value = "<null>";
                    System.out.print (format (value, columnWidths[i-1]));
                    System.out.print (" ");
                }
                System.out.println ();
            }

        }

        catch (Exception e) {
            System.out.println ();
            System.out.println ("ERROR: " + e.getMessage());
        }

        finally {

            // Clean up.
            try {
                if (connection != null)
                    connection.close ();
            }
            catch (SQLException e) {
                // Ignore.
            }
        }

        System.exit (0);
    }



}

  --buck