[Devel] [ openser-Bugs-1618322 ] avp_db_query cannot call mysql stored procedures

[Adrian Georgescu]

The flag is CLIENT_MULTI_RESULTS and you must also modify the code  
that reads the results back because there are multiple result sets  
returned by a stored procedure.

On Dec 21, 2006, at 11:44 AM, SourceForge.net wrote:

> Bugs item #1618322, was opened at 2006-12-18 20:17
> Message generated for change (Comment added) made by vabdulla
> You can respond by visiting:
> https://sourceforge.net/tracker/? 
> func=detail&atid=743020&aid=1618322&group_id=139143
>
> Please note that this message will contain a full copy of the  
> comment thread,
> including the initial issue submission, for this request,
> not just the latest update.
> Category: modules
> Group: ver devel
> Status: Open
> Resolution: None
> Priority: 5
> Private: No
> Submitted By: vabdulla (vabdulla)
> Assigned to: Daniel-Constantin Mierla (miconda)
> Summary: avp_db_query cannot call mysql stored procedures
>
> Initial Comment:
> Hi,
>
> When calling a mysql5 stored procedure which returns a result set  
> like this:
> avp_db_query("CALL db.sp_name('$avp(s:param1)', "param2",'$oU')",  
> "$avp(s:rs1);$avp(s:rs2));
>
> I get the following error:
>
> 0(1245) submit_query: PROCEDURE db.sp_name can't return a result  
> set in the given context
> 0(1245) db_raw_query: Error while submitting query
> 0(1245) avpops:db_query_avp: error - cannot do the query
>
> After some research, I have found that this error come from the  
> client flags passed to mysql_real_connect() function.
>
> The patch attached correct this bug.
>
> -- 
> Regards,
> -vma
> .
>
>
>
> ----------------------------------------------------------------------
>
>> Comment By: vabdulla (vabdulla)
> Date: 2006-12-21 11:44
>
> Message:
> Logged In: YES
> user_id=1580175
> Originator: YES
>
> Hello,
>
> According to the mysql documentation, these flags exist since  
> version 4.1
> (http://dev.mysql.com/doc/refman/4.1/en/mysql-real-connect.html).
>
> Otherwise, I don't see a simple way to check the mysql version  
> directly as
> you need to be connected first before calling  
> mysql_get_server_version().
>
> -- 
> Regards,
> -vma
> .
>
>
> ----------------------------------------------------------------------
>
> Comment By: Daniel-Constantin Mierla (miconda)
> Date: 2006-12-20 22:57
>
> Message:
> Logged In: YES
> user_id=1246013
> Originator: NO
>
> If I am not wrong, these new flags are available with MySQL 5.0 or
> greater. Could you adapt the patch so it detects mysql version and  
> sets the
> flags only when the version is appropriate? Otherwise, I guess,  
> will give
> compilation errors for older versions of MySQL.
>
> ----------------------------------------------------------------------
>
> Comment By: Kobi Eshun (ekobi)
> Date: 2006-12-18 21:06
>
> Message:
> Logged In: YES
> user_id=1039134
> Originator: NO
>
> Hi,
>
> I believe that there is more to this problem than simply setting the
> additional client flags. The MySQL manual also recommends disposing  
> of all
> result sets by iterating over them with mysql_next_result().
>
> I've appended a patch that does this, and it works fine for some use
> cases. However, I don't believe it's trivial to retrieve more than one
> result set into the OpenSER context using the current API.
>
> Anyone else have any thougts on this? Cheers,
> --
> kobi
>
>
> Index: modules/mysql/dbase.c
> ===================================================================
> RCS file: /cvsroot/openser/sip-server/modules/mysql/dbase.c,v
> retrieving revision 1.5
> diff -u -r1.5 dbase.c
> --- modules/mysql/dbase.c	14 Jul 2006 14:12:59 -0000	1.5
> +++ modules/mysql/dbase.c	5 Dec 2006 00:31:03 -0000
> @@ -327,6 +327,7 @@
>  	}
>
>  	CON_RESULT(_h) = mysql_store_result(CON_CONNECTION(_h));
> +
>  	if (!CON_RESULT(_h)) {
>  		if (mysql_field_count(CON_CONNECTION(_h)) == 0) {
>  			(*_r)->col.n = 0;
> @@ -352,7 +353,18 @@
>  		return -4;
>  	}
>  	
> +	/* */
> +	/* XXX: flush list of result sets -- ke */
> +	LOG(L_DBG,"store_result: emptying mysql_store_result list...\n ");
> +	while ((!mysql_next_result (CON_CONNECTION(_h)))) {
> +		MYSQL_RES *foo=mysql_store_result (CON_CONNECTION(_h));
> +		LOG(L_DBG,"store_result: freeing extra result set..\n");
> +		mysql_free_result(foo);
> +	}
> +	LOG(L_DBG,"store_result: done emptying mysql_store_result list.\n  
> ");
> +
>  	return 0;
> +
>  }
>
>
> Index: modules/mysql/my_con.c
> ===================================================================
> RCS file: /cvsroot/openser/sip-server/modules/mysql/my_con.c,v
> retrieving revision 1.4
> diff -u -r1.4 my_con.c
> --- modules/mysql/my_con.c	14 Jul 2006 14:12:59 -0000	1.4
> +++ modules/mysql/my_con.c	5 Dec 2006 00:31:03 -0000
> @@ -76,7 +76,7 @@
>  		    );
>  	}
>
> -	if (!mysql_real_connect(ptr->con, id->host, id->username, id- 
> >password,
> id->database, id->port, 0, 0)) {
> +	if (!mysql_real_connect(ptr->con, id->host, id->username, id- 
> >password,
> id->database, id->port, 0, CLIENT_MULTI_RESULTS| 
> CLIENT_MULTI_STATEMENTS))
> {
>  		LOG(L_ERR, "new_connection: %s\n", mysql_error(ptr->con));
>  		mysql_close(ptr->con);
>  		goto err;
>
>
> ----------------------------------------------------------------------
>
> You can respond by visiting:
> https://sourceforge.net/tracker/? 
> func=detail&atid=743020&aid=1618322&group_id=139143
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel