22.8.17 C API Support for Multiple Statement Execution

By default, mysql_query() and
mysql_real_query() interpret their
statement string argument as a single statement to be executed,
and you process the result according to whether the statement
produces a result set (a set of rows, as for
SELECT) or an affected-rows count
(as for INSERT,
UPDATE, and so forth).

MySQL 5.7 also supports the execution of a string
containing multiple statements separated by semicolon
(“;”) characters. This capability
is enabled by special options that are specified either when you
connect to the server with
mysql_real_connect() or after
connecting by calling`
mysql_set_server_option().

Executing a multiple-statement string can produce multiple result
sets or row-count indicators. Processing these results involves a
different approach than for the single-statement case: After
handling the result from the first statement, it is necessary to
check whether more results exist and process them in turn if so.
To support multiple-result processing, the C API includes the
mysql_more_results() and
mysql_next_result() functions.
These functions are used at the end of a loop that iterates as
long as more results are available. Failure to process
the result this way may result in a dropped connection to the
server.

Multiple-result processing also is required if you execute
CALL statements for stored
procedures. Results from a stored procedure have these
characteristics:

Statements within the procedure may produce result sets (for
example, if it executes SELECT
statements). These result sets are returned in the order that
they are produced as the procedure executes.

In general, the caller cannot know how many result sets a
procedure will return. Procedure execution may depend on loops
or conditional statements that cause the execution path to
differ from one call to the next. Therefore, you must be
prepared to retrieve multiple results.

The final result from the procedure is a status result that
includes no result set. The status indicates whether the
procedure succeeded or an error occurred.

CLIENT_MULTI_RESULTS enables the client
program to process multiple results. This option
must be enabled if you execute
CALL statements for stored
procedures that produce result sets. Otherwise, such
procedures result in an error Error 1312 (0A000):
PROCEDURE proc_name can't
return a result set in the given context. In
MySQL 5.7,
CLIENT_MULTI_RESULTS is enabled by
default.

CLIENT_MULTI_STATEMENTS enables
mysql_query() and
mysql_real_query() to
execute statement strings containing multiple statements
separated by semicolons. This option also enables
CLIENT_MULTI_RESULTS implicitly, so a
flags argument of
CLIENT_MULTI_STATEMENTS to
mysql_real_connect() is
equivalent to an argument of
CLIENT_MULTI_STATEMENTS |
CLIENT_MULTI_RESULTS. That is,
CLIENT_MULTI_STATEMENTS is sufficient
to enable multiple-statement execution and all
multiple-result processing.

After the connection to the server has been established, you
can use the
mysql_set_server_option()
function to enable or disable multiple-statement execution by
passing it an argument of
MYSQL_OPTION_MULTI_STATEMENTS_ON or
MYSQL_OPTION_MULTI_STATEMENTS_OFF. Enabling
multiple-statement execution with this function also enables
processing of “simple” results for a
multiple-statement string where each statement produces a
single result, but is not sufficient to
permit processing of stored procedures that produce result
sets.

The following procedure outlines a suggested strategy for handling
multiple statements:

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.

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.

One possible implementation of the preceding strategy is shown
following. The final part of the loop can be reduced to a simple
test of whether
mysql_next_result() returns
nonzero. The code as written distinguishes between no more results
and an error, which enables a message to be printed for the latter
occurrence.