RE: How do I create a UDF?

["Neill Harper" <neill.harper@xxxxxxxx>]

If you are struggling to get a create function to work in an rpg program,
build the statement up in a string and do an execute immediate, if it is
valid sql it will work.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: 18 August 2009 18:22
To: Midrange Systems Technical Discussion
Subject: Re: How do I create a UDF?

you can do a create function in STRSQL.

However, STRSQL can't prompt it or guide you through the process.

The wizard in iSeries Navigator can however.

I've never tried to embed a create function in an RPG program, the SQL
reference does say the create function can be embedded, but I get the
same error you do about SQL being an invalid token.

Just stick the statement in a source member with type = SQL, then use
RUNSQLSTM to run it.

Charles


On Tue, Aug 18, 2009 at 11:35 AM, Koester,
Michael<mkoester@xxxxxxxxxxxxx> wrote:
> (If this would be more appropriate to the RPG or WDSC lists, let me
> know.)
>
>
>
> I noticed some articles that reference some very handy user defined
> functions that I'd like to incorporate and use in embedded SQL
> statements.  Having never created a UDF before, I thought I could copy
> some sample code and make it happen.  It appears that CREATE FUNCTION is
> not available to me.  Interactive SQL does not list FUNCTION as a
> supported argument to the CREATE verb.  Coding it into SQLRPGSRC makes
> the WDSCi syntax-checker mad (it doesn't like the "Language SQL"
> statement, among others), so I am wondering if I'm going about this all
> wrong, or if we don't have the necessary licensed programs on our
> machine, or ???
>
> The UDF I tried to create is:
>
> Create Function myLib/IsNumeric
>
> (@TestData VarChar(64))
>
> Returns Char(1)
>
> Deterministic
>
> Not Fenced
>
> Language SQL
>
> Set Option Commit=*None, UsrPrf=*Owner
>
> Begin
>
>  Declare @Double Double Not Null Default 0;
>
>  Declare Exit Handler For SQLException
>
>  Return 'N';
>
>  If @TestData Is Null Then
>
>  Return 'N';
>
>  End If;
>
>  Set @Double=Cast(@TestData As Double);
>
>  Return 'Y';
>
> End
>
> I didn't see anything that ran counter to the syntax in the WDSCi
> Reference.
>
> Running V5R4 and WDSCi 7.0.0.8  Been doing embedded SQL programming here
> for over a year.
>
> TIA.
> --Michael Koester
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.