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

[SourceForge.net]

Bugs item #1618322, was opened at 2006-12-18 11:17
Message generated for change (Comment added) made by ekobi
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 1.1.x
Status: Open
Resolution: None
Priority: 5
Private: No
Submitted By: vabdulla (vabdulla)
Assigned to: Nobody/Anonymous (nobody)
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: Kobi Eshun (ekobi)
Date: 2006-12-18 12: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