Re: calling sql procedure from interactive SQL session

On 28 Nov 2012 14:30, Hockchai Lim wrote:

Anybody know if it is possible to call the stored procedure below
from interactive SQL session?

lrResetLoginProcessRequestSP
( in piUserId Varchar(256)
, in piRequestID Varchar(256)
, in piChgPwdAlso Char(1)
, in pinewEncPwd Binary(256)
, in pinewEncPwdLn int
, in pinewEncPwdVc Binary(16)
, in pinewASCEBC Char(1)
, in piForcePwd char(1)
, inout piErrFlag char(1)
, inout piErrID char(7)
, inout piErrMsg char(255)
)

Maybe. What release of the DB2 for i?

From STRSQL, before IBM i 7.1, probably sqlcode -469 aka SQL0469 would be the effect no matter what could be validly specified for the arguments for those INOUT parameters; e.g. SQL0090 for an invalid attempt to use Host Variable naming. Since 7.1 [and some revision level?] there is the option to use a global variable as the argument for each of the INOUT parameters. Perhaps also possible, by specifying DEFAULT for the argument, whenever the SQL parameter was defined with a DEFAULT?

IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Statements
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcallsta.htm
_i CALL i_
"The CALL statement calls a procedure.
_Invocation_

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

...

If a default is used for an INOUT parameter, then the default expression is used to initialized the parameter for input to the procedure. No value is returned for this parameter when the procedure exits.

...

Notes: ...

Global variables: A global variable may be specified and will be modified if used as a parameter that is INOUT or OUT.

..."