Re: Censoring SQL access -- ideas?

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