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.