Movatterモバイル変換

This section describes prepared-statement support in the C API for stored procedures executed usingCALL statements:

Stored procedures executed using preparedCALL statements can be used in the following ways:

The following discussion shows how to use these capabilities through the C API for prepared statements. To use preparedCALL statements through thePREPARE andEXECUTE statements, seeCALL Statement.

An application that executes a preparedCALL statement should use a loop that fetches a result and then invokesmysql_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 hasOUT orINOUT parameters, the result set preceding the final status value contains their values. To determine whether a result set contains parameter values, test whether theSERVER_PS_OUT_PARAMS bit is set in theserver_status member of theMYSQL connection handler:

mysql->server_status & SERVER_PS_OUT_PARAMS

The following example uses a preparedCALL statement to execute a stored procedure that produces multiple result sets and that provides parameter values back to the caller by means ofOUT andINOUT 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:

The code to execute the procedure:

MYSQL_STMT *stmt;MYSQL_BIND ps_params[3];  /* input parameter buffers */int        int_data[3];   /* input/output values */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: 3Data: this result set is produced by the procedure val[0] = 10; val[1] = NULL; val[2] = 30;Number of columns in result: 3Data: this result set is produced by the procedure val[0] = 100; val[1] = 200; val[2] = 300;Number of columns in result: 2Data: 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() andtest_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);  }}