Re: Call RPG UDF from ADO

[CRPence <CRPbottle@xxxxxxxxx>]

On 14 Nov 2012 09:09, Michael Naughton wrote:
> <<SNIP>>I know I can write a stored procedure that will pass
> back a parameter, and I can use the UDF in an SQL statement (as in
> 'SELECT field1, field2, MyFunc(field3) FROM MyTable"), but I'm
> curious if I can duplicate the functionality in RPGLE, where I can
> just say "Eval Var1 = MyFunc(Var2)".

  The SQL RPG can use any of the SQL SET, VALUES INTO, or SELECT INTO 
to evaluate the results of a User Defined Function (UDF) invocation into 
a Host [language] Variable, or the SQL RPG could set an INOUT or OUT 
variable using a SQL CALL to a Stored Procedure.

   exec sql
     set :var1 = MyFunc(:Var2) ;
   if sqlcode ... // test SQL return code(s); e.g. sqlstate

> It looks like ADO has a parameter type of ReturnValue, but I'm
> having no luck trying to code a command that uses it. I'm trying to
> do this out of a Lotus Domino agent, using LotusScript (very similar
> to Visual Basic).

  I have no /legitimate/ experience with ADO, and I do not recognize 
the "parameter type of ReturnValue", so I may be way off...  However, my 
guess is... That particular parameter type probably identifies a 
variable to be used for a PROCEDURE [INOUT or OUT argument], rather than 
the assigned value from a FUNCTION invocation [of which the arguments 
are not capable of being changed\returned].

  A client interface to a PROCEDURE routine is an effective equivalent 
to the SQL CALL whereby the variable as parameter can be set\updated.  I 
can imagine a capability of a client to invoke a UDF [coded in a manner 
that appears to be requested] directly, such that the client implements 
the effect, but I am unaware of that feature being available directly 
from the SQL methods provided to the typical dynamic client database 
connection requests.  That is, the client [language, e.g. Lotus Script] 
might be able to allow code to be written like 
ReturnValueVariable=MyFunc(AnotherVariable), but I would expect instead 
that the typical client language more likely requires something like 
SuccessIndicator=CallMethod(ProcedureName,ParameterList[ReturnValueVariable]). 
 I recall a few times having written an SQL PROCEDURE for the sole 
purpose of invoking a UDF to obtain the return value, to set a output 
capable parameter or to return the data as a row\result-set; had I been 
able to get the result directly from the UDF, that would have been 
easier, and perhaps I just overlooked the capability.?

  Not sure if the following link might be of any assistance:
http://www.ibm.com/developerworks/lotus/library/DB2-procedures/
"...
A DB2 stored procedure that has input and output parameters
..."