mysql_client_test.c

几个嵌入式MYSQL编程示例

{
  MYSQL_RES *result;
  int       row_count;

  if (!(result= mysql_store_result(mysql)))
    return 0;

  row_count= my_process_result_set(result);

  mysql_free_result(result);
  return row_count;
}


/* Process the statement result set */

#define MAX_RES_FIELDS 50
#define MAX_FIELD_DATA_SIZE 255

int my_process_stmt_result(MYSQL_STMT *stmt)
{
  int         field_count;
  int         row_count= 0;
  MYSQL_BIND  buffer[MAX_RES_FIELDS];
  MYSQL_FIELD *field;
  MYSQL_RES   *result;
  char        data[MAX_RES_FIELDS][MAX_FIELD_DATA_SIZE];
  ulong       length[MAX_RES_FIELDS];
  my_bool     is_null[MAX_RES_FIELDS];
  int         rc, i;

  if (!(result= mysql_stmt_result_metadata(stmt))) /* No meta info */
  {
    while (!mysql_stmt_fetch(stmt))
      row_count++;
    return row_count;
  }

  field_count= min(mysql_num_fields(result), MAX_RES_FIELDS);

  bzero((char*) buffer, sizeof(buffer));
  bzero((char*) length, sizeof(length));
  bzero((char*) is_null, sizeof(is_null));

  for(i= 0; i < field_count; i++)
  {
    buffer[i].buffer_type= MYSQL_TYPE_STRING;
    buffer[i].buffer_length= MAX_FIELD_DATA_SIZE;
    buffer[i].length= &length[i];
    buffer[i].buffer= (void *) data[i];
    buffer[i].is_null= &is_null[i];
  }

  rc= mysql_stmt_bind_result(stmt, buffer);
  check_execute(stmt, rc);

  rc= 1;
  mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, (void*)&rc);
  rc= mysql_stmt_store_result(stmt);
  check_execute(stmt, rc);
  my_print_result_metadata(result);

  mysql_field_seek(result, 0);
  while ((rc= mysql_stmt_fetch(stmt)) == 0)
  {
    if (!opt_silent)
    {
      fputc('\t', stdout);
      fputc('|', stdout);
    }
    mysql_field_seek(result, 0);
    for (i= 0; i < field_count; i++)
    {
      field= mysql_fetch_field(result);
      if (!opt_silent)
      {
        if (is_null[i])
          fprintf(stdout, " %-*s |", (int) field->max_length, "NULL");
        else if (length[i] == 0)
        {
          data[i][0]= '\0';  /* unmodified buffer */
          fprintf(stdout, " %*s |", (int) field->max_length, data[i]);
        }
        else if (IS_NUM(field->type))
          fprintf(stdout, " %*s |", (int) field->max_length, data[i]);
        else
          fprintf(stdout, " %-*s |", (int) field->max_length, data[i]);
      }
    }
    if (!opt_silent)
    {
      fputc('\t', stdout);
      fputc('\n', stdout);
    }
    row_count++;
  }
  DIE_UNLESS(rc == MYSQL_NO_DATA);
  if (!opt_silent)
  {
    if (row_count)
      my_print_dashes(result);
    fprintf(stdout, "\n\t%d %s returned\n", row_count,
            row_count == 1 ? "row" : "rows");
  }
  mysql_free_result(result);
  return row_count;
}


/* Prepare statement, execute, and process result set for given query */

int my_stmt_result(const char *buff)
{
  MYSQL_STMT *stmt;
  int        row_count;
  int        rc;

  if (!opt_silent)
    fprintf(stdout, "\n\n %s", buff);
  stmt= mysql_simple_prepare(mysql, buff);
  check_stmt(stmt);

  rc= mysql_stmt_execute(stmt);
  check_execute(stmt, rc);

  row_count= my_process_stmt_result(stmt);
  mysql_stmt_close(stmt);

  return row_count;
}


/* Utility function to verify a particular column data */

