RPG Cheatsheet

AS400 Dummy File And Pivot in SQL

IBM's equivalent to the DUAL table in Oracle is SYSDUMMY1. Some implementations of SQL allow values to be SELECTed without naming a table but, other implementations like DB2 and Oracle do not. IBM supplies the SYSDUMMY1 table for that purpose and Oracle, the DUAL table.

SELECTing a Row of Values From SYSDUMMY1

SELECT 1 ONE, 2 TWO, 3 THREE FROM sysibm.sysdummy1;

    ONE               TWO               THREE
      1                 2                   3

SELECTing a Column of Values From SYSDUMMY1

SELECT 'one' FROM sysibm.sysdummy1 UNION 
SELECT 'two' FROM sysibm.sysdummy1 UNION 
SELECT 'three' FROM sysibm.sysdummy1;

Constant Value
    one
    two
    three

Pivot in SQL

After learning about pivot tables in Excel, I found that SQL can also take horizontal data and place it in columns. This is often useful where tables have been denormalized. Retrieving the data with the UNION simplifies the coding that processes the data whether the code is RPG or Java.
create table states_afflictions(key1 CHAR(10), prop1 VARCHAR(20), 
key2 CHAR(10), prop2 VARCHAR(20), 
key3 CHAR(10), prop3 VARCHAR(20));

insert into states_afflictions values('Texas', 'tornadoes', 'Oklahoma', 'tornadoes', 'Arkansas', 'floods'); 
insert into states_afflictions values('Idaho', 'potato famine', 'Texas', 'rodeos', 'Lousiana', 'hurricanes');
insert into states_afflictions values('Arkansas', 'cotton', 'Louisiana', 'mosquitoes', 'Texas', 'hurricanes');
insert into states_afflictions values('Texas', 'heat', 'Texas', 'drought', 'Florida', 'hurricanes');

select 'from 1st kv pair', key1, prop1 from states_afflictions where key1 = 'Texas' UNION ALL 
select 'from 2nd kv pair', key2, prop2 from states_afflictions where key2 = 'Texas' UNION ALL
select 'from 3rd kv pair', key3, prop3 from states_afflictions where key3 = 'Texas';

Results
from 1st kv pair|Texas|tornadoes
from 1st kv pair|Texas|heat
from 2nd kv pair|Texas|rodeos
from 2nd kv pair|Texas|drought
from 3rd kv pair|Texas|hurricanes

Basic RPG Demo Executing the SQL


Basic Java Demo Executing the SQL

This is a standard Maven + Spring Boot project.


The Resulting Output From the Java
keyval = Texas, drought
keyval = Texas, heat
keyval = Texas, hurricanes
keyval = Texas, rodeos
keyval = Texas, tornadoes

Are MVC & 3-Tier Architecture the Same Thing?

The MVC design pattern is sometimes mistaken for the 3-Tier architectural pattern.

Are MVC and 3-Tier the Same Thing?

•  MVC was developed in the late 70s for desktop apps by Smalltalk developer Trygve Reenskaug.

•  3-Tier architecture was developed in the early 90s by MIT professor John Donovan.


MVC & 3-Tier evolved separately and at different times. They are different things.


Is the Application Data Model the Same as the DB Data Model?

•  When a program prompts a user to save his changes, is the data in the database? Obviously not. So, is the database the application's model? Obviously not. After the user makes changes to fields displayed on the screen, the program's copy of the data is out of sync with the database and saving will update the database with the changes made by the user.

•  When a user elects to undo changes he has made, where are the versions of the data? Has anyone ever implemented undo and redo in the database? Undo and redo are not performed in the database. Undo and redo are performed on data in the application's memory (in OO, the domain model).

•  If you migrate your data store from DB2 to MongoDB you will have to modify the application's data layer to process Mongo's JSON instead of SQL's result sets but the model itself and the code in the MVC implementation won't need to change. MVC is independent of data access and data store.

•  Although developers often speak of a data model in the DB, it is not the same model as is in program memory. You can find many examples of Domain Model POJOs (plain old Java objects) by searching online. Domain model POJOs aren't coded in SQL.

