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.5 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.
The multiple statement and result capabilities can be used only
with mysql_query()
or
mysql_real_query()
. They cannot be
used with the prepared statement interface. Prepared statement
handles are defined to work only with strings that contain a
single statement. See Section 21.9.4, “C API Prepared Statements”.
To enable multiple-statement execution and result processing, the following options may be used:
The mysql_real_connect()
function has a flags
argument for which two
option values are relevent:
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
. As of
MySQL 5.5.3, proc_name
can't
return a result set in the given contextCLIENT_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
allow processing of stored procedures that produce result
sets.
The following procedure outlines a suggested strategy for handling multiple statements:
Pass CLIENT_MULTI_STATEMENTS
to
mysql_real_connect()
, to fully
enable multiple-statement execution and multiple-result
processing.
After calling mysql_query()
or
mysql_real_query()
and
verifying that it succeeds, enter a loop within which you
process statement results.
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 allows a message to be printed for the latter
occurrence.
/* connect to server with the CLIENT_MULTI_STATEMENTS option */ if (mysql_real_connect (mysql, host_name, user_name, password, db_name, port_num, socket_name, CLIENT_MULTI_STATEMENTS) == NULL) { printf("mysql_real_connect() failed\n"); mysql_close(mysql); exit(1); } /* execute multiple statements */ status = mysql_query(mysql, "DROP TABLE IF EXISTS test_table;\ CREATE TABLE test_table(id INT);\ INSERT INTO test_table VALUES(10);\ UPDATE test_table SET id=20 WHERE id=10;\ SELECT * FROM test_table;\ DROP TABLE test_table"); if (status) { printf("Could not execute statement(s)"); mysql_close(mysql); exit(0); } /* process each statement result */ do { /* did current statement return data? */ result = mysql_store_result(mysql); if (result) { /* yes; process rows and free the result set */ process_result_set(mysql, result); mysql_free_result(result); } else /* no result set or error */ { if (mysql_field_count(mysql) == 0) { printf("%lld rows affected\n", mysql_affected_rows(mysql)); } else /* some error occurred */ { printf("Could not retrieve result set\n"); break; } } /* more results? -1 = no, >0 = error, 0 = yes (keep looping) */ if ((status = mysql_next_result(mysql)) > 0) printf("Could not execute statement\n"); } while (status == 0); mysql_close(mysql);
User Comments
Add your own comment.