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

SourceForge.net noreply at sourceforge.net
Wed Dec 20 22:57:00 CET 2006


Bugs item #1618322, was opened at 2006-12-18 21:17
Message generated for change (Comment added) made by miconda
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: Daniel-Constantin Mierla (miconda)
Date: 2006-12-20 23: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 22: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



More information about the Devel mailing list