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.


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

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)


Not Fenced

Language SQL

Set Option Commit=*None, UsrPrf=*Owner


 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';


I didn't see anything that ran counter to the syntax in the WDSCi

Running V5R4 and WDSCi  Been doing embedded SQL programming here
for over a year.

--Michael Koester

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].