RE: Stored Procedure calls from Cognos Impromptu

["Michel Sabourin" <msabourin@xxxxxxxxxxxx>]

Hi Gord,

I don't know if you got an answer yet, but here's what I
understand/know:

1) When you want a stored proc to return a result set, you must tell it
to do so, both in the SQL interface :

(CREATE PROCEDURE USERAEXTTEST ... RESULT SETS   1  ... LANGUAGE
RPGLE...)

and in the SQLRPGLE program :

c/exec sql
c+ declare c1 cursor for
c+ select ...
c/end-exec

c/exec sql
c+ open c1
c/end-exec

c/exec sql
c+ set result sets cursor c1
c/end-exec

2) when you call a stored proc from STRSQL interface, you don't get to
see the result set because this native interface does not support it.
You must use a interface which can deal with result sets, such as the
one bundled with Operation Navigator.

We used to use stored proc a lot for fetching data from the database
until we realized that current drivers (System Object, Easycom) on the
market are not optimized for this usage.  They work better with dynamic
SQL requests.  Nevertheless, we still use stored proc for inserting,
updating, and deleting records, anything but data fetching.

HTH


Michel Sabourin
Cascades SA
msabourin@cascades-europe.com

>>> GRoyle@cott.com Friday 4 October 2002 20:57:42 >>>
My thanks to Pete Hall for getting us past the first stumbling block
and
configuring ODBC to allow procedure calls. Now we're at the next stp
and we
can't get the results set to come back to us. In fact, we can't get it
to
come back to STRSQL even though we can see the results set being
generated
in debug. I've added all of the code below.

Can anyone help?

Gord Royle

The sample AS/400 RPGILE program is as follows.

fiwil01    if   e           k disk
di                s              3  0
dinvent           ds                  occurs(30)
dITEM                           15
DNUMBER                          5
c     *entry        plist
c                   parm                    xprod            15
c                   parm                    xNUM              5
c                   eval      i=0
c     *loval        setll     iwil01
c                   read      iwil01
c                   dow       not(%eof)
c                   if        wprod=xprod
c                   eval      i=i+1
c     i             occur     invent
c                   move      wprod         ITEM

The program should accept parameters from via the ODBC call and return
the
contents of the data structure as the result set.

We is compiled using the statement.

CRTSQLRPGI OBJ(USERA/EXTTEST) OUTOUT(*PRINT) DBGVIEW(*SOURCE)

We defined the program as a procedure to the Data Base.

CREATE PROCEDURE USERAEXTTEST LANGUAGE RPGLE SPECIFIC
USERA/EXTTEST NOT DETERMINISTIC CONTAINS SQL EXTERNAL NAME
USERA/EXTTEST PARAMETER STYLE SQL

We called the procedure from STRSQL and from Impromptu.

We don't get the results.
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.