Dan,
Is the calling program or SQL source using the library list (*SYS naming) or the SQL path (QSYS/QSYS2/RDB Library with *SQL naming) to find the UDF?
Paul Morgan
Principal Programmer Analyst
IT Supply Chain/Replenishment
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Thursday, August 04, 2011 3:16 PM
To: Midrange Systems Technical Discussion
Subject: SQL UDF not found MADNESS
I'm at my wit's end. This was working the last time I used it a few months
ago. Now I'm getting SQL0204 "<function> in *LIBL type *N not found." The
library containing both the service program and the function is at the top
of the library list. (FWIW, we're at V6R1.)
The function is called TRUEDATE. It's found in QSYS2/SYSFUNCTIONS. The
service program by the same name also exists in the library list. After
first getting this message, I deleted the service program and module
objects, dropped the function (DROP TRUEDATE), and recreated all from
scratch. Same result. Hair accumulating on floor.
(For the curious, this function takes in four 2-digit values (CC, YY, MM, &
DD) and returns a date. The legacy tables here are rife with dates
represented by four 2-digit fields,)
Following is the source, with the the command used to create the service
program shown as a comment in the RPGLE source, and the SQL statement to
create the UDF at the very end:
TRUEDATEPR TrueDate f(): Prototype
d TrueDate pr d
d p_CenturyIn 2p 0
d p_YearIn 2p 0
d p_MonthIn 2p 0
d p_DayIn 2p 0
* The null indicator fields below mandate option PARAMETER STYLE GENERAL
WITH NULLS
d pni_Input4 5i 0 Dim( 4 )
d pni_Result 5i 0
TRUEDATEM TrueDate f(): Module
* TrueDate - receives 4 parms (century, year, month, & day, all 2 digits)
* and returns an *ISO format date field
h Option( *SrcStmt : *NoDebugIO )
**#$%COM: DbgView( *All )
**#$%OBJ: *MODULE
**#$%PST: CRTSRVPGM SRVPGM(DANDEV/TRUEDATE) MODULE(DANDEV/TRUEDATEM) +
**#$%PST: EXPORT(*SRCFILE) SRCFILE(DANDEV/DANDEV) SRCMBR(*SRVPGM) +
**#$%PST: TEXT('TrueDate: Four 2-digit date fields in, date out') +
**#$%PST: AUT(*ALL)
*
* See SQL source member TRUEDATECF for the script to Create the Function
d/copy DANDEV/DANDEV,TrueDatePr
p TrueDate b export
d TrueDate pi d DatFmt( *ISO )
d p_CenturyIn 2p 0
d p_YearIn 2p 0
d p_MonthIn 2p 0
d p_DayIn 2p 0
d pni_Input4 5i 0 Dim( 4 )
d pni_Result 5i 0
d ds
d DateIn 8s 0
d inCentury 2s 0 Overlay( DateIn : 1 )
d inYear 2s 0 Overlay( DateIn : 3 )
d inMonth 2s 0 Overlay( DateIn : 5 )
d inDay 2s 0 Overlay( DateIn : 7 )
d DateOut s d Inz DatFmt( *ISO )
/free
inCentury = p_CenturyIn ;
inYear = p_YearIn ;
inMonth = p_MonthIn ;
inDay = p_DayIn ;
Test(DE) *iso DateIn ;
If %error ;
// Return *Null ; <== doesn't like; *NULL not same type as prototype
pni_Result = -1 ; // Null Indicator is set ON
Else ;
DateOut = %date( DateIn : *iso ) ;
pni_Result = 0 ; // Null Indicator is set OFF
Endif ;
*inLR = *off ;
Return DateOut ;
/end-free
p e
TRUEDATE Export source for TrueDate
STRPGMEXP SIGNATURE('TrueDate20110804')
EXPORT SYMBOL(TRUEDATE)
ENDPGMEXP
The SQL statement to create the function:
CREATE FUNCTION DANDEV/TRUEDATE (CC DEC(2,0), YY DEC(2,0),
MM DEC(2,0), DD DEC(2,0) )
RETURNS DATE
LANGUAGE RPGLE
PARAMETER STYLE GENERAL WITH NULLS
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
EXTERNAL NAME 'DANDEV/TRUEDATE(TRUEDATE)'
midrange.com
