Problem with mysql calling stored procedures multiple times _ MySQL

BitsCN.com is a problem with mysql calling stored procedures for multiple times. when using mysql c api to build an application, one connection can only execute one stored procedure, no matter how free we use this connection again, there will be a prompt that we cannot execute qurry. I think such a large software will not have such basic problems, after all, most of the adodb is based on c api. is it impossible for all software to connect to the cache?

Not to mention, there are not many people who are exposed to such problems. most of the connection pools are well written. who will directly build connections for operations? so baidu google basically has no results, today, I came back to solve this problem and suddenly made a breakthrough. I found an article titled analysis on the problems and solutions of calling the stored procedure using Mysql C API.
The analysis by the author is quite complicated. In fact, we generally only use functions such as mysql_query and mysql_use_result. the crux of the problem is that when a stored procedure is executed, the database returns multiple data sets, even if there is only one data set, he will also have an empty set to end a session, and the author will scold him for his abnormal behavior. In fact, what the author may consider is the average request, mysql is used by all users. if there is no abnormal person who saves the image files and so on directly in the mysql field, then the reply cannot be completed at one time. therefore, more network interaction is required, therefore, an ending symbol is required for all interactions, and the stored procedure can return multiple data sets, if he only processes one result in c pai and allows the next new request, there is still data not sent for the same connection when the mysql service is disconnected, at this time, his safe practice is not to accept any new requests until the data is completely sent or the connection is closed. Otherwise, the mysql protocol resolution will fail, the next packet or packet loss occurs, so his approach is completely correct, Even if there is only one result set, the communication in the current session (for example, mysql_next_result) needs to be confirmed and the request is terminated. at this time, the server actually has no data, however, this process cannot be omitted, and a new request can be made without closing the connection.
To sum up, you must execute the stored procedure cyclically and retrieve all the result sets until they are empty. at this time, the current database connection can be safely returned, the following is a demo.

MYSQL_RES * conn;
MYSQL_RES * res;
MYSQL_ROW row;
Conn = connection pool. Get ("xxxx ");

Mysql_query (conn, "call qt ()");

Res = mysql_use_result (conn );
While (row = mysql_fetch_row (res ))){
Do main thins; // processing the first dataset this time
}
Mysql_free_result (res );
While (res = mysql_next_result (conn ))! = NULL ){
Do some thing; // process other datasets cyclically
Mysql_free_result (res );
}
Connection pool. Free (conn)

After reading through the above loop, we can ensure that a connection will be returned to the connection pool cleanly. of course, some connection pools may have placed the next loop in the connection pool for processing, the explanation is that all the remaining datasets are retrieved and then released. for example, when mysql connection pool is used in php, the above problems are not felt. BitsCN.com