This section describes prepared-statement support in the C API for
stored procedures executed via CALL
statements:
Prior to MySQL 5.5.3, prepared CALL
statements can be used only for stored procedures that produce at
most one result set. Nor can the calling application use
placeholders for OUT
or
INOUT
parameters.
MySQL 5.5.3 expands support for stored procedures executed via
prepared CALL
statements 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.
The following discussion shows how to use these capabilities via
the C API for prepared statements. To use prepared
CALL
statements via the
PREPARE
and
EXECUTE
statements, see
Section 12.2.1, “CALL
Syntax”.
If an application might be compiled or executed in a context where
a version of MySQL older than 5.5.3 is used, prepared
CALL
capabilities for multiple
result sets and OUT
or INOUT
parameters might not be available:
For the client side, the application will not compile unless the libraries are from MySQL 5.5.3 or higher (the API function and symbols introduced in that version will not be present).
To verify at runtime that the server is recent enough, a client can use this test:
if (mysql_get_server_version(mysql) < 50503) { fprintf(stderr, "Server does not support required CALL capabilities\n"); mysql_close(mysql); exit (1); }
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 containing 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 containing 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 parameter values */ my_bool is_null[3]; /* input parameter 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_inout */ 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()
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) { printf("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) { printf("Error: %s (errno: %d)\n", mysql_stmt_error(stmt), mysql_stmt_errno(stmt)); exit(1); } }
User Comments
Add your own comment.