Re: Censoring SQL access -- ideas?

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