Avoid MySQL multi-results from SP with Execute
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...
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.
精彩评论