Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to get return array from rpg program ?

Posted on 2008-06-22
10
Medium Priority
?
2,311 Views
Last Modified: 2013-12-10
Hi

I have created a rpg program that returns 2 parameter 1 is the id and another one is list of array, when I called this program I passed two programparameter from my java code  (see the code below) but when i checked what value would be return it is returned only first value of array. how will i get all array values ?

please suggest me regarding this issues I amn't so much aware on java & AS400.


 try
         {
               
          ProgramParameter[] parmList = new ProgramParameter[2];

          AS400Text p1 = new AS400Text(10);
        AS400Text p2 = new AS400Text(30);
         try
          {
                  parmList[0] = new ProgramParameter(10);
                  parmList[1] = new ProgramParameter(30);
                parmList[0].setParameterType(ProgramParameter.PASS_BY_REFERENCE);
                 parmList[1].setParameterType(ProgramParameter.PASS_BY_REFERENCE);
                 
                 parmList[0].setInputData(p1.toBytes("Pune"));
                 parmList[1].setInputData(p2.toBytes(" "));

          }
          catch(Exception ex)
          {
          }
               
               ProgramCall pgm = new ProgramCall(o);
               pgm.setProgram("/QSYS.LIB/XXX/XXX.PGM",parmList);

         if (pgm.run())
          {
                     
               byte s[] =  parmList[1].getOutputData(); // HERE I got only first value of returning array.
             
               parmList[1].getOutputDataLength();
               
                   //String sts = ((String) (new AS400Text(10,o).toBytes(s[0])));
             }
             else
             {
                   AS400Message[] messageList = pgm.getMessageList();
                  for (int msg = 0; msg < messageList.length; msg++) {
                 
                  }
             }
              
                     
              }
         catch(Exception ex)
         {
               AS400Message[] messageList = null;
         }
         finally
             {
               o.disconnectAllServices();
             }
      }
0
Comment
Question by:vikasbapat
  • 4
  • 2
  • 2
10 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 21844543
Hi vikasbapat,

Is it possible that the problem is inside the RPG program, that the return parameter is filled the wrong way ?

If you not sure, please post the RPG (as Snippet) and I (we) will look.

Regards,
Murph
0
 

Author Comment

by:vikasbapat
ID: 21844881
Hi murphey2.

Thank you very much for posting the solution.
Actually i am getting both the parameter in my java code  but problem is that I wanted to fetch array as second parameter like
parmList[0] = "XX"
parmList[1] =  Array()

I got only first value of that array not all of them.
Is it possible in java As400 I also attach RPG program with this message please suggest me.

and also tell in my database there is a table for student record I need to retrieve all the records How can I do this thing which is the best thing to get the records easily?
 

hdftactgrp(*no)actgrp(*caller)
     FSMASTER1  IF   E           K DISK    rename(smaster1:test)
     F                                     USROPN
     DARRAY1           S             30A   DIM(10)
     DCITY1            S             10A
     DI                S             10  0
      *--------------------------------------------------------
     c                   Open      Smaster1
     C                   EVAL      I=0
     C                   EVAL      array1 = *blanks
     C     *ENTRY        PLIST
     C                   PARM                    CITY1
     c                   Parm                    Array1
     C     *loval        SETLL     SMASTER1
     C                   READ      SMASTER1
     C                   DOW       NOT%EOF()
     c                   If        CITY = CITY1
     C                   EVAL      I=I+1
     C                   EVAL      ARRAY1(I)=NAME
     c                   Endif
     C                   READ      SMASTER1
     C                   ENDDO
     c                   Close     Smaster1
     C                   RETURN
     c                   seton                                        lr
      *
     c     *INZSR        Begsr
     c                   call      'STUDENT1/T1'
     c                   Endsr
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 21847991
hi  vikasbapat,

I'm not sure if an Array can be used as parameter within RPG (never tried) but you can try this
(See snippet)  see the PARM2 field

Regards,
Murph

     FSMASTER1  IF   E           K DISK    rename(smaster1:test)  
     F                                     USROPN                 
     DARRAY1           S             30A   DIM(10)                
     DCITY1            S             10A                          
     DI                S             10  0                        