static void verify_col_data(const char *table, const char *col,
                            const char *exp_data)
{
  MYSQL_RES *result;
  MYSQL_ROW row;
  int       rc, field= 1;

  if (table && col)
  {
    strxmov(query, "SELECT ", col, " FROM ", table, " LIMIT 1", NullS);
    if (!opt_silent)
      fprintf(stdout, "\n %s", query);
    rc= mysql_query(mysql, query);
    myquery(rc);

    field= 0;
  }

  result= mysql_use_result(mysql);
  mytest(result);

  if (!(row= mysql_fetch_row(result)) || !row[field])
  {
    fprintf(stdout, "\n *** ERROR: FAILED TO GET THE RESULT ***");
    exit(1);
  }
  if (strcmp(row[field], exp_data))
  {
    fprintf(stdout, "\n obtained: `%s` (expected: `%s`)",
            row[field], exp_data);
    DIE_UNLESS(FALSE);
  }
  mysql_free_result(result);
}


/* Utility function to verify the field members */

#define verify_prepare_field(result,no,name,org_name,type,table,\
                             org_table,db,length,def) \
          do_verify_prepare_field((result),(no),(name),(org_name),(type), \
                                  (table),(org_table),(db),(length),(def), \
                                  __FILE__, __LINE__)

static void do_verify_prepare_field(MYSQL_RES *result,
                                   unsigned int no, const char *name,
                                   const char *org_name,
                                   enum enum_field_types type,
                                   const char *table,
                                   const char *org_table, const char *db,
                                   unsigned long length, const char *def,
                                   const char *file, int line)
{
  MYSQL_FIELD *field;
  CHARSET_INFO *cs;

  if (!(field= mysql_fetch_field_direct(result, no)))
  {
    fprintf(stdout, "\n *** ERROR: FAILED TO GET THE RESULT ***");
    exit(1);
  }
  cs= get_charset(field->charsetnr, 0);
  DIE_UNLESS(cs);
  if (!opt_silent)
  {
    fprintf(stdout, "\n field[%d]:", no);
    fprintf(stdout, "\n    name     :`%s`\t(expected: `%s`)", field->name, name);
    fprintf(stdout, "\n    org_name :`%s`\t(expected: `%s`)",
            field->org_name, org_name);
    fprintf(stdout, "\n    type     :`%d`\t(expected: `%d`)", field->type, type);
    if (table)
      fprintf(stdout, "\n    table    :`%s`\t(expected: `%s`)",
              field->table, table);
    if (org_table)	      
      fprintf(stdout, "\n    org_table:`%s`\t(expected: `%s`)",
              field->org_table, org_table);
    fprintf(stdout, "\n    database :`%s`\t(expected: `%s`)", field->db, db);
    fprintf(stdout, "\n    length   :`%lu`\t(expected: `%lu`)",
            field->length, length * cs->mbmaxlen);
    fprintf(stdout, "\n    maxlength:`%ld`", field->max_length);
    fprintf(stdout, "\n    charsetnr:`%d`", field->charsetnr);
    fprintf(stdout, "\n    default  :`%s`\t(expected: `%s`)",
            field->def ? field->def : "(null)", def ? def: "(null)");
    fprintf(stdout, "\n");
  }
  DIE_UNLESS(strcmp(field->name, name) == 0);
  DIE_UNLESS(strcmp(field->org_name, org_name) == 0);
  /*
    XXX: silent column specification change works based on number of
    bytes a column occupies. So CHAR -> VARCHAR upgrade is possible even
    for CHAR(2) column if its character set is multibyte.
    VARCHAR -> CHAR downgrade won't work for VARCHAR(3) as one would
    expect.
  */
  if (cs->mbmaxlen == 1)
  {
    if (field->type != type)
    {
      fprintf(stderr,
              "Expected field type: %d,  got type: %d in file %s, line %d\n",
              (int) type, (int) field->type, file, line);
      DIE_UNLESS(field->type == type);
    }
  }
  if (table)
    DIE_UNLESS(strcmp(field->table, table) == 0);
  if (org_table)
    DIE_UNLESS(strcmp(field->org_table, org_table) == 0);
  DIE_UNLESS(strcmp(field->db, db) == 0);
  /*
    Character set should be taken into account for multibyte encodings, such
    as utf8. Field length is calculated as number of characters * maximum
    number of bytes a character can occupy.
  */
  if (length && field->length != length * cs->mbmaxlen)
  {
    fprintf(stderr, "Expected field length: %d,  got length: %d\n",
            (int) (length * cs->mbmaxlen), (int) field->length);
    DIE_UNLESS(field->length == length * cs->mbmaxlen);
  }
  if (def)
    DIE_UNLESS(strcmp(field->def, def) == 0);
}


