|
midrange.com
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
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
