25.2.9. C API Handling of Multiple Query Execution
MySQL 5.1 supports the execution of multiple
statements specified in a single query string. To use this
capability with a given connection, you must specify the
CLIENT_MULTI_STATEMENTS option in the
flags parameter to
mysql_real_connect() when opening the
connection. You can also set this for an existing connection by
calling
mysql_set_server_option(MYSQL_OPTION_MULTI_STATEMENTS_ON).
By default, mysql_query() and
mysql_real_query() return only the first
query status and the subsequent queries status can be processed
using mysql_more_results() and
mysql_next_result().
/* Connect to server with option CLIENT_MULTI_STATEMENTS */
mysql_real_connect(..., CLIENT_MULTI_STATEMENTS);
/* Now execute multiple queries */
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");
do
{
/* Process all results */
...
printf("total affected rows: %lld", mysql_affected_rows(mysql));
...
if (!(result= mysql_store_result(mysql)))
{
printf(stderr, "Got fatal error processing query\n");
exit(1);
}
process_result_set(result); /* client function */
mysql_free_result(result);
} while (!mysql_next_result(mysql));
The multiple-statement capability can be used with
mysql_query() or
mysql_real_query(). It cannot be used with
the prepared statement interface. Prepared statement handles are
defined to work only with strings that contain a single
statement.
