Re: Censoring SQL access -- ideas?

[CRPence <CRPbottle@xxxxxxxxx>]

On 3/25/11 4:40 PM, James Lampert wrote:
> I'm (FINALLY) reading through Milligan & Andruchuk's 2007 System i
> NEWS article on UDTFs.
>
> I just (I think) successfully defined a UDTF, STATECUST, on the
> VIEWCUS sample database that we supply with QuestView.
>
> STATECUST shows up as a *SRVPGM.
> Object    Type    Library Attribute Text
> STATECUST *SRVPGM MERCURY CLE       SQL FUNCTION STATECUST
>
> But I'm having trouble calling it. Here is what happens in STRSQL:
>
>  SELECT * FROM TABLE(STATECUST('CA')) FOO
>  STATECUST in *LIBL type *N not found.
>
>  SELECT * FROM TABLE(MERCURY/STATECUST('CA')) FOO
>  STATECUST in MERCURY type *N not found.
>
> And in SQuirreL SQL from my WinDoze box, I have no idea how to get it
> to see objects in MERCURY.

  What was the CREATE FUNCTION statement, other than SQL-routine-body
and RETURNS clause?

  The "not found" is presumably the condition described in the second 
level text as "function by that name *with the correct parameter(s)* is 
not found" versus simply "no function by that name is found" which might 
be mis-inferred from the first level text.

  If the parameter is declared as CHAR(2), then try:
     SELECT * FROM TABLE(STATECUST( cast('CA' as char(2)) )) FOO

  Or...

  Generally best to declare the parameter as VARCHAR [esp. if not IN 
parameter for a UDF vs UDTF] since [is it?] the "Promotion of data 
types" treats the literal [aka constant] 'CA' as VARCHAR, so "function 
resolution" attempts to find the STATECUST table function with the first 
parameter as VARCHAR versus the STATECUST Table function with the first 
parameter as CHAR.

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzcreatef.htm
"Based on the rules of promotion, using the following data types is 
recommended: <<SNIP>>
    * VARCHAR instead of CHAR"
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzpromo.htm

Regards, Chuck