Using Multiple-Statement Execution

The MySQL client library supports multiple-statement execution capability as of MySQL 4.1. This allows you to send a string to the server consisting of multiple statements separated by semicolons, and then retrieve the result sets one after the other.

Multiple-statement execution is not enabled by default, so you must tell the server that you want to use it. There are two ways to do this. The first is to add the CLIENT_MULTI_STATEMENTS option in the flags argument to mysql_real_connect() at connect time:

opt_flags |= CLIENT_MULTI_STATEMENTS;
if (mysql_real_connect (conn, opt_host_name, opt_user_name, opt_password,
        opt_db_name, opt_port_num, opt_socket_name, opt_flags) == NULL)
{
    print_error (conn, "mysql_real_connect() failed");
    mysql_close (conn);
    exit (1);
}

The other is to use mysql_set_server_option() to enable the capability for an existing connection. For example:

if (mysql_set_server_option (conn, MYSQL_OPTION_MULTI_STATEMENTS_ON) != 0)
    print_error (conn, "Could not enable multiple-statement execution");

Which method is preferable? If the program does not use stored procedures, either one is suitable. If the program does use stored procedures and invokes a CALL statement that returns a result set, the first method is better. That's because CLIENT_MULTI_STATEMENT also turns on the CLIENT_MULTI_RESULTS option, which must be enabled or an error occurs if a stored procedure attempts to return a result. (More preferable yet might be to add CLIENT_MULTI_RESULTS to the flags argument to mysql_real_connect(), because that makes it explicit that you're enabling the option.)

Two functions form the basis for checking the current status of result retrieval when you're processing multiple result sets. mysql_more_results() returns non-zero if more results are available and zero otherwise. mysql_next_result() returns a status and also initiates retrieval of the next set if more results are available. The status is zero if more results are available, 1 if not, and a value greater than zero if an error occurred.

You can use these functions by putting your result-retrieval code inside a loop. After retrieving a result with your usual code, check whether there are any results yet to be retrieved. If so, perform another iteration of the loop. If not, exit the loop. Depending on how you structure your loop, you may not need to call mysql_more_results() at all. That's because you can also tell from the return value of mysql_next_result() whether more results are available.

In "A General Purpose Statement Handler," we wrote a function, process_statement(), that executes a statement and retrieves the result or displays the number of rows affected. By placing the result-retrieval code into a loop and incorporating mysql_next_result(), we can write a similar function, process_multi_statement(), that can retrieve multiple results:

void
process_multi_statement (MYSQL *conn, char *stmt_str)
{
MYSQL_RES   *res_set;
int         status;
int         keep_going = 1;

    if (mysql_query (conn, stmt_str) != 0)  /* the statement(s) failed */
    {
        print_error (conn, "Could not execute statement(s)");
        return;
    }

    /* the statement(s) succeeded; enter result-retrieval loop */
    do {
        /* determine whether current statement returned data */
        res_set = mysql_store_result (conn);
        if (res_set)            /* a result set was returned */
        {
            /* process rows and then free the result set */
            process_result_set (conn, res_set);
            mysql_free_result (res_set);
        }
        else                    /* no result set was returned */
        {
            /*
             * does the lack of a result set mean that the statement didn't
             * return one, or that it should have but an error occurred?
             */
            if (mysql_field_count (conn) == 0)
            {
                /*
                 * statement generated no result set (it was not a SELECT,
                 * SHOW, DESCRIBE, etc.); just report rows-affected value.
                 */
                printf ("%lu rows affected\n",
                            (unsigned long) mysql_affected_rows (conn));
            }
            else    /* an error occurred */
            {
                print_error (conn, "Could not retrieve result set");
                keep_going = 0;
            }
        }
        /* determine whether more results exist */
        /* 0 = yes, -1 = no, >0 = error */
        status = mysql_next_result (conn);
        if (status != 0)        /* no more results, or an error occurred */
        {
            keep_going = 0;
            if (status > 0)     /* error */
                print_error (conn, "Could not execute statement");
        }
    } while (keep_going);
}

If you like, you can just test whether the result of mysql_next_result() is zero, and exit the loop if not. The disadvantage of this simpler strategy is that if there are no more results, you don't know whether you've reached the end normally or an error occurred. In other words, you don't know whether to print an error message.