MySQL 5.6 C API Developer Guide
This section describes prepared-statement support in the C API for
stored procedures executed using
CALL
statements:
Stored procedures executed using prepared
CALL
statements can be used in the
following ways:
A stored procedure can produce any number of result sets. The number of columns and the data types of the columns need not be the same for all result sets.
The final values of OUT
and
INOUT
parameters are available to the
calling application after the procedure returns. These
parameters are returned as an extra single-row result set
following any result sets produced by the procedure itself.
The row contains the values of the OUT
and
INOUT
parameters in the order in which they
are declared in the procedure parameter list.
For information about the effect of unhandled conditions on procedure parameters, see Condition Handling and OUT or INOUT Parameters.
The following discussion shows how to use these capabilities
through the C API for prepared statements. To use prepared
CALL
statements through the
PREPARE
and
EXECUTE
statements, see
CALL Statement.
An application that executes a prepared
CALL
statement should use a loop
that fetches a result and then invokes
mysql_stmt_next_result()
to
determine whether there are more results. The results consist of
any result sets produced by the stored procedure followed by a
final status value that indicates whether the procedure terminated
successfully.
If the procedure has OUT
or
INOUT
parameters, the result set preceding the
final status value contains their values. To determine whether a
result set contains parameter values, test whether the
SERVER_PS_OUT_PARAMS
bit is set in the
server_status
member of the
MYSQL
connection handler:
mysql->server_status & SERVER_PS_OUT_PARAMS
The following example uses a prepared
CALL
statement to execute a stored
procedure that produces multiple result sets and that provides
parameter values back to the caller by means of
OUT
and INOUT
parameters.
The procedure takes parameters of all three types
(IN
, OUT
,
INOUT
), displays their initial values, assigns
new values, displays the updated values, and returns. The expected
return information from the procedure therefore consists of
multiple result sets and a final status:
One result set from a SELECT
that displays the initial parameter values:
10
, NULL
,
30
. (The OUT
parameter
is assigned a value by the caller, but this assignment is
expected to be ineffective: OUT
parameters
are seen as NULL
within a procedure until
assigned a value within the procedure.)
One result set from a SELECT
that displays the modified parameter values:
100
, 200
,
300
.
One result set containing the final OUT
and
INOUT
parameter values:
200
, 300
.
A final status packet.
The code to execute the procedure:
MYSQL_STMT *stmt; MYSQL_BIND ps_params[3]; /* input parameter buffers */ int int_data[3]; /* input/output values */ my_bool is_null[3]; /* output value nullability */ int status; /* set up stored procedure */ status = mysql_query(mysql, "DROP PROCEDURE IF EXISTS p1"); test_error(mysql, status); status = mysql_query(mysql, "CREATE PROCEDURE p1(" " IN p_in INT, " " OUT p_out INT, " " INOUT p_inout INT) " "BEGIN " " SELECT p_in, p_out, p_inout; " " SET p_in = 100, p_out = 200, p_inout = 300; " " SELECT p_in, p_out, p_inout; " "END"); test_error(mysql, status); /* initialize and prepare CALL statement with parameter placeholders */ stmt = mysql_stmt_init(mysql); if (!stmt) { printf("Could not initialize statement\n"); exit(1); } status = mysql_stmt_prepare(stmt, "CALL p1(?, ?, ?)", 16); test_stmt_error(stmt, status); /* initialize parameters: p_in, p_out, p_inout (all INT) */ memset(ps_params, 0, sizeof (ps_params)); ps_params[0].buffer_type = MYSQL_TYPE_LONG; ps_params[0].buffer = (char *) &int_data[0]; ps_params[0].length = 0; ps_params[0].is_null = 0; ps_params[1].buffer_type = MYSQL_TYPE_LONG; ps_params[1].buffer = (char *) &int_data[1]; ps_params[1].length = 0; ps_params[1].is_null = 0; ps_params[2].buffer_type = MYSQL_TYPE_LONG; ps_params[2].buffer = (char *) &int_data[2]; ps_params[2].length = 0; ps_params[2].is_null = 0; /* bind parameters */ status = mysql_stmt_bind_param(stmt, ps_params); test_stmt_error(stmt, status); /* assign values to parameters and execute statement */ int_data[0]= 10; /* p_in */ int_data[1]= 20; /* p_out */ int_data[2]= 30; /* p_inout */ status = mysql_stmt_execute(stmt); test_stmt_error(stmt, status); /* process results until there are no more */ do { int i; int num_fields; /* number of columns in result */ MYSQL_FIELD *fields; /* for result set metadata */ MYSQL_BIND *rs_bind; /* for output buffers */ /* the column count is > 0 if there is a result set */ /* 0 if the result is only the final status packet */ num_fields = mysql_stmt_field_count(stmt); if (num_fields > 0) { /* there is a result set to fetch */ printf("Number of columns in result: %d\n", (int) num_fields); /* what kind of result set is this? */ printf("Data: "); if(mysql->server_status & SERVER_PS_OUT_PARAMS) printf("this result set contains OUT/INOUT parameters\n"); else printf("this result set is produced by the procedure\n"); MYSQL_RES *rs_metadata = mysql_stmt_result_metadata(stmt); test_stmt_error(stmt, rs_metadata == NULL); fields = mysql_fetch_fields(rs_metadata); rs_bind = (MYSQL_BIND *) malloc(sizeof (MYSQL_BIND) * num_fields); if (!rs_bind) { printf("Cannot allocate output buffers\n"); exit(1); } memset(rs_bind, 0, sizeof (MYSQL_BIND) * num_fields); /* set up and bind result set output buffers */ for (i = 0; i < num_fields; ++i) { rs_bind[i].buffer_type = fields[i].type; rs_bind[i].is_null = &is_null[i]; switch (fields[i].type) { case MYSQL_TYPE_LONG: rs_bind[i].buffer = (char *) &(int_data[i]); rs_bind[i].buffer_length = sizeof (int_data); break; default: fprintf(stderr, "ERROR: unexpected type: %d.\n", fields[i].type); exit(1); } } status = mysql_stmt_bind_result(stmt, rs_bind); test_stmt_error(stmt, status); /* fetch and display result set rows */ while (1) { status = mysql_stmt_fetch(stmt); if (status == 1 || status == MYSQL_NO_DATA) break; for (i = 0; i < num_fields; ++i) { switch (rs_bind[i].buffer_type) { case MYSQL_TYPE_LONG: if (*rs_bind[i].is_null) printf(" val[%d] = NULL;", i); else printf(" val[%d] = %ld;", i, (long) *((int *) rs_bind[i].buffer)); break; default: printf(" unexpected type (%d)\n", rs_bind[i].buffer_type); } } printf("\n"); } mysql_free_result(rs_metadata); /* free metadata */ free(rs_bind); /* free output buffers */ } else { /* no columns = final status packet */ printf("End of procedure output\n"); } /* more results? -1 = no, >0 = error, 0 = yes (keep looking) */ status = mysql_stmt_next_result(stmt); if (status > 0) test_stmt_error(stmt, status); } while (status == 0); mysql_stmt_close(stmt);
Execution of the procedure should produce the following output:
Number of columns in result: 3 Data: this result set is produced by the procedure val[0] = 10; val[1] = NULL; val[2] = 30; Number of columns in result: 3 Data: this result set is produced by the procedure val[0] = 100; val[1] = 200; val[2] = 300; Number of columns in result: 2 Data: this result set contains OUT/INOUT parameters val[0] = 200; val[1] = 300; End of procedure output
The code uses two utility routines,
test_error()
and
test_stmt_error()
, to check for errors and
terminate after printing diagnostic information if an error
occurred:
static void test_error(MYSQL *mysql, int status) { if (status) { fprintf(stderr, "Error: %s (errno: %d)\n", mysql_error(mysql), mysql_errno(mysql)); exit(1); } } static void test_stmt_error(MYSQL_STMT *stmt, int status) { if (status) { fprintf(stderr, "Error: %s (errno: %d)\n", mysql_stmt_error(stmt), mysql_stmt_errno(stmt)); exit(1); } }