RE: Help with UDFs or stored procedures

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

As Vern says UDF is the answer.

You're probably best off writing it in your HLL of choice (i.e. RPG), making
it a common function in a service program and then in your RPG programs call
it as a regular function.
In SQL you simply need to create a UDF that maps to that service program
function and voila, it is now accessible in SQL as well.

Reason I say use it in RPG as regular service program function instead of
accessing the UDF via VALUES sql clause, is that you can avoid SQL
precompiler that way.  Precompiler can be a pain in the neck sometimes.

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, December 15, 2006 3:51 PM
To: Midrange Systems Technical Discussion
Subject: Re: Help with UDFs or stored procedures

Dan - other than the SQL Reference at InfoCenter - always go to 
InfoCenter first- the standard book for UDFs and SPs is located at 
http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

A stored procedure cannot be used in a SELECT - that is where a UDF 
shines. A UDF can be implemented with a service program - so you 
could have the same service program for both a UDF and for CALLP in 
your RPG/LE programs.

The other possibilitiy is using the SQL SET or VALUES statement in 
RPG to execute a UDF and have the result put into a local host variable.

Oh - be sure to use VARCHAR in the CREATE FUNCTION or CREATE 
PROCEDURE statement - this will let you use constants instead of 
field names in your call of either, because literals are converted to 
variable length values by SQL. Also, declare you incoming parameters 
as VARYING.

HTH
Vern

At 03:38 PM 12/15/2006, you wrote:

> (Cross-posted to midrange-l and rpg400-l)
>
> Our legacy database files are littered with dates defined as four numeric
> fields, with a 2-digit century, 2-digit year, month, and day.  I must work
> with these dates in SQL and WrkQry, sometimes in RPG.  I would like to
> define either a UDF or stored procedure or *something* that would convert
> four input parameters into a date result.  It would be nice if I could use
> the same function in an SQL statement and in RPG code, but not a
> requirement.
>
> So, I need to read up on how to do this, and how to choose UDF vs. stored
> procedure.  I searched the archives, saw a few examples of code, but did
not
> see any reference on how to call the UDF or stored procedure.
>
> Can anyone suggest good online references?
>
> TIA,
> Dan