RE: UDF problem with alpha values

A-ha!

cheers
Colin.W

http://as400blog.blogspot.com
 
Extension   5800
Direct dial   0870 429 5800


-----Original Message-----
From: vhamberg@xxxxxxxxxxx [mailto:vhamberg@xxxxxxxxxxx] 
Sent: 07 December 2004 20:02
To: Midrange Systems Technical Discussion
Subject: Re: UDF problem with alpha values


Character literals are escalated to VARCHARs, so declare your parameters as
VARCHAR, not CHAR.

-------------- Original message -------------- 

> 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. 
> 
--
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.
This e-mail has been sent by a company of Bertram Group Ltd, whose registered 
office is 1 Broadland Business Park, Norwich, NR7 0WF. 
This message, and any attachments, are intended solely for the addressee and 
may contain privileged or confidential information.  If you are not the 
intended recipient, any disclosure, copying, distribution or any action taken 
or omitted to be taken in reliance on it, is prohibited and may be unlawful.  
If you believe that you have received this email in error, please contact the 
sender immediately. Opinions, conclusions and statements of intent in this 
e-mail are those of the sender and will not bind a Bertram Group Ltd company 
unless confirmed in writing by a director independently of this message. 
Although we have taken steps to ensure that this email and any attachments are 
free from any virus, we advise that in keeping with good computing practice the 
recipient should ensure they are actually virus free.