Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to get return array from rpg program ?

Posted on 2008-06-22
10
Medium Priority
?
2,296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ideal Solution for Multi-Display Applications

Check out ATEN’s VS1912 12-Port DP Video Wall Media Player at InfoComm 2017. Kerri describes how easy it is to design creative video walls in asymmetric layouts and schedule detailed playlists ahead of time with its advanced scheduling feature.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Verbose logging is used to diagnose garbage collector problems. By default, -verbose:gc output is written to either native_stderr.log or native_stdout.log.   It is also possible to redirect the logs to a user-specified file. This article will de…
Upgrading Tomcat – There are a couple of methods to upgrade Tomcat is to use The Apache Installer is to download and unzip and run the services.bat remove|install Tomcat6 Because of the App that we are working with, we can only use Tomcat 6.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

721 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