Stored Procedures Problem

Does anyone have any comments on (or, ideally, a solution for) the attached
customer query?

My current suggested workaround is to use a C/400 program with SQL CLI
calls in it to call the remote stored procedure, instead of using embedded
SQL within RPG to do it. We haven't actually tried this yet but I reckon it
*should* work.

To be clear, this is not basically a Domino problem, Domino just happens to
be the platform that's trying to call all this.

You should also be aware that the reason we can't call the remote stored
procedure direct from Notes is that there are stored procedure output
parameters and the Domino LC LSX technology doesn't support these. We can't
use result sets instead because DRDA does not support returning result sets
from a stored procedure on a remote box. As you have probably guessed, this
has been a very long-running saga. I would also mention (gently) that the
whole setup works 100% perfectly on an NT Notes server integrated with the
remote AS/400 using ODBC, returning result sets from the remote stored
procedures with zero problem.

This is all extremely frustrating for the customer who is committed to
AS/400 but who is currently having to run his Web site on an NT box.

Mandy

******

We have Notes running on an AS/400, with Notes agents calling SQLRPG
programs, running on the same machine, via stored procedures.

A couple of the SQLRPG programs themselves call stored procedures. These
SQLRPGs were created to point to a database on another AS/400, using the
RDB keyword on the CRTSQLRPG command.

When I call these SQLRPGs from the command line on the Notes AS/400, they
automatically connect to the remote database and call the procedures on
that box, as required & expected.

When these SQLRPGs are called from a Notes agent via a local stored
procedure, they attempt to call the 'remote' stored procedures on the Notes
AS/400, not on the remote one as they should. They appear to make no
attempt to connect to the remote database. When called this way, they run
in a QSQSRVR prestart job in QSYSWRK. I have debugged them running this
way, and there are no error messages in the QSQSRVR job log, other than the
(obvious) failure to find the stored procedure (because it's not on this
box, it's on the remote one).

As a test, I created the procedure on the Notes AS/400, but with incorrect
parameters. It then gave an 'incorrect parameters' error message, telling
me that it is trying to call the procedure on the Notes AS/400. As a
further test, I added a SET CONNECTION to the SQLRPG, specifying the remote
db. It then gave a 'connection not found' error message.

----------------------------------------------------------------------
----------------------------------------------------------------------
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 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
+---