Re: Calling a stored procedure

Resubmit:

C'mon, any responses?  This works from Notes.  (Well, if the data and
programs reside on the same 400 as the Domino server.)  I just want to know
how to do it straight from RPG.

There is a good example at:
http://publib.boulder.ibm.com/pubs/html/as400/v4r5/ic2924/info/RZAIKCALLINGSPRESULTSETS.HTM

However can someone translate that ODBC stuff into RPGLE?


Sample Notes code currently executing subprocedure:
...
     txt="CALL " & plantdoc.bpcsprglib(0) & ".BOM240FILE('" &
Left(plantdoc.bpcsobjectlib(0)&String(10," "),10) & "', '" &
Left(doc.supplierinfofield(0)&String(15," "),15) & "', '" &
Left(plantdoc.BPCSFacility(0)&String(2," "),2) & "')"
Else
     txt="CALL " & plantdoc.bpcsprglib(0) & ".BOM220FILE('" &
Left(plantdoc.bpcsobjectlib(0)&String(10," "),10) & "', '" &
Left(doc.supplierinfofield(0)&String(15," "),15) & "', '" &
Left(plantdoc.BPCSFacility(0)&String(2," "),2) & "')"
End If
query.sql=txt
Set result.query=query
If Not result.Execute Then
     Messagebox result.GetExtendedErrorMessage,, result.GetErrorMessage
     doc.piupdate=5
     Call SendMailMsg(BPCSProfile,doc,"Could not execute  " & txt)
Else
     txt=""
     If result.isresultsetavailable Then
          result.nextrow
          pifile=result.getvalue(1)
          If Left(pifile,6)<>"*ERROR" And pifile<>"" Then
               txt="SELECT * FROM " & plantdoc.bpcsfilelib(0) & "." &
pifile
          Else
               doc.piupdate=6
               If reporttype>0 Then
                    Call SendMailMsg(BPCSProfile,doc,"BOM240FILE returned
parameter of '"+pifile+"'")
               Else
                    Call SendMailMsg(BPCSProfile,doc,"BOM220FILE returned
parameter of '"+pifile+"'")
               End If
               txt=""
               pifile=""
          End If
     Else
          doc.piupdate=6
          If reporttype>0 Then
               Call SendMailMsg(BPCSProfile,doc,"No parameters were
returned from BOM240FILE")
          Else
               Call SendMailMsg(BPCSProfile,doc,"No parameters were
returned from BOM220FILE")
          End If
     End If
     If txt<>"" Then
          query.sql=txt
          Set result.query=query
          If Not result.Execute Then
               Messagebox result.GetExtendedErrorMessage,,
result.GetErrorMessage
               doc.piupdate=7
               Call SendMailMsg(BPCSProfile,doc,"Could not execute  " &
txt)
          Else
               If result.isresultsetavailable Then


Rob Berendt

==================
Remember the Cole!


                                                                                
                                       
                    rob@dekko.com                                               
                                       
                    Sent by:                 To:     RPG400-L@midrange.com      
                                       
                    owner-rpg400-l@mi        cc:                                
                                       
                    drange.com               Subject:     Calling a stored 
procedure                                   
                                                                                
                                       
                                                                                
                                       
                    03/30/01 02:00 PM                                           
                                       
                    Please respond to                                           
                                       
                    RPG400-L                                                    
                                       
                                                                                
                                       
                                                                                
                                       





I have an SQLRPGLE program named BOM240FILE with the following:
...
D BOM240FILE      PR
D  UserLibrary                  10A
D  ParentItem                   15A
D  Facility                      2A

 /COPY ROUTINES/QRPGLESRC,SRVPGMCPY

D BOM240FILE      PI
D  UsrLib                       10A
D  Parent                       15A
D  KFaci                         2A
...
C/EXEC SQL
C+ SET RESULT SETS ARRAY :Parm1 FOR :Row1 ROWS
C/END-EXEC
...


I plan on calling it from another program with:
C/EXEC SQL
C+ CONNECT TO :DATABASE
C/END-EXEC

C/EXEC SQL
C+ CALL GDIDIVO/BOM240FILE (:UsrLib, :Parent, :KFaci)
C/END-EXEC

How do I get back the :Parm1?


Rob Berendt

==================
Remember the Cole!

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---



+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---