/* Utility function to verify the parameter count */

static void verify_param_count(MYSQL_STMT *stmt, long exp_count)
{
  long param_count= mysql_stmt_param_count(stmt);
  if (!opt_silent)
    fprintf(stdout, "\n total parameters in stmt: `%ld` (expected: `%ld`)",
            param_count, exp_count);
  DIE_UNLESS(param_count == exp_count);
}


/* Utility function to verify the total affected rows */

static void verify_st_affected_rows(MYSQL_STMT *stmt, ulonglong exp_count)
{
  ulonglong affected_rows= mysql_stmt_affected_rows(stmt);
  if (!opt_silent)
    fprintf(stdout, "\n total affected rows: `%ld` (expected: `%ld`)",
            (long) affected_rows, (long) exp_count);
  DIE_UNLESS(affected_rows == exp_count);
}


/* Utility function to verify the total affected rows */

static void verify_affected_rows(ulonglong exp_count)
{
  ulonglong affected_rows= mysql_affected_rows(mysql);
  if (!opt_silent)
    fprintf(stdout, "\n total affected rows: `%ld` (expected: `%ld`)",
            (long) affected_rows, (long) exp_count);
  DIE_UNLESS(affected_rows == exp_count);
}


/* Utility function to verify the total fields count */

static void verify_field_count(MYSQL_RES *result, uint exp_count)
{
  uint field_count= mysql_num_fields(result);
  if (!opt_silent)
    fprintf(stdout, "\n total fields in the result set: `%d` (expected: `%d`)",
            field_count, exp_count);
  DIE_UNLESS(field_count == exp_count);
}


/* Utility function to execute a query using prepare-execute */

#ifndef EMBEDDED_LIBRARY
static void execute_prepare_query(const char *query, ulonglong exp_count)
{
  MYSQL_STMT *stmt;
  ulonglong  affected_rows;
  int        rc;

  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  rc= mysql_stmt_execute(stmt);
  myquery(rc);

  affected_rows= mysql_stmt_affected_rows(stmt);
  if (!opt_silent)
    fprintf(stdout, "\n total affected rows: `%ld` (expected: `%ld`)",
            (long) affected_rows, (long) exp_count);

  DIE_UNLESS(affected_rows == exp_count);
  mysql_stmt_close(stmt);
}
#endif

/* Store result processing */

static void client_store_result()
{
  MYSQL_RES *result;
  int       rc;

  myheader("client_store_result");

  rc= mysql_query(mysql, "SELECT * FROM t1");
  myquery(rc);

  /* get the result */
  result= mysql_store_result(mysql);
  mytest(result);

  (void) my_process_result_set(result);
  mysql_free_result(result);
}


/* Fetch the results */

static void client_use_result()
{
  MYSQL_RES *result;
  int       rc;
  myheader("client_use_result");

  rc= mysql_query(mysql, "SELECT * FROM t1");
  myquery(rc);

  /* get the result */
  result= mysql_use_result(mysql);
  mytest(result);

  (void) my_process_result_set(result);
  mysql_free_result(result);
}


/*
  Accepts arbitrary number of queries and runs them against the database.
  Used to fill tables for each test.
*/

void fill_tables(const char **query_list, unsigned query_count)
{
  int rc;
  const char **query;
  DBUG_ENTER("fill_tables");
  for (query= query_list; query < query_list + query_count;
       ++query)
  {
    rc= mysql_query(mysql, *query);
    myquery(rc);
  }
  DBUG_VOID_RETURN;
}

/*
  All state of fetch from one statement: statement handle, out buffers,
  fetch position.
  See fetch_n for for the only use case.
*/

