FR with MySQL - Stored Procedures

[Gunther]

Did some further research on the MySQL - FR Stored Procedure (SP) problem.

When calling the SP, MySQL always returns two results. One is the actual
result and
the other is the number of affected rows, which is different to a normal
e.g. SELECT query.

SP:
mysql> call CheckIt('myString');
+--------+
| result |
+--------+
|     10 | (result is correct)
+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec) <-- Result plus the number of affected
rows!

Normal Query:
mysql> select 25 AS result;
+--------+
| result |
+--------+
|      25 |
+--------+
1 row in set (0.00 sec) <--- Normal query with one result

-------- MYSQL 5.0 Ref manual ----
If you write C programs that use the CALL SQL statement to execute stored
procedures that produce result sets, you must set the CLIENT_MULTI_RESULTS
flag, either explicitly, or implicitly by setting CLIENT_MULTI_STATEMENTS
when you call mysql_real_connect(). This is because each such stored
procedure produces multiple results: the result sets returned by statements
executed within the procedure, as well as a result to indicate the call
status. To process the result of a CALL statement, use a loop that calls
mysql_next_result() to determine whether there are more results.

The following procedure outlines a suggested strategy for handling multiple
statements:
 1. Pass CLIENT_MULTI_STATEMENTS to mysql_real_connect(), to fully enable
multiple-statement execution and multiple-result processing.
 2. After calling mysql_query() or mysql_real_query() and verifying that it
succeeds, enter a loop within which you process statement results.
 3. For each iteration of the loop, handle the current statement result,
retrieving either a result set or an affected-rows count. If an error
occurs, exit the loop.
 4. At the end of the loop, call mysql_next_result() to check whether
another result exists and initiate retrieval for it if so. If no more
results are available, exit the loop.
----------------------------------

Just for a test, I added a very quick and dirty 'mysql_next_result' into the
sql_free_result function of
"sql_mysql.c" in row 292 of FR 1.1.6, the same location Thomas used the 
.....
        if (sqlsocket->row == NULL) {
                return sql_check_error(mysql_errno(mysql_sock->sock));
        }
        mysql_next_result(mysql_sock->sock); /* eat the number of affected
rows result */
        return 0;
}
.....

As a result I do not get the 2014 error anymore and everything seems to be
working fine.
Since I do not really know the implications of just adding this command,
maybe one of the experts
could help out here.

In an ealier posting 3 days ago I said that the problem is not really stored
procedure related ...
but it is! Once the SP is called at least once other queries will have
errors too.

Gunther

FR 1.1.6 - MySQL 5.0.41 - CentOS 4.4