Avoid MySQL multi-results from SP with Execute

Question

i have an SP like


BEGIN
DECLARE ...
CREATE TEMPORARY TABLE tmptbl_found (...);
PREPARE find FROM

"

      INSERT INTO tmptbl_found
       (SELECT userid FROM
            (
          SELECT userid FROM Soul
          WHERE
            .?.?.
          ORDER BY
            .?.?.
            ) AS left_tbl
          LEFT JOIN
            Contact
          ON userid = Contact.userid
        WHERE Contact.userid IS NULL LIMIT ?)

";


DECLARE iter CURSOR FOR SELECT userid, ... FROM Soul ...;
...
l:LOOP
    FETCH iter INTO u_id, ...;
    ...
    EXECUTE find USING ...,. . .,u_id,...;
    ...
  END LOOP;
...
END//

and it gives multi-results. Besides it's inconvenient, if i get all this multi-results (which i really don't need at all), about 5 (limit's param) for each of the hundreds of thousands of records in Soul, i'm afraid it will take all my memory (and all in vain). Also, i noticed, if i do prepare from an empty string, it still has multi-results... At least how to get rid of them in the execute statement? And i would like to have a recipe to avoid ANY output from SP, for any possible statement (i also have a lot of "update ..."s and "select ... into "s inside, if they can produce multi's). Tnx for any help...

score 0 · Answer 1 · answered Nov 27 '10 at 21:04

Well. I'll just say that it has come out that there wasn't really a problem. I didn't investigate hard, but it looks like the server didn't actually try to execute the statement ("call Proc();") to see whether there will be any results to return - it just looked at the code and assumed that there will be multiple result sets, requiring connection to be capable of handling them. But in PhpMyAdmin, which i was using at the time, it wasn't. However, issuing the same command from the MySQL command line client did the trick - no complaining about the given connection context, and no multis, too, because they don't have to be there - it's just a MySQL's estimation. I didn't have to conclude from the error, that the SP like this one will certainly return multis in MySQL, flushing all the intermediately fetched data, which i will need to suppress somehow.

It may be not so as i supposed, but the problem is gone now.

Avoid MySQL multi-results from SP with Execute

1 Answers1