Re: Stored Procedures -- RPG400-L

I second Alan's response....your other programmer is completely incorrect.

You can return a set of records, aka a "result set" using embedded SQL
or an SQL defined procedure.

When using embedded SQL, you can even build the result set from an
array instead of an SQL select. The only downside, at least with RPG,
is you need to know the number of records that will be returned.

So to summarize,
- An SQL defined stored procedure can return a result set
- An external program, identified as an external stored procedure,
that uses an SQL select can return a result set.
- An external program, identified as an external stored procedure,
that uses native i/o or no i/o can use the SET RESULTS statement to
return a result set from an array.

HTH,
Charles

On Tue, Mar 17, 2009 at 3:42 PM, <DLee@xxxxxxxx> wrote:

Hello

In talking to one of my fellow programmers, he tells me stored procedures
can't be used to retreive data file data from th iSeries to a network
server. He says they can only be used to pass parameter type data back a
forth.

To me the term SQL means I can select and pull data files back a forth. In
different examples I see on the web, it looks like data can be moved back
and forth.
Since I don't work with the pc world much, only iSeries, I'm not real
expert in that area.

I have an application coming up where I where I was planning to use stored
procedures to allow a C# programmer to retreive database data from the
iSeries, so I'm kind of interested in wanting this to work.
What's the real scoop?

Appreciate any help.

Darrell Lee
Information Technology
Extension 17127

rpg400-l-request@xxxxxxxxxxxx
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
03/17/2009 12:01 PM
Please respond to
rpg400-l@xxxxxxxxxxxx

To
rpg400-l@xxxxxxxxxxxx
cc

Subject
RPG400-L Digest, Vol 8, Issue 198

Send RPG400-L mailing list submissions to
rpg400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
http://lists.midrange.com/mailman/listinfo/rpg400-l
or, via email, send a message with subject or body 'help' to
rpg400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
rpg400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of RPG400-L digest..."

*** NOTE: When replying to this digest message, PLEASE remove all text
unrelated to your reply and change the subject line so it is meaningful.

Today's Topics:

1. Re: New parameter needed for a subprocedure (rob@xxxxxxxxx)

----------------------------------------------------------------------

message: 1
date: Tue, 17 Mar 2009 10:54:18 -0400
from: rob@xxxxxxxxx
subject: Re: New parameter needed for a subprocedure

If it makes reasonable sense to me to use a return variable, then I'll use

a return variable. This allows me to:
- use it in a expression
- make an SQL UDF out of that expression, if needed.
http://faq.midrange.com/data/cache/185.html

I see a lot of stuff (even here) still created as separate program calls.
Where there are multiple parameters and only one of them is expected to be

returned. I say make it a subprocedure with a return variable. Then, if
you have the dinosaur that will only use a subprocedure at gunpoint then
simply wrap the subprocedure with the separate called program and they can

use that. It shouldn't be the other way around.

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From:
Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
To:
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date:
03/16/2009 02:55 PM
Subject:
Re: New parameter needed for a subprocedure
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx

Kurt Anderson wrote:

I just want to address the comment about output parameters being a
bad design technique. It's good design. Ok, that's my opinion.
Output parameters are passed by reference (16 bytes).

FWIW, I completely agree with you. Using parameters for output is
elegant, simple, performs well, etc.

There are times when I'll use a return value for output data...
especially when I'm writing a routine explicitly designed to be used in
an expression. For example, a routine that takes a number from 1-12,
and returns a month name January-December -- that's a good place to use
the return value.

But, when the output isn't meant to be used in an expression, I just
return a success/fail flag.

I guess it comes down to personal choice and style, so long as people
err on the side of readability. I won't say not using output
parameters is bad design, but I don't agree that using them is
either.

Amen. They're all tools in the toolbox. Understand them, and use the
right tool for the job.

I wouldn't mind hearing the justification of why using output
parameters is bad.

I've heard three arguments for this so far (I don't agree with any of
them)

1) It makes it clearer which data is input and which data is output,
enforcing that input parameters are only used for input, and output
parameters are only used for output.

2) All routines should do only one thing. Since there's only one return
value, using the return value for output enforces that the routine does
only one thing. If you need to return something else, make a different
routine.

3) Modern languages, like Java, cannot return output through their
parameter list. If you want your RPG code to be modern, you shouldn't
return data through the parameter list, either.

Those are the arguments I've heard. I don't agree with them. Here are
my responses to them:

1) This argument makes a good point, however, if all input parameters
are defined with CONST or VALUE as they should be, then it should be
easy enough to see which ones are input/output without returning
everything via the return value.

The performance problems of the return value, and the awkwardness of
having to create data structures for simple things like returning two
values is a bigger and more important issue, than clearly delineating
input vs output.

2) Unfortunately, "one thing" is really hard to define in exact terms.
I don't think "one thing" was really meant to equate to "one parameter"
or "one variable".

There are certain things that are naturally computed at the same time.
For example, retrieving a customer's address... does it make sense to
CHAIN/SELECT a new record for the customer's name, then again for the
street address, and again for the city, and again for the
state/province, and again for the postal code, etc, just because you
wanted each piece of information returned separately? That's not good
for performance to re-get the record each time, plus these things will
almost always be retrieved together. What's the point of making the
caller call 5 routines instead of 1?

As soon as you return the data in a data structure to get all of the
fields at once, you've broken your own "one thing" rule. I agree that a
routine should do "one thing", but I don't agree that it should only
return one output variable. If you're going to have mutliple output
variables, what's the point of restricting to the return value?

3) All programming languages have their quirks. The best policy is to
take advantage of the best features of each, rather than restricting
yourself to only using techniques that are available in all languages.

I find it hard to believe that the way you pass parameters will make
your code more or less "modern". Instead, do what's practical in the
language you're working in, rather than making things more difficult in
the name of "being more like Java."
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

------------------------------

--
This is the RPG programming on the IBM i / System i (RPG400-L) digest list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

End of RPG400-L Digest, Vol 8, Issue 198
****************************************

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.