RE: Creating a SQL UDF -- RPG400-L

Alan,

The procedure is being exported, because I am using it in several
programs already. I took your suggestion and reduced the size of the
varying fields to 1000 and changed the indicator to a 1 character, but
unfortunately that didn't make any difference. The only other thing I
can think of is that the OPDESC on the procedure is creating problems.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Thursday, September 26, 2013 3:29 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: 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.

--
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.