enum { MAX_COLUMN_LENGTH= 255 };

typedef struct st_stmt_fetch
{
  const char *query;
  unsigned stmt_no;
  MYSQL_STMT *handle;
  my_bool is_open;
  MYSQL_BIND *bind_array;
  char **out_data;
  unsigned long *out_data_length;
  unsigned column_count;
  unsigned row_count;
} Stmt_fetch;


/*
  Create statement handle, prepare it with statement, execute and allocate
  fetch buffers.
*/

void stmt_fetch_init(Stmt_fetch *fetch, unsigned stmt_no_arg,
                     const char *query_arg)
{
  unsigned long type= CURSOR_TYPE_READ_ONLY;
  int rc;
  unsigned i;
  MYSQL_RES *metadata;
  DBUG_ENTER("stmt_fetch_init");

  /* Save query and statement number for error messages */
  fetch->stmt_no= stmt_no_arg;
  fetch->query= query_arg;

  fetch->handle= mysql_stmt_init(mysql);

  rc= mysql_stmt_prepare(fetch->handle, fetch->query, strlen(fetch->query));
  check_execute(fetch->handle, rc);

  /*
    The attribute is sent to server on execute and asks to open read-only
    for result set
  */
  mysql_stmt_attr_set(fetch->handle, STMT_ATTR_CURSOR_TYPE,
                      (const void*) &type);

  rc= mysql_stmt_execute(fetch->handle);
  check_execute(fetch->handle, rc);

  /* Find out total number of columns in result set */
  metadata= mysql_stmt_result_metadata(fetch->handle);
  fetch->column_count= mysql_num_fields(metadata);
  mysql_free_result(metadata);

  /*
    Now allocate bind handles and buffers for output data:
    calloc memory to reduce number of MYSQL_BIND members we need to
    set up.
  */

  fetch->bind_array= (MYSQL_BIND *) calloc(1, sizeof(MYSQL_BIND) *
                                              fetch->column_count);
  fetch->out_data= (char**) calloc(1, sizeof(char*) * fetch->column_count);
  fetch->out_data_length= (ulong*) calloc(1, sizeof(ulong) *
                                             fetch->column_count);
  for (i= 0; i < fetch->column_count; ++i)
  {
    fetch->out_data[i]= (char*) calloc(1, MAX_COLUMN_LENGTH);
    fetch->bind_array[i].buffer_type= MYSQL_TYPE_STRING;
    fetch->bind_array[i].buffer= fetch->out_data[i];
    fetch->bind_array[i].buffer_length= MAX_COLUMN_LENGTH;
    fetch->bind_array[i].length= fetch->out_data_length + i;
  }

  mysql_stmt_bind_result(fetch->handle, fetch->bind_array);

  fetch->row_count= 0;
  fetch->is_open= TRUE;

  /* Ready for reading rows */
  DBUG_VOID_RETURN;
}


/* Fetch and print one row from cursor */

int stmt_fetch_fetch_row(Stmt_fetch *fetch)
{
  int rc;
  unsigned i;
  DBUG_ENTER("stmt_fetch_fetch_row");

  if ((rc= mysql_stmt_fetch(fetch->handle)) == 0)
  {
    ++fetch->row_count;
    if (!opt_silent)
      printf("Stmt %d fetched row %d:\n", fetch->stmt_no, fetch->row_count);
    for (i= 0; i < fetch->column_count; ++i)
    {
      fetch->out_data[i][fetch->out_data_length[i]]= '\0';
      if (!opt_silent)
        printf("column %d: %s\n", i+1, fetch->out_data[i]);
    }
  }
  else
    fetch->is_open= FALSE;
  DBUG_RETURN(rc);
}


void stmt_fetch_close(Stmt_fetch *fetch)
{
  unsigned i;
  DBUG_ENTER("stmt_fetch_close");

  for (i= 0; i < fetch->column_count; ++i)
    free(fetch->out_data[i]);
  free(fetch->out_data);
  free(fetch->out_data_length);
  free(fetch->bind_array);
  mysql_stmt_close(fetch->handle);
  DBUG_VOID_RETURN;
}