Stored procedures in rlm_sql_mysql

[Michael Griego]

This code is specific to MySQL 4.1 and higher installations, so the  
MySQL version would have to be checked during build to insure its  
being linked against the appropriate library versions...

--Mike

On May 16, 2007, at 3:30 PM, Michael A Baikov wrote:

> This patch allows me to use stored procedures in in sql.conf.
> For example instead
>
>        authorize_check_query = "SELECT id, UserName, Attribute,  
> Value, op \
>          FROM ${authcheck_table} \
>          WHERE Username = BINARY '%{SQL-User-Name}' \
>          ORDER BY id"
>
> I can use
> 	authorize_check_query = "CALL auth_check('%{SQL-User-Name}')"
> and store all code in the database.
>
>
> --------------------------------------
>
> --- sql_mysql_orig.c    2007-04-05 14:52:38.000000000 +0400
> +++ sql_mysql.c 2007-05-17 00:22:59.000000000 +0400
> @@ -52,6 +52,9 @@
>         SQL_ROW row;
>  } rlm_sql_mysql_sock;
>
> +/*static int sql_free_result(SQLSOCK*, SQL_CONFIG*); */
> +
> +
>  / 
> ********************************************************************** 
> ***
>   *
>   *     Function: sql_create_socket
> @@ -84,7 +87,7 @@
>                                                     config->sql_db,
>                                                     atoi(config- 
> >sql_port),
>                                                     NULL,
> -                                                    
> CLIENT_FOUND_ROWS))) {
> +                                                   CLIENT_FOUND_ROWS|
> CLIENT_MULTI_RESULTS))) {
>                 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;
> @@ -305,13 +308,35 @@
>    
> ********************************************************************** 
> ***/
>  static int sql_free_result(SQLSOCK * sqlsocket, UNUSED SQL_CONFIG  
> *config)
>  {
> +       int status;
>         rlm_sql_mysql_sock *mysql_sock = sqlsocket->conn;
>
>         if (mysql_sock->result) {
>                 mysql_free_result(mysql_sock->result);
>                 mysql_sock->result = NULL;
>         }
> +
> +       /*
> +        * http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple- 
> queries.html
> +        *
> +        * Multiple-result processing also is required if you  
> execute CALL
> +        * statements for stored procedures: A stored procedure  
> returns a
> +        * status result when it terminates, but it may also  
> produce result
> +        * sets as it runs (for example, if it executes SELECT  
> statements).
> For
> +        * any stored procedure that produces result sets in  
> addition to the
> +        * final status, you must be prepared to retrieve multiple  
> results.
> +        * */
> +
> +       status = mysql_next_result(mysql_sock);
> +       while(status == 0) {
> +               if ((status = mysql_next_result(mysql_sock)) > 0)
> +                       radlog(L_ERR, "rlm_sql_mysql: Error while  
> fetching
> next result in result set.");
> +               /* I do not need them - just discard it */
>
> +               radlog(L_DBG, "rlm_sql_mysql: Discarded an result from
> multiple result request");
> +               mysql_store_result(mysql_sock->sock);
> +       }
>         return 0;
>  }
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/ 
> devel.html

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 6184 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20070516/f1120a07/attachment.bin>