Re: Creating a SQL UDF

I notice that the declaration of the function and the procedure in the
service program have different lengths. Function is 64k and procedure is
32k. Do you need it that big? Going to slow it down a lot copying back that
data.

I also notice the procedure is declared n but the function is declared.
char 1. I would make it char and make it Y or N.

Make sure that you do not have a previous declaration out there that it is
finding. Do a DROP FUNCTION several times to make sure there is not
something out there.

Also, did you do a DSPSRVPGM program and see what the service program is
exporting? Is it exporting your function?


On Thu, Sep 26, 2013 at 1:14 PM, Robert Mullis <rmullis@xxxxxxxxxxxxxxxx>wrote:

I tried to adapt the iDate example Alan, but unfortunately with the same
results.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Thursday, September 26, 2013 2:58 PM
To: RPG programming on the IBM i / System
Subject: RE: Creating a SQL UDF

If you goto www.think400.dk/downloads.htm and look for iDate you will
find a complete example of a UDF.
On Sep 26, 2013 12:48 PM, "Robert Mullis" <rmullis@xxxxxxxxxxxxxxxx>
wrote:

Glenn,

WORKLB is in my library list when I signon and in STRSQL the naming
convention is set to *SYS.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Thursday, September 26, 2013 2:31 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: Creating a SQL UDF

Hi Robert,

How do you know WORKLB is in your library list?
In your exec sql set option, do you have "naming=*sys" ?

Glenn Gundermann

I have a service program (SYSRVUTL) with the following procedure in

it:

D Remove_Non_AlphaNumeric...

D pr 65535a varying opdesc

D inData 65535a const varying

D inCompress n const



I want to create a UDF to use this procedure in embedded SQL, but so

far I have been unsuccessful. I have tried numerous times creating
the function with slight variations and each time it appears to
create. But when I try and run it in an SQL Select, it comes back
and

tells me the function is not found in *LIBL. I now it was created
in a library that is in my library list.



This was my last attempt at creating:



CREATE FUNCTION WORKLB/REMOVE_NON_ALPHANUMERIC

(INDATA VARCHAR(32740), COMPRESS CHAR(1))

RETURNS VARCHAR(32740)

LANGUAGE RPGLE

DETERMINISTIC

NO SQL

RETURNS NULL ON NULL INPUT

EXTERNAL NAME 'WORKLB/SYSRVUTL(REMOVE_NON_ALPHANUMERIC)'

PARAMETER STYLE GENERAL



Anyone have any suggestions? I am sure it is simple, but this is my

first attempt at creating a UDF.



Thanks,

Robert J. Mullis

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx

To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.