==>  DPARM2            S            300A                          
      *--------------------------------------------------------   
     c                   Open      Smaster1                       
     C                   EVAL      I=0                            
     C                   EVAL      array1 = *blanks               
     C     *ENTRY        PLIST                                    
     C                   PARM                    CITY1            
==>  C                   Parm                    PARM2            
     C     *loval        SETLL     SMASTER1                       
     C                   READ      SMASTER1                       
     C                   DOW       NOT%EOF()                      
     C                   If        CITY = CITY1                   
     C                   EVAL      I=I+1                          
     C                   EVAL      ARRAY1(I)=NAME                 
     c                   Endif                                             
     C                   READ      SMASTER1                                
     C                   ENDDO                                             
     C                   Close     Smaster1                                
     C                   RETURN                                            
==>  C                   EVAL      PARM2 = Array                           
     C                   seton                                        lr   
      *                                                                    
     c     *INZSR        Begsr                                             
     c                   call      'STUDENT1/T1'                           
     C                   Endsr                                             

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 21848613
About the other part  uoy asked :
and also tell in my database there is a table for student record I need to retrieve all the records How can I do this thing which is the best thing to get the records easily?

You can do that easily when ther would be a phisical-file (index) on City then you can use    See snippet
(a key that starts with city is enough)

If City is not a key element, you cen use SQL within RPG to select the records

I can provide you an example if needed
     C     CITY1         SETLL     SMASTER1                       
     C     CITY1         READE      SMASTER1                       
     C                   DOW       NOT%EOF() and I<10                              
     C                   EVAL      I=I+1                          
     C                   EVAL      ARRAY1(I)=NAME                 
     C     CITY1         READE      SMASTER1
     C                   ENDDO                           

Open in new window

0
 

Author Comment

by:vikasbapat
ID: 21852819
hi murphey2,
could you just tell me if i need fetching the records from  phisical-file how  can i get those on java side,
suppose if i write one sql "select * from SMaster1.Student1;" then it will return all the records, on java side I use ResultSet to get all the records and display those anywhere wherever i need.
but problem is tht if i used Rpg program then how can i do this ? else any other way to get ? bks i passed one or two parameter so that it will be return only one record on the rpg program.
 please help me ....i m quite confuse.
0
 
LVL 16

Accepted Solution

by:
theo kouwenhoven earned 1000 total points
ID: 21853535
I will post only the SQL_part and handling, the rest will be up to you :)



C     *ENTRY        Plist
C                   Parm                    #INPARM
C                   Parm                    #OUTPARM
 *--- Open file
C                   ExSr      SQL_OPEN
 *--- Fill fields
C                   ExSr      SQL_READ
C     SQLCOD        DoWNe     100
C     I             AndLT     10                                           Your counter
C                   Eval      I=I+1
C                   Eval      #ARRY(I) = CITY
C                   ExSr      SQL_READ
C                   EndDo
 *--- Fill outparm
C                   Eval      #OUTPARM = #ARRY
		    ExSr      SQL_CLOSE
C                   Eval      *INLR = *ON
 *------------------------------------------------------------------------------
 *  SQL SUBROUTINES
 *------------------------------------------------------------------------------
C     SQL_OPEN      BEGSR
 
C                   EVAL      #SQLSTR = 'SELECT CITYNM ' +
C                               ' FROM SMaster1' +
C                               ' Where CITYNM = ' + #INPARM
 
C/EXEC SQL
C+  PREPARE S1 FROM :#SQLSTR
C/END-EXEC
 
C/EXEC SQL
C+  DECLARE C1 DYNAMIC SCROLL CURSOR FOR S1
C/END-EXEC
 
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
 
C                   ENDSR
 *-----------------------------------------------------------------------------
C     SQL_READ      BEGSR
C
C/EXEC SQL
C+ FETCH C1 INTO :CITY
C/END-EXEC
C
C                   ENDSR
 *-----------------------------------------------------------------------------
C     SQL_CLOSE     BEGSR
C
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC
C
C                   ENDSR

Open in new window

0
 
LVL 14

Expert Comment

by:daveslater
ID: 21958286
Hi
I think I have what you need. It is called "return result set array " and I will post an example tomorrow - just need to find the code.

It is simular to murphey2 last post  but will return an array and not a result set.