•  MVC is used in the front end of email, system management dashboards and drawing apps, none of which have a relational DB data store. The objects used in the email or drawing app aren't coded in SQL so, the model in MVC isn't a DB model.

Is There Data Access in MVC?

•  You only have to look at Microsoft's frameworks to see that the presentation framework ASP.Net MVC is completely separate from Microsoft's database access solution, ADO.Net Entity Framework. We conclude therefore that the application cannot use the .Net MVC framework for data access and requires the Entity framework or similar component to perform that work.

•  MVC tutorials freely available on the web show nothing about interacting with the database. MVC is independent of the data store.

•  Database IO design patterns such as DAO have nothing to do with MVC since MVC has nothing to do with the database.

•  The 1st image below shows interaction in the MVC components.

•  The 2nd image shows 3-Tier architecture which is composed of totally different things.

   


Unit Test RPG with JUnit

A presentation I gave to the Dallas AS400 User Group some years ago.

Google's Latitude & Longitude for RPG

I was asked to add the Google geocoding functionality for longitude and latitude to a legacy green-screen (interactive) program.

Before this project, all of my Java code has run in a JEE container but, since my company's operations team wasn't very familiar with app servers, I designed my proof of concept to run as a simple Java program. The next choice was determining where the Java would run.

Having RPG make direct calls to Java seemed to be a poor choice as it would start a JVM for each user job that used the function. Another alternative, RPG's HTTP service, would require I write the RPG code needed to parse the JSON document Google returns and, in retrospect, I'm glad I avoided that option.

I developed a simple design in which one instance of the Java program can process all the requests made by users of the interactive IBM i program. The company's security plan requires the clients of external HTTP services run in a different tier from the production IBM i so the Java application runs on a Linux server in the web tier.

The design uses a single FIFO data queue to communicate requests from instances of the RPG program running in interactive jobs to a Java program and then to return the response via a single keyed data queue from the Java to the calling instance of the RPG program. The keyed data queue solves the problem of ensuring the response goes to the correct requesting job.

To make this work, each request sent from a RPG program will contain a unique key that the RPG program generates by using the IBM i UUID generator API. The RPG program sends the request by writing the key along with the address that was entered on the screen to the FIFO data queue. The Java program running on Unix reads the data queue and passes the address to the Google API. Google returns the lat/long (latitude and longitude) in the response which Java writes to the keyed data queue along with the key the RPG sent in the request. The RPG then performs a read on the keyed data queue to get the lat/long.

Screen Shot - Connecting With An Early Version of the Code

Sending the address to Google and getting the lat/long response the first time.



The Java writes the longitude and latitude extracted from Google's response to the keyed DTAQ along with the key the RPG program generated. And lastly, the RPG subprocedure reads the entry from the keyed DTAQ. The key ensures that only the original requestor will get the response for its request.

SRVPGM Testing with JUnit

