Re: Censoring SQL access -- ideas?

[rob@xxxxxxxxx]

UDF.  Use a User Defined Function.  If you can do it in RPG I know that 
"YOU" can do it in SQL with a UDF.  I believe in you.

I used to have a step-by-step in taking a RPG subprocedure, exporting it 
to a service program and using the CREATE FUNCTION to publish that 
subprocedure as a UDF and then a sample select using that, and a sample 
view using that.  But I had them on the FAQ.midrange.com website which got 
shutdown.

Here's an old sample:

* Concepts: *
*  In the SQL you will notice a function called FAXNEWPHONEA, for alpha, *
*  and FAXNEWPHONEN for numeric.  This is how a function is created. *
*  1)  You create a service program.  These use the service program *
*      ROUTINES/SRVPGM. *
*  2)  You add a subprocedure to the service program.  In this case the *
*      functions are named the same as the subprocedures in the service *
*      program. *
*  3)  You register the subprocedure with SQL as a function.  Check out *
*      the member FAXUDF in the file QSQLSESS in ROUTINES.  Below is a *
*      sample: *
*       create function ROUTINES/FAXNEWPHONEA (CHAR (30)) *
*       returns CHAR (30) *
*       language rpgle *
*       deterministic *
*       no sql *
*       returns null on null input *
*       no external action allow parallel *
*       simple call *
*       external name 'ROUTINES/SRVPGM(FAXNEWPHONEA)' *

*      I used RUNSQLSTM to run this source member.  I ran this on GDISYS *
*      and on GDIHQ.  This functions accepts one parameter of char30 and *
*      returns one parameter of char30. *
*      I gotta tell you, creating an SQL function is a heck of a slick *
*      way to test a subprocedure.  For example I created a couple of *
*      subprocedures.  One to convert a phone number to a fax number. *
*      Another to get the current area code.  Now instead of writing a *
*      program to test these I can just run this SQL: *
*       SELECT keyfld,phone, *
*       FAXA(phone) as FaxNumber, *
*       FAXAREACODE(FAXA(phone)) as AreaCode *
*       FROM ROB/atest *
*      and I get: *
*       KEYFLD  PHONE                 FAXNUMBER          AREACODE *
*         A     2603473100            912603473100         260 *
*         B     (260) 347-3100        912603473100         260 *
*         C                    70560  70560 *
*         D     616.651.1234          916166511234         616 *
*         E           (260) 347-1222  912603471222         260 *
*         F     12T4549D999999        914549999999         454 *
*         G     12345678901234567890  916789012345         678 *
***************************************************************************

 * Return new phone number (alphanumeric) given old phone number. 
D FAXNEWPHONEA    PR            30A 
D OldPhone                      30A   CONST 

 
***************************************************************************
 * FAX API's *
 
***************************************************************************
P*-------------------------------------- 
P* Procedure name: FAXNEWPHONEA 
P* Purpose: 
P* Returns:        New phone number 
P* Parameter:      OldPhone => Old Phone Number 
P*-------------------------------------- 
P FAXNEWPHONEA    B                   EXPORT 
D FAXNEWPHONEA    PI            30A 
D OldPhone                      30A   CONST 
 
D* Local fields 
D rFAXNEWPHONEA   S             30A 
D OldAreaCode     s              3a 
D Exchange        s              3a 
D Number          s              4a 
D NewAreaCode     s                   like(OldAreaCode)
 
 /free 
  OldAreaCode=FAXAREACODE(FAXA(%trim(OldPhone))); 
  Select; 
  When OldAreaCode=*blanks; 
   rFAXNEWPHONEA=OldPhone; 
  Other; 
   Exchange=FAXEXCHANGE(FAXA(%trim(OldPhone))); 
   Number=FAXNUMBER(FAXA(%trim(OldPhone))); 
   NewAreaCode=FAXNEWAREA(OldAreaCode:Exchange); 
   Select; 
   When NewAreaCode=OldAreaCode; 
    rFAXNEWPHONEA=OldPhone; 
   Other; 
    rFAXNEWPHONEA=FAXNEWSTRING(OldPhone:NewAreaCode:Exchange:Number);
   EndSl; 
  EndSl; 
  return rFAXNEWPHONEA; 
 /end-free 
 
P FAXNEWPHONEA    E 


Rob Berendt