• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2357
  • Last Modified:

how to get return array from rpg program ?

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
vikasbapat
Asked:
vikasbapat
  • 4
  • 2
  • 2
2 Solutions
 
MurpheyApplication ConsultantCommented:
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
 
vikasbapatAuthor Commented:
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
 
MurpheyApplication ConsultantCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
MurpheyApplication ConsultantCommented:
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
 
vikasbapatAuthor Commented:
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
 
MurpheyApplication ConsultantCommented:
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
 
daveslaterCommented:
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
 
daveslaterCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now