The ServiceProgramCall class in AS400 Java toolbox makes it easy to execute service program (SRVPGM) subprocedures from Java but there are some limitations that the ServiceProgramCall API has that limit your choices in the development of the subprocedure. The documentation states the following requirements for the design of the service program subprocedure:
  • The service program must be on an AS/400 running OS/400 V4R4 or later.
  • Up to seven parameters can be passed to the service program.
  • The return value must be void or numeric. This class does not support calling service programs that return a pointer.


  • As shown in the diagram, when a user has entered an address on a 5250 UI, the getLatLng subprocedure places the address and a GUID on the request FIFO data queue and immediately performs a receive on the keyed data queue for 5 seconds. The Java app reads the request from the FIFO queue and sends the address to Google which returns the latitude and longitude and county name. The county name is converted into the county code and the three values are put on the keyed data queue along with the RPG program's GUID key. The subprocedure waits for a response on the keyed data queue and returns the values to the caller.



    There is a lot going on but with the modularity of service programs and Java classes, the code stays fairly simple. This application does the following as shown on in the diagram:
  • RPG puts request on first DTAQ
  • RPG waits on response DTAQ
  • Java reads request DTAQ
  • Java sends address to Google
  • Java parses JSON for lat/lng
  • Java does county code lookup
  • Java puts lat/lng/county code on response DTAQ
  • RPG gets response off DTAQ it waited on


  • The purpose of the JUnit code is to replace the call of the subprocedure made from the RPG program with a call from the Java JUnit test. This test is an integration test of all the components working together and the main benefit it provide is performance verification.





    RPG Program Working With JRuby to Respond to Ajax Request

    This Javascript and JRuby code will work with most databases but, in this case, the stored procedure language is RPG. The data retrieved by the RPG program is sent to the web page as JSON. Right now the rendered HTML is pretty ugly because I haven't gotten around to utilizing the CSS.

    The button defined in the HTML controls the submission of the AJAX request for JSON which is a GET request processed by the JRuby code. The JRuby calls the RPG program via JDBC.

    CAVEATS: The HTML needs a rewrite and the SQL wrapper code is omitted for brevity. You can find the JDBC config yaml file in this earlier post.

    JRuby Executes AS400 RPG Program Stored Procedure

    The code below includes a simple JRuby program that connects to an AS400 and executes an RPG program. Code and detailed instructions are below.

    After compiling the RPG and creating the stored procedure, run the Sinatra program by entering jruby as400sp.rb in the PC's command terminal window.

    The as400sp.rb JRuby program will run on the PC and connect to the AS400 and execute the RPG program (stored procedure).

    Verify using a browser by entering the URL:
    http://localhost:4567/capitalize?first=George&last=Jetson

    The URL and parameters will return a JSON response
    {"name":{"first":"GEORGE","last":"JETSON"}}



    AS400 Stored Procedures' Parameters

    The System i Navigator will display the information about your AS400's stored procedures but I more often use some version of this SQL statement, modifying the WHERE clause as needed.
    select substr(A.SPECIFIC_SCHEMA,1,10) AS "LIB",    
           substr(A.SPECIFIC_NAME,1,15) AS "PROC",     
           B.ORDINAL_POSITION AS "POS",                
           B.PARAMETER_MODE AS "MODE",                 
           substr(B.PARAMETER_NAME,1,15) AS "PARMNAME",
           substr(B.DATA_TYPE,1,15) AS "TYPE",         
           B.CHARACTER_MAXIMUM_LENGTH AS "CHARLEN",    
           B.NUMERIC_PRECISION AS "PRECISION",         
           B.NUMERIC_SCALE AS "SCALE"                  
    from qsys2/sysprocs A                              
    join qsys2/sysparms B  
    on A.SPECIFIC_SCHEMA = B.SPECIFIC_SCHEMA          
    and A.SPECIFIC_NAME = B.SPECIFIC_NAME                      
    WHERE A.SPECIFIC_SCHEMA = 'GHELTON' 
    
    LIB         PROC                 POS   MODE   PARMNAME         TYPE              CHARLEN    PRECISION      SCALE
    GHELTON     SEARCHOTBANDOVL        1   IN     P_PVID           INTEGER                  -          10          0
    GHELTON     SEARCHOTBANDOVL        2   IN     P_TAXID          CHARACTER              50             -     
    GHELTON     SEARCHOTBANDOVL        3   IN     P_FMTZIP5        CHARACTER               5             -     
    GHELTON     SEARCHOTBANDOVL        4   IN     P_FMTZIP9        CHARACTER               9             -     
    GHELTON     SEARCHOTBANDOVL        5   IN     P_ORIGIN         CHARACTER              20             -     
    GHELTON     SEARCHOTBANDOVL        6   IN     P_TPA            CHARACTER              10             -    
    GHELTON     SEARCHOTBANDOVL        7   IN     P_BILENT         CHARACTER              20             -    
    GHELTON     SEARCHOTBANDOVL        8   IN     P_TARGETAMOUNT   DECIMAL                  -          20          2
    GHELTON     SEARCHOTBANDOVL        9   IN     P_TARGETDATE     CHARACTER              10             -   
    GHELTON     SQLRPGJDBC             1   IN     -                CHARACTER              15             -    
    GHELTON     SQLRPGJDBC             2   IN     -                CHARACTER              15             -   
    GHELTON     UPDATEOUTSIDEVE        1   IN     ID               INTEGER                  -          10          0
    GHELTON     UPDATEOUTSIDEVE        2   IN     PHONE            CHARACTER VARYI        20             -    
    GHELTON     UPDATEOUTSIDEVE        3   IN     USERID           CHARACTER VARYI        20             -    
    GHELTON     UPDATEOUTSIDEVE        1   IN     ID               INTEGER                  -          10          0
    GHELTON     UPDATEOUTSIDEVE        2   IN     FAX              CHARACTER VARYI        20             -    
    GHELTON     UPDATEOUTSIDEVE        3   IN     PHONE            CHARACTER VARYI        20             -    
    GHELTON     UPDATEOUTSIDEVE        4   IN     USERID           CHARACTER VARYI        20             -    
    ********  End of data  ********  
    

    Implementing Stack in RPG

    Pascal Plus Data Structures is the textbook I got the most value from in school. A program's data structures are probably the biggest factor in the complexity of the program's code. In recent years, RPG's data structure syntax has been expanded to include reuse of definitions and nested data structures. Such features allow the code to be more concise while being more expressive.

    Having recently coded some RPG-ILE subprocedures to implement a linked list (see previous post), I decided to implement a stack as well. The stack is a LIFO (last in, first out) data structure manipulated by Push and Pop functions.

    The first field declared in the program code, stackable, is the definition of field that is stored in the stack. Changing this one field definition should be the only change necessary to change the element stored in the stack and referenced by the Push and Pop subprocedures.

    The program code shows an example of the use of stacks. Five names are pushed on to stack1 then three names are popped from stack1 and saved on stack2. A new name is then pushed on to stack1 and the saved names restored from stack2 back to stack1. PrintStack() then is executed to perform a non-destructive print of the values in stack1.

    h option(*srcstmt:*nodebugio)                                        
     
    d stackable       s              6A                                  
      
    d Print           pr                  extproc('Stack.Print') 
    d  stack                              likeds(stack1) 
     
    d Pop             pr                  extproc('Stack.Pop') like(stackable) 
    d  field                              likeds(stack1) 
     
    d Push            pr                  extproc('Stack.Push') 
    d  stack                              likeds(stack1)
    d  field                              like(stackable) CONST          
                                                                         
    d Empty           pr              N   extproc('Stack.Empty')               
    d  stack                              likeds(stack1) 
                                                                         
    d stack1          ds                  qualified INZ 
    d  nodes                              like(stackable) dim(20)        
    d  cursor                        3P 0                                
      
    d stack2          ds                  qualified INZ                  
    d  nodes                              like(stackable) dim(20)       
    d  cursor                        3P 0                              
      
    d amy             s                   like(stackable) inz('Amy   ')
    d betty           s                   like(stackable) inz('Betty ')
    d carrie          s                   like(stackable) inz('Carrie')
    d denise          s                   like(stackable) inz('Denise')
    d ertha           s                   like(stackable) inz('Ertha ')
    d fannie          s                   like(stackable) inz('Fannie')
    d x               s              3P 0                              
        
     /free                                                             
               Stack.Push(stack1 : amy);                                     
               Stack.Push(stack1 : betty);                                   
               Stack.Push(stack1 : carrie);                                  
               Stack.Push(stack1 : denise);                                  
               Stack.Push(stack1 : ertha);                                   
       
               for x = 1 to 3;                                         
                  Stack.Push(stack2 : Stack.Pop(stack1));                          
               endFor;                                                 
     
               Stack.Push(stack1 : fannie);                                  
     
               dow not Stack.Empty(stack2);                                 
                  Stack.Push(stack1 : Stack.Pop(stack2));                    
               endDo;                                            
     
               Stack.Print(stack1);
     
               *inlr = *on;             
     /end-free  
     *********************************************************** 
     *                                                           
     *********************************************************** 
    p Push            b                   EXPORT                 
    d                 pi                                         
    d stack                               likeds(stack1)         
    d item                                like(stackable) CONST  
     /free        
                stack.cursor += 1;                               
                stack.nodes( stack.cursor ) = item;              
     /end-free        
    p                 e                                          
     *********************************************************** 
     *      
     *********************************************************** 
    p Pop             b                                          
    d                 pi                  like(stackable)       
    d stack                               likeds(stack1)         
     /free 
                stack.cursor -= 1;                               
                return stack.nodes(stack.cursor + 1);            
     /end-free 
    p                 e   
     *********************************************************** 
     * 
     *********************************************************** 
    p Print           b                   EXPORT                 
    d                 pi 
    d stack                               likeds(stack1)         
    d name            s                   like(stackable)        
      
    d stack3          ds                  qualified INZ          
    d  nodes                              like(stackable) dim(20)
    d  cursor                        3P 0 
     
     /free 
            dow (not Stack.Empty(stack));                              
               name = Stack.Pop(stack);                                
               Stack.Push(stack3:name);                                
               dsply name;                                       
            endDo;                                              
     
            dow (not Stack.Empty(stack3));                            
               Stack.Push(stack : Stack.Pop(stack3));                         
            endDo;                                              
     /end-free                                                  
    p                 e                                         
     ***********************************************************
     *  
     ***********************************************************
    p Empty           b                   EXPORT                
    d                 pi              N                         
    d stack                               likeds(stack1)        
     
     /free 
                return stack.cursor = 0;                        
     /end-free            
    p                 e 

    Most RPG programmers have adopted RPG-IV, ILE subprocedures and free form coding so many may find nothing new here. But, for those not completely familiar with these new facets of RPG, let's review the densest line of code in the program and work out from there. I consider the two calls in the one line of code to be a busy line of code. This line

    Stack.Push(stack2 : Stack.Pop(stack1));

    calls the Pop subprocedure which returns a value that is passed to the Push subprocedure. In other words, the value popped from stack1 is pushed onto stack2.

    The fact that a subprocedure (Pop) is called from the argument list of another subprocedure (Push) did impact the coding of Pop. The second parameter of Push had to be declared as CONST to eliminate the compiler error. This is for similar reasons that the parameter has to be declared CONST when it accepts a literal argument. In both cases it is because there is no variable to accept any changes to the argument made within the subprocedure.

    p Push            b                   EXPORT                 
    d                 pi                                         
    d stack                               likeds(stack1)         
    d item                                like(stackable) CONST

    There is a small but very significant difference between the coding of the parameters (and return value) of subprocedures Push and Pop. The style used to declare parameters and return values makes it easy to overlook the differences between Push and Pop. Pop returns a value and this is coded on the D spec 'pi' (subprocedure interface) line shown below. Push's 'pi' line (above) does not have a type defined on it so it has no return value. Both subprocedures name have a parameter named stack. Push has a second parameter named item. Pop has only the one parameter.
    p Pop             b                                          
    d                 pi                  like(stackable)       
    d stack                               likeds(stack1) 

    Now that we've seen the mechanics of the return value, let's put it to work. By having the Push() subprocedure return the stack passed to it as shown here:
     /free
                Push(Push(Push(Push(stack1:amy):betty):carrie):denise);
                *inlr = *on;
     /end-free
    p Push            b                   EXPORT                 
    d                 pi                  likeds(stack1)   
    d stack                               likeds(stack1)         
    d item                                like(stackable) CONST  
     /free        
                stack.cursor += 1;                               
                stack.nodes( stack.cursor ) = item;  
                return stack;            
     /end-free        
    p                 e

    But, the compiler complains that the stack passed to it must be declared CONST and, because doing so would contradict our desire to update the stack, a compromise is reached in which CONST is used but the parameter is now a pointer to a stack.

    RPG Linked List

    A developer told me about a problem he was having and I told him I thought a linked list would help in creating a good solution. Below is the example I created for him. There are many perfectly acceptable ways of creating a linked list but, I had a particular model in mind. My goal was to create a linked list that would both retain its original sequence and present the altered sequence. I also wanted the implementation to be clean and simple.
    The purpose of the linked list is to be able to resequence data and that is exactly what this assignment required. I created the moveAbove() function that accepts two arguments, the node to be moved and the node to be superceded in the list.
    The linked list data structure is a list of girls' names. The list was initialized with the girls' names in alphabetical order - Amy, Betty, Carrie, Denise, Ertha and Fannie. Then moveAbove(5:2) is run. The results are shown in blue above the program code. The DSPLY statements show the name, the original seq# and the nextPointer (not really a pointer). Note the seq# is their original sequence and the data in the array is still in that order. All that moveAbove() does is reassign next values and the print function prints in the linked list sequence. I was taught that the program is more efficient since data is not moved. The function is extensible as the data element is referenced by a pointer.
    DSPLY  Amy    001 005
    DSPLY  Ertha  005 002
    DSPLY  Betty  002 003
    DSPLY  Carrie 003 004
    DSPLY  Denise 004 006
    DSPLY  Fannie 006 007
    
    h option(*srcstmt:*nodebugio)                          
                                                           
    d printList       pr                                   
                                                           
    d moveAbove       pr                                   
    d                                3P 0 VALUE            
    d                                3P 0 VALUE            
                                                           
    d topOfList       s              3P 0 inz(1)           
                                                           
    d list            ds                  qualified dim(20)
    d seq                            3P 0 inz              
    d data                            *   inz(*NULL)       
    d next                           3P 0 inz              
                                                           
    d dsplyField      s             14A                    
    d amy             s              6A   inz('Amy   ')    
    d betty           s              6A   inz('Betty ')    
    d carrie          s              6A   inz('Carrie')    
    d denise          s              6A   inz('Denise')    
    d ertha           s              6A   inz('Ertha ')    
    d fannie          s              6A   inz('Fannie')    
                                                          
     /free                                              list(1).seq  = 1;             
               list(1).next = 2;             
               list(1).data = %addr(amy);    
               list(2).seq  = 2;             
               list(2).next = 3;             
               list(2).data = %addr(betty);  
               list(3).seq  = 3;             
               list(3).next = 4;             
               list(3).data = %addr(carrie); 
               list(4).seq  = 4;             
               list(4).next = 5;             
               list(4).data = %addr(denise); 
               list(5).seq  = 5;             
               list(5).next = 6;             
               list(5).data = %addr(ertha);  
               list(6).seq  = 6;             
               list(6).next = 7;             
               list(6).data = %addr(fannie); 
                                             
               moveAbove(5:2);               
               printList();                  
               *inlr = *on;                  
     /end-free                              
    p printList       b                                  
    d                 pi                                 
    d x               s              3P 0                
    d ptrName         s               *                  
    d name            s              6A   based(ptrName) 
     /free                                               
            x = topOfList;                               
            dow list(x).data <> *NULL;                   
               ptrName = list(x).data;                   
               dsplyField = name                         
                          + %editw(list(x).seq:'0   ')   
                          + %editw(list(x).next:'0   ') ;
               dsply dsplyField;                         
               x = list(x).next;                         
            endDo;                                       
     /end-free                                           
    p                 e                                  
     *                                                   
    p moveAbove       b                                  
    d                 pi                                 
    d number1                        3P 0 VALUE          
    d number2                        3P 0 VALUE          
    d x               s              3P 0                
    d ptrName         s               *                 
    d name            s              8A   based(ptrName)                                     
     /free                                                                                   
                                                              // move 5 above 2              
            for x = 1 to %elem(list) ;                                                       
               if list(x).next <> 0;                                                         
                                                              // make 4 point to 6           
                  if list(x).next = list(number1).seq;        // 4.next = 5                  
                     list(x).next = list(list(x).next).next;  // 4.next = 6.seq              
                     leave;                                                                  
                  endIf;                                                                     
               endIf;                                                                        
            endFor;                                                                          
                                                                                             
                                                                                             
            for x = 1 to %elem(list) ;                                                       
                                                              // make 1 point to 5 and 5 to 2
               if list(x).next = list(number2).seq;           // 1.next = 2.seq              
                  list(x).next = list(number1).seq;           // 1.next = 5.seq              
                  list(number1).next = list(number2).seq;     // 5.next = 2.seq              
                  leave;                                                                     
               endIf;                                                                        
            endFor;                                                                          
                                                                                             
     /end-free                                                                              
    p                 e
    


    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.
                }
            }
    
    
    

    Blog Archive