B. Johannessen wrote:
> The below patch allows you to call MySQL stored procedures that return
> results by adding the CLIENT_MULTI_RESULTS flag to the call to
> mysql_real_connect().
Huh! This wasn't as simple as I'd hoped. It seems that executing a CALL
query that returns results yields *at least* two result set. At least
one for the results returned from the stored procedure and finally one
for the CALL itself.
Without a fairly major rewrite, the MySQL lookup is unable to handle
multiple rows with different columns in a meaningful way, so to at least
be able to retrieve the first result set, I had to modify the patch a bit.
As this suddenly turned into a bigger change that I originally thought,
I also withdraw my request to have this included in the next Exim
release. Someone that knows a lot more about MySQL then me should really
have a look at it first. Paul Kelly is credited with contributing the
original code; is he still around?
Bob
diff -ruN exim-4.69-orig/src/lookups/mysql.c exim-4.69/src/lookups/mysql.c
--- exim-4.69-orig/src/lookups/mysql.c 2007-08-23 12:16:51.000000000 +0200
+++ exim-4.69/src/lookups/mysql.c 2008-03-20 22:32:12.000000000 +0100
@@ -202,7 +202,7 @@
if (mysql_real_connect(mysql_handle,
/* host user passwd database */
CS sdata[0], CS sdata[2], CS sdata[3], CS sdata[1],
- port, CS socket, 0) == NULL)
+ port, CS socket, CLIENT_MULTI_RESULTS) == NULL)
{
*errmsg = string_sprintf("MYSQL connection failed: %s",
mysql_error(mysql_handle));
@@ -316,6 +316,20 @@
if (mysql_result != NULL) mysql_free_result(mysql_result);
+/* To allow stored procedures to return results, the connection has to be
+set up with the CLIENT_MULTI_RESULTS flag. When we do this, and execute a
+CALL query, there may be more then one result set returned. We are only
+interested in the first one, but we have to retreve and discard the rest
+to avoid complaints of "Commands out of sync; you can't run this command
+now" */
+
+while (0 == mysql_next_result(mysql_handle))
+ {
+ if (NULL == (mysql_result = mysql_use_result(mysql_handle)))
+ mysql_free_result(mysql_result);
+ }
+
+
/* Non-NULL result indicates a sucessful result */
if (result != NULL)
