From LB0074453@TechMahindra.com Tue Jan 15 12:31:06 2013
From: Lakshmi Narayana Baliah <LB0074453@TechMahindra.com>
To: freeradius-users@lists.freeradius.org
Subject: how to call stored procedure in free-radius server
Date: Tue, 15 Jan 2013 17:00:40 +0530
Message-ID:
 <1B91A6A3C38A4F4B9E3B1AE4D3746E18052A129F@SINBNGEX001.TechMahindra.com>
In-Reply-To: <mailman.7.1358247610.4234.freeradius-users@lists.freeradius.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0615419871795318817=="

--===============0615419871795318817==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Hi Arran,

Thanks for your reply.... have created stored procedure(myS1Request) in oracl=
e database 11g as shown below, procedure takes input and output parameters.
The below procedure should be called in the SQL module in Free-radius for Aut=
hentication and Authorization purpose.

PROCEDURE myS1Request(
        UserName         IN  VARCHAR2,
        MessageType      IN  LONG,
        RemoteAddress    IN  LONG,
        FemtoAp          IN  VARCHAR2,
        RadCoverageInd   IN  LONG,
        RadCellId        IN  LONG,
        Rad3GCellId      IN  LONG,
        RadGeoCord       IN  LONG,
        RadAltDirection  IN  LONG,
        HNBIPAddress     IN  LONG,
        RadPLMNId        IN  LONG,
        HNBCellIdentity  IN  LONG,
        RadLocAreaLAC    IN  LONG,
        RadRoutAreaCode  IN  LONG,
        RadServiceArCode IN  LONG,
 	  RadCSGId         IN  LONG,
        RadUECap         IN  LONG,
        Result           OUT VARCHAR2,
        RadRegRejCau     OUT LONG,
        RadLocBlacklInd  OUT LONG,
        HNBCellId        OUT LONG,
        RadServiceAreaC  OUT LONG,
        RadLocationAreaI OUT LONG,
        RadRoutAreaC     OUT LONG
         )=20
 =20

please help me how can proceed...........
.


Lakshmi narayana |=C2=A0Prod Engineering | Tech Mahindra
#9/7 Hosur Road,Bangalore-560029
/Office: +91 80=C2=A040243000, Extn: 3486
| Mobile: +91 9060867386
Email:lb0074453@techmahindra.com
www.techmahindra.com


Message: 2
Date: Tue, 15 Jan 2013 10:46:08 +0000
From: Arran Cudbard-Bell <a.cudbardb@freeradius.org>
To: FreeRadius users mailing list
	<freeradius-users@lists.freeradius.org>
Subject: Re: how to call stored procedure with output varaibles in
	free-radius
Message-ID: <C47F1EE1-3524-4709-85FA-79D0B268C5D6@freeradius.org>
Content-Type: text/plain; charset=3Dus-ascii


On 15 Jan 2013, at 06:22, "Lakshmi Narayana Baliah" <LB0074453@techmahindra.c=
om> wrote:

> Hi All,
> =20
> How to call my own stored procedure ,which takes input and output variable =
in free-radius server.
> =20
> How do i do that ??? please help....................

Guessing you're referring to stored procedures in SQL? Or are you misusing SQ=
L terminology to refer to something like a subroutine or function writen in t=
he policy language?

-Arran

------------------------------

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

End of Freeradius-Users Digest, Vol 93, Issue 42
************************************************

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Disclaimer:  This message and the information contained herein is proprietary=
 and confidential and subject to the
 Tech Mahindra policy statement, you may review the policy at <a href=3D"http=
://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaim=
er.html</a>=20
externally and <a href=3D"http://tim.techmahindra.com/tim/disclaimer.html">ht=
tp://tim.techmahindra.com/tim/disclaimer.html</a> internally within Tech Mahi=
ndra.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

--===============0615419871795318817==--


From p.mayers@imperial.ac.uk Tue Jan 15 12:40:41 2013
From: Phil Mayers <p.mayers@imperial.ac.uk>
To: freeradius-users@lists.freeradius.org
Subject: Re: how to call stored procedure in free-radius server
Date: Tue, 15 Jan 2013 11:40:35 +0000
Message-ID: <50F54033.8090909@imperial.ac.uk>
In-Reply-To:
 <1B91A6A3C38A4F4B9E3B1AE4D3746E18052A129F@SINBNGEX001.TechMahindra.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============1542154031023045526=="

