Re: AW: Creating a SQL UDF

I'm pretty sure that SQL will automatically promote from CHAR to VARCHAR.

However, it does not do the opposite (VARCHAR to CHAR)

Sorry Scott, but the inverse is true:

If varying character parameters are defined and fixed character parameter
values are passed instead, the function is found.
If character parameters are defined and varying character parameter values
or expressions or constant values are passed (both are interpreted as being
VARCHAR) the function is not found.

Just execute the following code:
CREATE or Replace FUNCTION YOURSCHEMA/TESTCHAR( MYPAR CHARACTER (10) )
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
CALLED ON NULL INPUT
Return 1;

Values(TestChar('A')); //Does not run because VARCHAR
is passed
Values( TestChar(Cast('A' as Char(10)))); //Runs since the parameter is
passed correctly


CREATE or Replace FUNCTION YOURSCHEMA/TESTVCHAR( MYPAR VarChar (10) )
RETURNS INTEGER
LANGUAGE SQL
CONTAINS SQL
CALLED ON NULL INPUT
Return 1;

Values(TestVChar('A')); //Runs since the parameter is
passed correctly
Values( TestVChar(Cast('A' as Char(10)))); //Runs since the CHAR parameter
is converted into VARCHAR

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Scott Klement
Gesendet: Thursday, 26.9 2013 22:04
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Creating a SQL UDF

Buck,

I'm pretty sure that SQL will automatically promote from CHAR to
VARCHAR. However, it does not do the opposite (VARCHAR to CHAR)

so your exact scenario of passing the first param as a char would not be a
problem. However, Robert's second parameter is a CHAR, so this would indeed
cause a problem if you passed a VARCHAR to it.

And when you type a literal (instead of providing an actual database
column) character literals are presumed to be VARCHAR.

So, that's the likely problem... Robert is passing a VARCHAR for the 2nd
parameter... so due to overloading the system can't find the function. That
would be my guess.





On 9/26/2013 2:02 PM, Buck Calabro wrote:

On 9/26/2013 2:26 PM, Robert Mullis wrote:

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.

So SQL allows something called overloading. One can create a function
named GetName that takes a numeric customer number and another GetName
in the same schema that takes a character item number and SQL tells
them apart by the parameter types the caller supplies.

I'm looking at the VARCHAR() parameter type and wondering if your
caller is supplying a VARCHAR() or a CHAR() type. They are different,
so if the caller were supplying a CHAR() the parameter signatures
would not match and therefore the REMOVE_NON_ALPHAMERIC(CHAR()) really
wouldn't be found in *LIBL.
--buck

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