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

[Dan Pascu]

It is not a guarantee that there are multiple result sets after you call a 
stored procedure. In fact the number of result sets may be zero, one or 
more than one, depending on what the stored procedure does.

However the important part is that you retrieve all the result sets that 
were generated, else the server may close the connection. Even if it 
doesn't close the connection effectively, if you do not retrieve all the 
results sets, the following SQL queries will fail and the database 
connection will be basically inoperational.

On Thursday 21 December 2006 12:52, Adrian Georgescu wrote:
> 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
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel

-- 
Dan