Re: Directly accessing DB2/400 data from Domino across 400's -- DOMINO400

You're so right - I have been up against this one, big time.
If you want only a single row result set, you can get round it by:
a) changing the stored procedure to return output parameters
Now these are not supported by Domino (one step forward two steps back) so
b) write another RPG stored procedure which runs on the web server & which
simply calls the remote stored procedure & converts the output parameters
into a single row result set - Eureka! (It does work, I promise, although
you'll have to use some ticklish SQLRPG programming in your calling stored
procedure.)
If you want a multiple row result set, you will have to move the stored
procedure and the RPG programs onto the web server and access the database
via DDM, which is probably infeasible in a BPCS environment. But maybe you
only want a single row result set!
My perception is that this is not fixed for AS/400-AS/400 connections in
V5R1 but may be later.





"Walter Scanlan" <wscanlan@us.ibm.com> on 28/03/2001 16:54:25

Please respond to DOMINO400@midrange.com

To:   DOMINO400@midrange.com
cc:    (bcc: Mandy Shaw/Pacific/UK)
Subject:  Re: Directly accessing DB2/400 data from Domino across 400's





Rob,
     A result set is not supported when using a called stored proceedure
using DRDA.

It sounds like you are running into this limitation.

Walter Scanlan
Advisory Software Engineer
Domino For AS/400 Team Leader
Phone   (507)286-6088
Fax         (507)286-5028
Pager    (507)292-2985
Internet  WSCANLAN@US.IBM.COM


rob@dekko.com@midrange.com on 03/28/2001 09:17:52 AM

Please respond to DOMINO400@midrange.com

Sent by:  owner-domino400@midrange.com


To:   DOMINO400@midrange.com
cc:
Subject:  Directly accessing DB2/400 data from Domino across 400's




We had a Domino server, and DB2 data, on the same 400.  This is our
development situation.  In our live we have Domino on 1 400 and the DB2
data on the other.  We have a database, which accesses data in a BPCS
environment.  Runs a BPCS report, converts the report into a disk file and
sucks the data back into Domino.  All from pushing a Domino button.  Works
great as long as the DB2 and the Domino are on the same 400.  When we moved
the Domino database into production, but left the data in development, it
failed.  Sort of.  It still called the BPCS programs, ran the report,
converted the report back into a disk file.  But it's supposed to pass us
the name of the file back and we execute further script to bring the file.
This is how it passes the name of the file back:

C/EXEC SQL
C+ SET RESULT SETS ARRAY :Parm1 FOR :Row1 ROWS
C/END-EXEC

The difference we notice in the joblog is that when the Domino is ran from
production versus being on the same development 400 is that two additional
messages appear in the joblog.  They are:
SQL0104    Diagnostic              30    03/27/01   16:43:56   QSQXCUTE
QSYS        *STMT    QSQXCUTE     QSYS         *STMT
                                     From module . . . . . . . . :
QSQXCUTE
                                     From procedure  . . . . . . :
CLEANUP
                                     Statement . . . . . . . . . :   17293
                                     To module . . . . . . . . . :
QSQXCUTE
                                     To procedure  . . . . . . . :
CLEANUP
                                     Statement . . . . . . . . . :   17293
                                     Message . . . . :   Token
<END-OF-STATEMENT> was not valid. Valid tokens: (
                                       LIKE.
                                     Cause . . . . . :   A syntax error was
detected at token <END-OF-STATEMENT>.
                                       Token <END-OF-STATEMENT> is not a
valid token.  A partial list of valid
                                       tokens is ( LIKE.  This list assumes
that the statement is correct up to the
                                       token.  The error may be earlier in
the statement, but the syntax of the
                                       statement appears to be valid up to
this point. Recovery  . . . :   Do one
                                       or more of the following and try the
request again: -- Verify the SQL
                                       statement in the area of the token
<END-OF-STATEMENT>. Correct the
                                       statement.  The error could be a
missing comma or quotation mark, it could
                                       be a misspelled word, or it could be
related to the order of clauses. -- If
                                       the error token is
<END-OF-STATEMENT>, correct the SQL statement because it
                                       does not end with a valid clause.
...
SQL0464    Information             00    03/27/01   16:43:59   QSQCALL
QSYS        *STMT    QSQCALL      QSYS         *STMT
                                     From module . . . . . . . . :
QSQCALL
                                     From procedure  . . . . . . :
CLEANUP
                                     Statement . . . . . . . . . :   19038
                                     To module . . . . . . . . . :
QSQCALL
                                     To procedure  . . . . . . . :
CLEANUP
                                     Statement . . . . . . . . . :   19038
                                     Message . . . . :   Procedure
returned 1 result
                                       sets, which exceeds the defined
limit of 0.
                                     Cause . . . . . :   The stored
procedure                                in

completed normally. However, the stored
                                       procedure exceeded the defined limit
on the number of result sets a
                                       procedure can return. Only 0 result
sets are returned to the SQL program
                                       that issued the SQL CALL statement.
The possible causes are: -- The number
                                       of result sets is greater than the
maximum specified when the procedure was
                                       created. -- The stored procedure is
unable to return 1 result sets due to
                                       DRDA limitations imposed by the
client. Recovery  . . . :   The SQL
                                       statement is successful.  The
SQLWARN9 field of the SQLCA is set to 'Z'.


The Domino code looks something like:
Else
     servername=plantdoc.bpcssystemname(0)
End If

If Not session.isonserver Then
     servername=servername & "-RON"
End If

If Not conn.connectto(servername,
bpcsprofile.username(0),bpcsprofile.password(0)) Then
     Print "Could not connect to the AS/400 '" & servername & "'."
     doc.piupdate=4
     If servername="" Then
          Call SendMailMsg(BPCSProfile,doc,"The system name is not defined
in BPCS Library List for facility '" & doc.pifac(0) & "'")
     Else
          Call SendMailMsg(BPCSProfile,doc,"Could not connect to system '"
& servername & "'")
     End If
End If

If doc.piupdate(0)=0 Then
     Set query.connection=conn
     Do While doc.hasitem("PI_Report")
          Call Doc.RemoveItem("PI_Report")
     Loop
     pifile=""
     Set rtitem = New NotesRichTextItem( Doc, "PI_Report" )
     If reporttype>0 Then
     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


I have a pmr opened with Domino:  79730,500, but I'd thought I'd pick your
brains also.

Rob Berendt

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

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



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





----------------------------------------------------------------------
----------------------------------------------------------------------
Catalyst Solutions plc.  Registered No 2918101.
Registered @ Kingfisher House, Frimley Business Park, Frimley,
Surrey. GU16 5SG   U.K.

NOTICE:
This message is intended only for the named addressee(s) and may
contain confidential and/or privileged information. If you are not the
named addressee you should not disseminate, copy or take any action
or place any reliance on it. If you have received this message in error
please notify postmaster@catalyst-solutions.com and delete the message
and any attachments accompanying it immediately.
----------------------------------------------------------------------


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