|
midrange.com
Frank, I wondered where where SQL was registering these functions because I
never found an "object" out there for it. Thanks.
Here's what's in QSYS2/SYSROUTINE. I don't see anything out of the
ordinary:
SPECIFIC_SCHEMA ISSBDP
SPECIFIC_NAME ALPHAITEM
ROUTINE_SCHEMA ISSBDP
ROUTINE_NAME ALPHAITEM
ROUTINE_TYPE FUNCTION
ROUTINE_CREATED 2005-10-14-09.56.36.478000
ROUTINE_DEFINER ISSBDP
ROUTINE_BODY EXTERNAL
EXTERNAL_NAME ISSBDP/SQLUDF(ALPHAITEM)
EXTERNAL_LANGUAGE RPGLE
PARAMETER_STYLE GENERAL
IS_DETERMINISTIC YES
SQL_DATA_ACCESS NONE
SQL_PATH
PARM_SIGNATURE 0
NUMBER_OF_RESULTS 1
MAX_DYNAMIC_RESULT_S ETS
IN_PARMS 1
OUT_PARMS
INOUT_PARMS
PARSE_TREE
FUNCTION_ORIGIN E
FUNCTION_TYPE S
EXTERNAL_ACTION N
IS_NULL_CALL NO
SCRATCH_PAD
FINAL_CALL NO
PARALLELIZABLE YES
DBINFO NO
IS_USER_DEFINED_CAST NO
CARDINALITY
FENCED YES
COMMIT_ON_RETURN NO
IASP_NUMBER
NEW_SAVEPOINT_LEVEL NO
LAST_ALTERED 0001-01-01-00.00.00.000000
DEBUG_MODE 0
fkany@averittexpr
ess.com
Sent by: To
rpg400-l-bounces@ CN=RPG programming on the
midrange.com AS400/O=iSeries
<rpg400-l@xxxxxxxxxxxx>
cc
10/14/2005 10:20
AM Subject
Re: SQL UDF not found?
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Try checking the file QSYS2/SYSROUTINE to see if the function is named
correctly.
hth,
Frank
Ben_Pforsich@xxxxxxxxxxxx@midrange.com on 10/14/2005 09:07:19 AM
Please respond to RPG programming on the AS400 / iSeries
<rpg400-l@xxxxxxxxxxxx>
Sent by: rpg400-l-bounces+fkany=averittexpress.com@xxxxxxxxxxxx
To: rpg400-l@xxxxxxxxxxxx
cc:
Subject: SQL UDF not found?
I am trying to write a very simple SQL UDF from an RPGLE module in a
SRVPGM. I've registered the function ALPHAITEM which is same name as the
subprocedure exported in the module, but I am consistently getting the
error message "ALPHAITEM in *LIBL type *N not found." when I try to execute
the following SQL from STRSQL and from an SQLRPGLE program:
select ALPHAITEM(s20prd)
from rsp200 where s20cmp = 'B1'
Below is the code with the steps I followed to create the service program
and register the SQL function. Any help would be greatly appreciated.
BTW, I have read the following article which was helpful in getting me
started:
http://faq.midrange.com/data/cache/185.html
<code>
h nomain
h debug
h option(*nodebugio : *nosrcstmt)
*************************************************************************
* Compile Instructions
*------------------------------------------------------------------------
* 1. Create RPGLE module from this source member:
* CRTRPGMOD MODULE(ISSBDP/ALPHAITEM) SRCFILE(ISSBDP/QRPGLESRC)
* SRCMBR(ALPHAITEM)
*
* 2. Create/update SQLUDF service program in ISSBDP with this module:
* CRTSRVPGM SRVPGM(ISSBDP/SQLUDF) MODULE(ISSBDP/ALPHAITEM)
* EXPORT(*ALL)
*
* 3. Register this function by executing the following in STRSQL:
* CREATE FUNCTION ISSBDP/ALPHAITEM(INT) RETURNS CHAR(15)
* LANGUAGE RPGLE
* DETERMINISTIC
* NO SQL
* RETURNS NULL ON NULL INPUT
* NO EXTERNAL ACTION ALLOW PARALLEL
* SIMPLE CALL
* EXTERNAL NAME 'ISSBDP/SQLUDF(ALPHAITEM)'
*************************************************************************
* Procedure prototypes
*------------------------------------------------------------------------
* AlphaItem function
* returns the alphanumeric representation of a numeric item so that
it
* contains at least 4-digits (ex: 23 -> '0023', 5603 -> '5603')
d AlphaItem pr 15a
d numItem 10p 0
*************************************************************************
* Procedure interfaces
*------------------------------------------------------------------------
p AlphaItem b export
*------------------------------------------------------------------------
d AlphaItem pi 15a
d numItem 10p 0
*------------------------------------------------------------------------
d Result s 15a
/free
select;
when numItem < 10;
Result = '000' + %char(numItem);
when numItem < 100;
Result = '00' + %char(numItem);
when numItem < 1000;
Result = '0' + %char(numItem);
other;
Result = %char(numItem);
endsl;
return Result;
/end-free
p AlphaItem e
</code>
Ben Pforsich
Programmer Analyst
Bob Evans Farms, Inc.
I/S Department
Columbus, Ohio
Ben_Pforsich@xxxxxxxxxxxx
--
This is the RPG programming on the AS400 / 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 AS400 / 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 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].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
