Calling a stored procedure.

[rob@xxxxxxxxx]

Sorry to flood the lists, but I am not getting any response from the RPG
list.

I am having a problem calling a stored procedure from RPGLE.  This letter
is in three parts:  the second response, the first response and the
original letter - all from me.  Any help?


*****************************
*****  SECOND RESPONSE  *****
*****************************

I am getting a little further.  I've created a program with many
statements, including the following:
...
 * Call stored procedure
C                   eval      stmt='CALL GDIDIVO/BOM240FILE ("' +
C                                  UsrLib + '", "' +
C                                  Parent + '", "' +
C                                  KFaci + '")'
C                   callb     SQLExecDirect
C                   parm                    hstmt
C                   parm                    stmt
C                   parm                    SQL_NTS
...

I realize I am probably doing many things wrong here and I am open for
critique.
1)  I probably need to create a variable called SQLExecDirect and
initialize it to 'SQLExecDirect', or
2)  Instead of CALLB maybe I should be using:  eval
rc=SQLExecDirect(hstmt:stmt:SQL_NTS)

I)  Where are the prototypes for these calls?  I searched QSYSINC/QRPGLESRC
and the closest hit was SQLCLI.  Teaser mentioned prototypes and
SQLExecDirect, and more, but didn't have the prototypes.
II)  Where do I find SQL_NTS?
III)  What do I bind to to get these procedures?

************************************
*****  END OF SECOND RESPONSE  *****
************************************



****************************
*****  FIRST RESPONSE  *****
****************************

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

***********************************
*****  END OF FIRST RESPONSE  *****
***********************************




****************************
*****  INITIAL LETTER  *****
****************************



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?

***********************************
*****  END OF INITIAL LETTER  *****
***********************************



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