Dave


0
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 1000 total points
ID: 21962493
AS/400 RPG program example
name: SQLRS1

Register storred procedure

in excel copy the code into a module, you will need to add
"Microsoft ActiveX Data Objects 2.x Library"
using Tools/References in VB editor


Enjoy


Dave
AS/400 RPG program example
name: SQLRS1 
H DftActGrp(*No)     
H ActGrp(*Caller)                            
*********************************************************************************************   
D MainLine        Pr                  EXTPGM('SQLRS1')                                            
D Parm                          20                                                                
D MainLine        PI                                                                              
D Parm                          20                                                                
                                                                                                  
 **********************************************************************************************   
 ** Result Set - SQL can only return a MODS and not an Array                                      
D ResultSet       DS                  Occurs(30)                                                  
D  Index                         5  0                                                             
D  Text                         30                                                                
 ** Array - number of entries as the MODS                                                         
D Array           s                   Like(ResultSet) based(ArrayPtr)                             
d                                     Dim(%elem(ResultSet))                                       
 ** Link array to MODS                                                                            
D ArrayPtr        S               *   inz(%addr(ResultSet))                                       
 ** Working DS the same as the result set                                                         
D MyDS            ds                  Likeds(ResultSet)                                        
 **********************************************************************************************
 ** Incluse the sql stuff                                                                      
C/EXEC SQL INCLUDE SQLCA                                                                       
c/END-EXEC                                                                                     
 **********************************************************************************************
 ** Always Exit                                                                                
C                   EVAL      *INLR = *ON                                                      
 ** Set the array                                                                              
C                   Do        30            I                 5 0                              
c                   Eval      Myds.Index = i                                                   
c                   Eval      Myds.Text = parm + ' ' + %editc(I:'L')                           
c                   Eval      Array(i) = MyDs                                                  
C                   Enddo                                                                      
 ** MODS element must bet set to 1                                                             
C                   eval      %occur(ResultSet) = 1                                            
 ** Set retun codes & Number of elements                                                       
C                   EVAL      SQLCOD=0                                                         
 ** I will set I to 20 so only 20 rows are returned not the 30 that have been filled           
C                   eval      I=20                                                             
 ** Return the Data                                        
C/EXEC SQL                                                 
C+  SET RESULT SETS ARRAY :ResultSet FOR :i Rows           
C/END-EXEC                                                 
 **                                                        
 ** Set retun codes & Number of elements                   
C                   EVAL      SQLCOD=0                     
 **                                                        
c                   Return                                 
 
 
 
-------------------------------------------------------------------------
 
 
Create stored procedure
CREATE PROCEDURE SQLRS1(IN PARM CHAR ( 20)) LANGUAGE RPGLE              
SPECIFIC SQLRS1 NOT DETERMINISTIC READS SQL DATA CALLED ON NULL         
INPUT EXTERNAL NAME QGPL/SQLRS1 PARAMETER STYLE GENERAL              
 
 
 
-----------------------------------------------------------------------
VB code in excel
 
Global con As New ADODB.Connection
Global rs As ADODB.Recordset
Global cmd As New ADODB.Command
Global prm1 As ADODB.Parameter
Global prm2 As ADODB.Parameter
Global No400 As Boolean
 
Public Sub GetData()
 
 
con.Open "PROVIDER=IBMDA400;Data Source=999.999.999.999;USER ID= ;PASSWORD= ;"
Set cmd.ActiveConnection = con
 
cmd.CommandText = "{call QGPL.SQLRS1(?)}"
cmd.Prepared = True
 
Set prm1 = cmd.CreateParameter("Cono", adChar, adParamInput, 20)
cmd.Parameters.Append prm1
 
 
cmd.Parameters(0) = "David S"
Set rs = cmd.Execute
 
 
Sheet1.Range("A1").Offset(0, 0).CopyFromRecordset rs
If rs.EOF = True Then
    MsgBox "OK"
Else
    MsgBox "What!!"
End If
            
End
 
End Sub

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configure Web Service (server application) I. Configure security for Web Services methods First, we need to protect Session bean which implements the service: 1. Open EJB deployment descriptor (ejb-jar.xml) in the EJB project that contains you…
This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question