Using STORED PROCEDURE with Freeradius
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 mysql client successfully, but if I call it from sql.conf it gives error: 1312. Any solution please? Thanks in advance Saeed Ahmed.
2006/2/7, Saeed Ahmed <saeed@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 mysql client successfully, but if Icall it from sql.conf it gives 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------
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@lists.freeradius.org [mailto:freeradius-users-bounces+saeed=n-g-c.com@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@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------
I do not know about mysql, but i have used stored procedures just fine with postgresql. it can look something along the lines of: authorize_check_query = "SELECT * FROM someproc('%{SQL-User-Name}', '%{Client-IP-Address}') HAVING id IS NOT NULL" for the record, I have been using stored procedures with postgresql and freeradius for a pretty long time in a heavy production environment, and it has been the greatest thing since slice bread. It allowed me to do all sorts of creative things, that you cannot even begin to imagine. Dr. Snow Ball
Is it possible to delete entire attributes with rlm_attr_rewrite? Tomasz -- Tomasz Wolniewicz Tomasz.Wolniewicz@uni.torun.pl http://www.uni.torun.pl/~twoln Uczelniane Centrum Informatyczne Information&Communication Technology Centre Uniwersytet Mikolaja Kopernika Nicolaus Copernicus University, pl. Rapackiego 1, Torun pl. Rapackiego 1, Torun, Poland tel: +48-56-611-2750 fax: +48-56-622-1850 tel kom.: +48-693-032-576
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@lists.freeradius.org [mailto:freeradius-users-bounces+saeed=n-g-c.com@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@lists.freeradius.org [mailto:freeradius-users-bounces+saeed=n-g-c.com@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@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 >>
participants (4)
-
Agus Supriyadi -
data zone -
Saeed Ahmed -
Tomasz Wolniewicz