RE: UDF problem with alpha values -- MIDRANGE-L

There was another FAQ that stated that system defaults literals to VARCHAR
data type and if your funcAlpha expects CHAR, system would mismatch function
signature, stating that it could not find that UDF.
To fix it, either typecast your literal (i.e. VARCHAR('this will work')) or
change your funcAlpha to expect VARCHAR as second parm.  Or perhaps create
another funcAlpha with VARCHAR signature that simply calls the original
funcAlpha casting 2nd parm to CHAR.
If you search the archive you'll find more details on this.

Elvis

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Antonio
Fernandez-Vicenti
Sent: Tuesday, December 07, 2004 2:00 PM
To: Midrange Systems Technical Discussion
Subject: UDF problem with alpha values

We have already created/used quite a number of UDF's. Most of them in SQL.
No problems so far.
Now we wrote a new UDF and had some problems.

1- Suppose you write a  SQL UDF function "funcAdd"  that will add two 
numeric fields, so you can write later on:
 "Select FldNum1, FldNum2, funcAdd(FldNum1, FldNum2) ...   from myFile ..."
This works OK
  If instead of using "real fields", I substitute one of the fields by a 
numeric constant
 "Select FldNum1, FldNum2, funcAdd(FldNum1, 100) ...   from myFile ..."
this works also OK, it will return the sum of FldNum1 plus the 
constant(literal) value of 100.

2- What about alphanumeric values?
We wrote a SQL UDF function "funcAlpha" that should work with several 
fields, some of them alphanumeric:
Let me simplify it as being called with just two alpha fields:
 "Select FldAlpha1, FldAlpha2, funcAlpha(FldAlpha1, FldAlpha2) ...   
from myFile ..."
This also works fine, BUT...
  If instead of using "real fields", I substitute one of the fields by 
an alphanumeric constant
 "Select FldAlpha1, FldAlpha2, funcAlpha(FldAlpha1, 'XXXX' ) ...   from 
myFile ..."
then we get a message saying "Object funcAlpha of type *N not found"... !!!

First we thought of some typo error. No: same Select with a second 
"real" field, it works.
We tried changing "SQL path", "Current path", ... etc with no changes at 
all.

Since, as mentioned above, it works if fields or constants are numeric, 
then I thought of one of the very frequent FAQs already commented on 
this list very often, in relation to alphanumeric parameters being 
passed as garbage to a CL pgm from the commmand line or from SBMJOB... 
depending on how long parameters were defined... This has been commented 
on so often!

So we tried with numeric constants. As mentioned above, it worked!!!
So I thought of passing fields as 32 char... Tried in different ways, 
with same failure always!

Talking about functions resolution, the DB2 SQL Ref. manual mentions:
"each function is uniquely identified by its
function signature, which is its schema name, function name, the number of
parameters, and the data types of the parameters..."

I suspect the system is mapping the alpha literal as something different 
that the alpha field, no matter the length of the literal. In our case, 
FldAlpha2 was, originally 4 chars. We tried with 'XXXX' , also alpha 4, 
with either single or double quotes..., then rebuilt the UDF to use 
FldAlpha2 as well as the literal with 32 chars, ... Tried all 
combinations we could think of...

Since the function does not get started, we cannot debug it to try to 
find what it is getting instead of the alpha literal...

So, the question is, how are alphanumeric fields and/or constants mapped 
in a UDF?
TIA

-- 
Antonio Fernandez-Vicenti
afvaiv@xxxxxxxxxx

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.