Hello Robert,
By the way... how did you guys know this? The documentation does not
mention anything about staying away from srvpgm's with procs that return a
value. Was I missing it?
Heh... I don't think it's reasonable for a reference manual to include
everything that an SQL statement DOES NOT do. It can only list what it
does... listing everything it doesn't do would be kinda tedious. :)
Here's the SQL reference manual for the CREATE PROCEDURE statement:
http://tinyurl.com/2da5rc
In the CREATE PROCEDURE statement you posted in your original message,
you had 'parameter style SQL'. If you scroll down in the documentation
(i.e. the link, above) you'll see that it explains what 'parameter style
SQL' consists of. It's important to understand that which parameters
are/are not required in your program is entirely dependent on which
parameter style you're using.
The other parameter styles are explained as well -- and none of them
include a return value as one of the parameters detailed (which makes
sense, since the syntax of a procedure call (CALL XXXX()) does not
provide a place to return a value. So even if you could return it,
you'd have no way to use that return value in an SQL statement. (A UDF,
by contrast, can be used in situations where it's possible to set
something equal to it's return value.)
FWIW, when I write ILE RPG service programs and wrap them with a stored
procedure, I generally create the regular procedure with parameters and
return values that make it as easy and useful for ILE RPG programs as
possible.
Then, I create another procedure that acts as a "wrapper" or "interface"
for stored procedure usage. This wrapper generally changes the
parameters around a little bit to conform to the stored procedure
parameter style that I'm using. I also like to have any of the output
data from the procedure be returned as part of a result set (instead of
using parameters). Even if it's just a 1-row result set. I like that
method because a result set carries meta-data for the columns it
returns.... i.e. it has the column name, data type, length, etc, all
embedded in the result set itself. This makes it easier for callers
written in languages like C, Java, PHP, etc to be able to get
information about the columns returned without having to hard-code the
field definitions into their source.
When you want to have a return value to indicate success or failure, it
might make sense to use parameter style SQL, and have your wrapper
receive the return value and use it to set the SQLSTATE parameter that's
provided by parameter style SQL. That way, SQL itself will know when an
error has occurred, and the same error handling procedures used for any
other SQL statement can be used to handle errors returned by your stored
procedure.
However, if that seems to complicated, you can always just have an
output field in your result set for "error number" and "error message".
and the caller can check for those.
But.... because the parameter styles and error handling between stored
procedures and ILE procedures ARE so different, I always code a wrapper
around my stored procedures. That way, it's the best of both worlds
for both ILE callers and non-ILE callers (who are the ones using the
stored procedures.)
midrange.com