--===============1542154031023045526==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

On 15/01/13 11:30, Lakshmi Narayana Baliah wrote:
> Hi Arran,
>
> Thanks for your reply.... have created stored procedure(myS1Request) in ora=
cle database 11g as shown below, procedure takes input and output parameters.
> The below procedure should be called in the SQL module in Free-radius for A=
uthentication and Authorization purpose.

>
> please help me how can proceed...........

Calling a stored procedure is the same as doing any other SQL query. You=20
can either force the intput/output to match the radcheck/radreply schema=20
using SQL, or use the SQL xlat syntax like so:

authorize {
   ...
   update request {
     Tmp-String-0 :=3D "%{sql:select storedproc('%{Arg1}',...)}"
   }
   ...
}

--===============1542154031023045526==--


From a.cudbardb@freeradius.org Tue Jan 15 13:10:39 2013
From: Arran Cudbard-Bell <a.cudbardb@freeradius.org>
To: freeradius-users@lists.freeradius.org
Subject: Re: how to call stored procedure in free-radius server
Date: Tue, 15 Jan 2013 12:10:33 +0000
Message-ID: <EAE0A62A-9D71-4278-8A99-6F3274E17C5E@freeradius.org>
In-Reply-To:
 <1B91A6A3C38A4F4B9E3B1AE4D3746E18052A129F@SINBNGEX001.TechMahindra.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============6079227860340718897=="

--===============6079227860340718897==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable


On 15 Jan 2013, at 11:30, Lakshmi Narayana Baliah <LB0074453@techmahindra.com=
> wrote:

> Hi Arran,
>=20
> Thanks for your reply.... have created stored procedure(myS1Request) in ora=
cle database 11g as shown below, procedure takes input and output parameters.
> The below procedure should be called in the SQL module in Free-radius for A=
uthentication and Authorization purpose.
>=20
> PROCEDURE myS1Request(
>        UserName         IN  VARCHAR2,
>        MessageType      IN  LONG,
>        RemoteAddress    IN  LONG,
>        FemtoAp          IN  VARCHAR2,
>        RadCoverageInd   IN  LONG,
>        RadCellId        IN  LONG,
>        Rad3GCellId      IN  LONG,
>        RadGeoCord       IN  LONG,
>        RadAltDirection  IN  LONG,
>        HNBIPAddress     IN  LONG,
>        RadPLMNId        IN  LONG,
>        HNBCellIdentity  IN  LONG,
>        RadLocAreaLAC    IN  LONG,
>        RadRoutAreaCode  IN  LONG,
>        RadServiceArCode IN  LONG,
> 	  RadCSGId         IN  LONG,
>        RadUECap         IN  LONG,
>        Result           OUT VARCHAR2,
>        RadRegRejCau     OUT LONG,
>        RadLocBlacklInd  OUT LONG,
>        HNBCellId        OUT LONG,
>        RadServiceAreaC  OUT LONG,
>        RadLocationAreaI OUT LONG,
>        RadRoutAreaC     OUT LONG
>         )=20
>=20
>=20
> please help me how can proceed...........

Just use the syntax that you usually would for calling stored procedures. Or =
if that doesn't work because oracle has a special function that needs to be c=
alled, submit patches.

For adding attributes to the reply list, you want to modify the authorize_rep=
ly_query.

As per the examples you can use %{attribute} syntax to specify parts of the S=
QL query string to replace with attribute values from the request.=20

You need to return a result set in the format

https://github.com/FreeRADIUS/freeradius-server/blob/master/src/modules/rlm_s=
ql/sql.c#L147

expects.

That is, one row per attribute that needs to be added to the reply, with the =
columns:
id, username, attribute, value, op

Id and username aren't used IIRC so they can be anything.

If you feel like getting into the server code, I think there'd be some value =
for SQL and LDAP to be able to register 'mapping' xlat functions, which when =
called with a query maps columns in the result to a RADIUS attributes, with a=
 default operator specified by the map.

Sort of like the shim code you'd find in some FFI (foreign function interface=
) implementations. Actually i'd be interested in getting other people's opini=
on on that.

-Arran

--===============6079227860340718897==--