Using STORED PROCEDURE with Freeradius

[Saeed Ahmed]

After exploring the web It came to know that I need to add a CLIENT_FLAG
mysql_real_connect(). Otherwise MySQL Stored Procedure will not work, I was
having same problem with PHP, then I added this parameter in mysql_connect()

mysql_connect($db_serv,$u_name,$u_pass, false,65536)

above 65536 could also be 'CLIENT_MULTI_STATEMENTS' but sometimes it can
only work with '65536'

Now I thought if add this flag in mysql_real_connect() in sql_mysql.c, it
will solve my problem BUT NO, I wasn't successful

I changed mysql_real_connect() in this way:

mysql_real_connect(&(mysql_sock->conn),
                                                    config->sql_server,
                                                    config->sql_login,
                                                    config->sql_password,
                                                    config->sql_db,
                                                    atoi(config->sql_port),
                                                    NULL,
                                                    CLIENT_MULTI_RESULTS OR
CLIENT_MULTI_STATEMENTS OR 65536))

Then I saved this file 'sql_mysql.c' THEN ./configure THEN MAKE THEN MAKE
INSTALL

But still I was unable to use Stored Procedure with FR, getting same error
"can't return a result set in the given context"
So Can anybody from developers guide me how should I change this
mysql_rael_connect() function so that I could be able to use MySQL stored
Procedure with FR

I will really appreciate your help

Thanks 
Saeed Ahmed.



_____________________________________________
From: freeradius-users-bounces+saeed=n-g-c.com at lists.freeradius.org
[mailto:freeradius-users-bounces+saeed=n-g-c.com at lists.freeradius.org] On
Behalf Of Saeed Ahmed
Sent: Wednesday, February 08, 2006 12:53 PM
To: 'FreeRadius users mailing list'
Subject: RE: Using STORED PROCEDURE with Freeradius

Thanks for your response. I tried this query:

authorize_check_query = "SELECT id, UN, Attribute, Value, op \
          FROM ${authcheck_table} \
          WHERE UN = '%{SQL-User-Name}' \
          ORDER BY id"

In above query I changed Username to UN, but radius was still working, so I
think its not necessary to return 'UserName' there could be some other
problem to use SP with FR. As I explored on web, I think I need to change
some mysql connect parameters, but I don't know from where should I start,
or is it really the point which is causing problem. I need more help please.


Thanks 
Saeed.


From: freeradius-users-bounces+saeed=n-g-c.com at lists.freeradius.org
[mailto:freeradius-users-bounces+saeed=n-g-c.com at lists.freeradius.org] On
Behalf Of Agus Supriyadi
Sent: Wednesday, February 08, 2006 3:55 AM
To: FreeRadius users mailing list
Subject: Re: Using STORED PROCEDURE with Freeradius


2006/2/7, Saeed Ahmed <saeed at n-g-c.com>:
Hi,

For some reasons I've to use Stored procedure With Freeradius but I am
getting following error from mysql:
Error: 1312 SQLSTATE: 0A000 (ER_SP_BADSELECT) 
Message: PROCEDURE %s can't return a result set in the given context
You can consider the following example:

Instead of following authorize_check_query
      authorize_check_query = "SELECT id, UserName, Attribute, Value, op,
uid \
          FROM ${authcheck_table} \
          WHERE Username = '%{SQL-User-Name}' \
          ORDER BY id"
I want to use this:
authorize_check_query = "CALL molo('%{SQL-User-Name}')"
And this is my stored proc in mysql: 

DELIMITER $$;
DROP PROCEDURE IF EXISTS `radius`.`molo`$$
CREATE PROCEDURE `molo`(did VARCHAR(10))
BEGIN
DECLARE rid INT;
DECLARE ruid VARCHAR(15);
DECLARE rattr VARCHAR(15);
DECLARE rop CHAR(2);
DECLARE rval VARCHAR(10);
SELECT id, UserName, Attribute, Value, op INTO rid,ruid,rattr,rop,rval  from
radcheck WHERE UserName=did;
SELECT rid,ruid,rattr,rop,rval;
END$$
DELIMITER;$$
I can call this stored proc From any my sql client successfully, but if I
call it from sql.conf it give s error: 1312. 

Any solution please?

Thanks in advance
Saeed Ahmed.

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


Perhaps it's happen because the field name returned by the molo procedure.
I think you have to use UserName insted of ruid because UserName field
already mapped to User-Name Attribute. Or you have to change how freeradius
map the attributes with field in your database. 

Regards

Agus
-- 
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS d(-) s:- a--- C++(+++)$>++++$ UL$>++++$ P+? L++$>$ !E--- W++ !N !o !K--
w !O M !V PS PE !Y PGP t 5 X R tv b DI D G e h r y 
------END GEEK CODE BLOCK------  << File: ATT00051.txt >> 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 8430 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20060208/004688a8/attachment.bin>