Re: UDF problem with alpha values

[vhamberg@xxxxxxxxxxx]

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