Solved

how to get return array from rpg program ?

Posted on 2008-06-22
10
2,171 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 16

Expert Comment

by:theo kouwenhoven
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:vikasbapat
Comment Utility
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 250 total points
Comment Utility
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
Comment Utility
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 250 total points
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
This exercise is about for the following scenario: Dmgr and One node with 2 application server. Each application server contains it owns application. Application server name as follows server1 contains app1 server2 contains app1 Prereq…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now