Re: [exim-dev] Patch to allow calling MySQL stored procedure…

Startseite
Nachricht löschen
Nachricht beantworten
Autor: B. Johannessen
Datum:  
To: exim-dev
Betreff: Re: [exim-dev] Patch to allow calling MySQL stored procedures from Exim
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)