FR with MySQL - Stored Procedures

[cky]

so...after a while I found a workaround for the SP problem..well, is quick
and dirty and probably not the best solution.
I've added to the function "sql_fetch_row" in sql_mysql.c a simple
"sql_free_result(sqlsocket, config);"...see the diff below.
Now my Stored Procedure works correct and as expected (my SP is at the end
of this message).

Perhaps someone can verify this, and perhaps this can added to the
freeradius sourcecode from a developer (well, perhaps not so dirty ;) )

--- sql_mysql.c_org     2007-05-08 15:55:47.000000000 +0200
+++ sql_mysql.c 2007-05-08 15:57:35.000000000 +0200
@@ -50,6 +50,8 @@
        SQL_ROW row;
 } rlm_sql_mysql_sock;

+static int sql_free_result(SQLSOCK*, SQL_CONFIG*);
+
 /*************************************************************************
  *
  *     Function: sql_create_socket
@@ -82,7 +84,7 @@
                                                    config->sql_db,
                                                    atoi(config->sql_port),
                                                    NULL,
-                                                   CLIENT_FOUND_ROWS))) {
+                                                  
CLIENT_FOUND_ROWS|CLIENT_MULTI_STATEMENTS))) {
                radlog(L_ERR, "rlm_sql_mysql: Couldn't connect socket to
MySQL server %s@%s:%s", config->sql_login, config->sql_server,
config->sql_db);
                radlog(L_ERR, "rlm_sql_mysql: Mysql error '%s'",
mysql_error(&mysql_sock->conn));
                mysql_sock->sock = NULL;
@@ -289,6 +291,9 @@
        if (sqlsocket->row == NULL) {
                return sql_check_error(mysql_errno(mysql_sock->sock));
        }
+
+       sql_free_result(sqlsocket, config);
+
        return 0;
 }

My SP:
CREATE PROCEDURE ssg_auth (IN `CallingStationID` VARCHAR(14),IN `ClientIP`
VARCHAR(15))
SQL SECURITY INVOKER
BEGIN
 DECLARE rows INT (1);
 DECLARE v_id BIGINT (20);
 DECLARE v_UserName VARCHAR(10);
 DECLARE v_Attribute,v_Value VARCHAR (30);
 DECLARE v_op VARCHAR (4);
 DECLARE v_IP VARCHAR (15);
 DECLARE user CURSOR FOR SELECT
`id`,`UserName`,`Attribute`,`Value`,`op`,`IP` FROM `ssg_check` WHERE
`Calling-Station-Id` = `CallingStationID`;
 SELECT COUNT(*) INTO rows FROM `ssg_check` WHERE `Calling-Station-Id` =
`CallingStationID`;
  IF rows = 0 THEN
    INSERT INTO `ssg_check` (`Calling-Station-Id`,`IP`) VALUES
(`CallingStationID`,`ClientIP`);
    SELECT '0','Guest','Auth-Type','REJECT',':=';
  END IF;
  IF rows = 1 THEN
    OPEN user;
      FETCH user INTO v_id,v_UserName,v_Attribute,v_Value,v_op,v_IP;
    CLOSE user;
    IF v_Value = 'REJECT' and v_ip != `ClientIP` THEN
      UPDATE `ssg_check` SET `IP` = `ClientIP` WHERE `Calling-Station-Id` =
`CallingStationID`;
    END IF;
    SELECT v_id,v_UserName,v_Attribute,v_Value,v_op;
  END IF;
END; //

Thomas
-- 
View this message in context: http://www.nabble.com/FR-with-MySQL---Stored-Procedures-tf3701829.html#a10376727
Sent from the FreeRadius - User mailing list archive at Nabble.com.