mysql_client_test.c

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

  rc= mysql_autocommit(mysql, TRUE);
  myquery(rc);

  rc= mysql_query(mysql, "DROP TABLE IF EXISTS my_prepare");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE my_prepare(col1 tinyint, "
                         "col2 varchar(15), col3 int, "
                         "col4 smallint, col5 bigint, "
                         "col6 float, col7 double )");
  myquery(rc);

  /* insert by prepare */
  strxmov(query, "INSERT INTO my_prepare VALUES(?, ?, ?, ?, ?, ?, ?)", NullS);
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  verify_param_count(stmt, 7);

  bzero((char*) bind, sizeof(bind));

  /* tinyint */
  bind[0].buffer_type= MYSQL_TYPE_TINY;
  bind[0].buffer= (void *)&tiny_data;
  /* string */
  bind[1].buffer_type= MYSQL_TYPE_STRING;
  bind[1].buffer= (void *)str_data;
  bind[1].buffer_length= 1000;                  /* Max string length */
  /* integer */
  bind[2].buffer_type= MYSQL_TYPE_LONG;
  bind[2].buffer= (void *)&int_data;
  /* short */
  bind[3].buffer_type= MYSQL_TYPE_SHORT;
  bind[3].buffer= (void *)&small_data;
  /* bigint */
  bind[4].buffer_type= MYSQL_TYPE_LONGLONG;
  bind[4].buffer= (void *)&big_data;
  /* float */
  bind[5].buffer_type= MYSQL_TYPE_FLOAT;
  bind[5].buffer= (void *)&real_data;
  /* double */
  bind[6].buffer_type= MYSQL_TYPE_DOUBLE;
  bind[6].buffer= (void *)&double_data;

  for (i= 0; i < (int) array_elements(bind); i++)
  {
    bind[i].length= &length[i];
    bind[i].is_null= &is_null[i];
    is_null[i]= 0;
  }

  rc= mysql_stmt_bind_param(stmt, bind);
  check_execute(stmt, rc);

  int_data= 320;
  small_data= 1867;
  big_data= 1000;
  real_data= 2;
  double_data= 6578.001;

  /* now, execute the prepared statement to insert 10 records.. */
  for (tiny_data= 0; tiny_data < 100; tiny_data++)
  {
    length[1]= my_sprintf(str_data, (str_data, "MySQL%d", int_data));
    rc= mysql_stmt_execute(stmt);
    check_execute(stmt, rc);
    int_data += 25;
    small_data += 10;
    big_data += 100;
    real_data += 1;
    double_data += 10.09;
  }

  mysql_stmt_close(stmt);

  /* now fetch the results ..*/
  rc= mysql_commit(mysql);
  myquery(rc);

  /* test the results now, only one row should exist */
  rc= my_stmt_result("SELECT * FROM my_prepare");
  DIE_UNLESS(tiny_data == (char) rc);

  stmt= mysql_simple_prepare(mysql, "SELECT * FROM my_prepare");
  check_stmt(stmt);

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

  /* get the result */
  rc= mysql_stmt_execute(stmt);
  check_execute(stmt, rc);

  o_int_data= 320;
  o_small_data= 1867;
  o_big_data= 1000;
  o_real_data= 2;
  o_double_data= 6578.001;

  /* now, execute the prepared statement to insert 10 records.. */
  for (o_tiny_data= 0; o_tiny_data < 100; o_tiny_data++)
  {
    len= my_sprintf(data, (data, "MySQL%d", o_int_data));

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

    if (!opt_silent)
    {
      fprintf(stdout, "\n");
      fprintf(stdout, "\n\t tiny   : %d (%lu)", tiny_data, length[0]);
      fprintf(stdout, "\n\t short  : %d (%lu)", small_data, length[3]);
      fprintf(stdout, "\n\t int    : %d (%lu)", int_data, length[2]);
      fprintf(stdout, "\n\t big    : %s (%lu)", llstr(big_data, llbuf),
              length[4]);

      fprintf(stdout, "\n\t float  : %f (%lu)", real_data, length[5]);
      fprintf(stdout, "\n\t double : %f (%lu)", double_data, length[6]);

      fprintf(stdout, "\n\t str    : %s (%lu)", str_data, length[1]);
    }

    DIE_UNLESS(tiny_data == o_tiny_data);
    DIE_UNLESS(is_null[0] == 0);
    DIE_UNLESS(length[0] == 1);

    DIE_UNLESS(int_data == o_int_data);
    DIE_UNLESS(length[2] == 4);

    DIE_UNLESS(small_data == o_small_data);
    DIE_UNLESS(length[3] == 2);

    DIE_UNLESS(big_data == o_big_data);
    DIE_UNLESS(length[4] == 8);

    DIE_UNLESS(real_data == o_real_data);
    DIE_UNLESS(length[5] == 4);

    DIE_UNLESS(cmp_double(&double_data, &o_double_data));
    DIE_UNLESS(length[6] == 8);

    DIE_UNLESS(strcmp(data, str_data) == 0);
    DIE_UNLESS(length[1] == len);

    o_int_data += 25;
    o_small_data += 10;
    o_big_data += 100;
    o_real_data += 1;
    o_double_data += 10.09;
  }

  rc= mysql_stmt_fetch(stmt);
  DIE_UNLESS(rc == MYSQL_NO_DATA);

  mysql_stmt_close(stmt);

}


/* Test double comparision */

static void test_double_compare()
{
  MYSQL_STMT *stmt;
  int        rc;
  char       real_data[10], tiny_data;
  double     double_data;
  MYSQL_RES  *result;
  MYSQL_BIND bind[3];
  ulong      length[3];

  myheader("test_double_compare");

  rc= mysql_autocommit(mysql, TRUE);
  myquery(rc);

  rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_double_compare");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE test_double_compare(col1 tinyint, "
                         " col2 float, col3 double )");
  myquery(rc);

  rc= mysql_query(mysql, "INSERT INTO test_double_compare "
                         "VALUES (1, 10.2, 34.5)");
  myquery(rc);

  strmov(query, "UPDATE test_double_compare SET col1=100 "
                "WHERE col1 = ? AND col2 = ? AND COL3 = ?");
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  verify_param_count(stmt, 3);

  /* Always bzero bind array because there can be internal members */
  bzero((char*) bind, sizeof(bind));

  /* tinyint */
  bind[0].buffer_type= MYSQL_TYPE_TINY;
  bind[0].buffer= (void *)&tiny_data;

  /* string->float */
  bind[1].buffer_type= MYSQL_TYPE_STRING;
  bind[1].buffer= (void *)&real_data;
  bind[1].buffer_length= sizeof(real_data);
  bind[1].length= &length[1];
  length[1]= 10;

  /* double */
  bind[2].buffer_type= MYSQL_TYPE_DOUBLE;
  bind[2].buffer= (void *)&double_data;

  tiny_data= 1;
  strmov(real_data, "10.2");
  double_data= 34.5;
  rc= mysql_stmt_bind_param(stmt, bind);
  check_execute(stmt, rc);

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

  verify_affected_rows(0);

  mysql_stmt_close(stmt);

  /* now fetch the results ..*/
  rc= mysql_commit(mysql);
  myquery(rc);

  /* test the results now, only one row should exist */
  rc= mysql_query(mysql, "SELECT * FROM test_double_compare");
  myquery(rc);

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

  rc= my_process_result_set(result);
  DIE_UNLESS((int)tiny_data == rc);
  mysql_free_result(result);
}


/* Test simple null */

static void test_null()
{
  MYSQL_STMT *stmt;
  int        rc;
  uint       nData;
  MYSQL_BIND bind[2];
  my_bool    is_null[2];

  myheader("test_null");

  rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_null");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE test_null(col1 int, col2 varchar(50))");
  myquery(rc);

  /* insert by prepare, wrong column name */
  strmov(query, "INSERT INTO test_null(col3, col2) VALUES(?, ?)");
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt_r(stmt);

  strmov(query, "INSERT INTO test_null(col1, col2) VALUES(?, ?)");
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  verify_param_count(stmt, 2);

  /* Always bzero all members of bind parameter */
  bzero((char*) bind, sizeof(bind));

  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].is_null= &is_null[0];
  is_null[0]= 1;
  bind[1]= bind[0];

  rc= mysql_stmt_bind_param(stmt, bind);
  check_execute(stmt, rc);

  /* now, execute the prepared statement to insert 10 records.. */
  for (nData= 0; nData<10; nData++)
  {
    rc= mysql_stmt_execute(stmt);
    check_execute(stmt, rc);
  }

  /* Re-bind with MYSQL_TYPE_NULL */
  bind[0].buffer_type= MYSQL_TYPE_NULL;
  is_null[0]= 0; /* reset */
  bind[1]= bind[0];

  rc= mysql_stmt_bind_param(stmt, bind);
  check_execute(stmt, rc);

  for (nData= 0; nData<10; nData++)
  {
    rc= mysql_stmt_execute(stmt);
    check_execute(stmt, rc);
  }

  mysql_stmt_close(stmt);

  /* now fetch the results ..*/
  rc= mysql_commit(mysql);
  myquery(rc);

  nData*= 2;
  rc= my_stmt_result("SELECT * FROM test_null");;
  DIE_UNLESS((int) nData == rc);

  /* Fetch results */
  bind[0].buffer_type= MYSQL_TYPE_LONG;
  bind[0].buffer= (void *)&nData; /* this buffer won't be altered */
  bind[0].length= 0;
  bind[1]= bind[0];
  bind[0].is_null= &is_null[0];
  bind[1].is_null= &is_null[1];

  stmt= mysql_simple_prepare(mysql, "SELECT * FROM test_null");
  check_stmt(stmt);

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

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

  rc= 0;
  is_null[0]= is_null[1]= 0;
  while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
  {
    DIE_UNLESS(is_null[0]);
    DIE_UNLESS(is_null[1]);
    rc++;
    is_null[0]= is_null[1]= 0;
  }
  DIE_UNLESS(rc == (int) nData);
  mysql_stmt_close(stmt);
}


/* Test for NULL as PS parameter (BUG#3367, BUG#3371) */

static void test_ps_null_param()
{
  MYSQL_STMT *stmt;
  int        rc;

  MYSQL_BIND in_bind;
  my_bool    in_is_null;
  long int   in_long;

  MYSQL_BIND out_bind;
  ulong      out_length;
  my_bool    out_is_null;
  char       out_str_data[20];

  const char *queries[]= {"select ?", "select ?+1",
                    "select col1 from test_ps_nulls where col1 <=> ?",
                    NULL
                    };
  const char **cur_query= queries;

  myheader("test_null_ps_param_in_result");

  rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_ps_nulls");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE test_ps_nulls(col1 int)");
  myquery(rc);

  rc= mysql_query(mysql, "INSERT INTO test_ps_nulls values (1), (null)");
  myquery(rc);

  /* Always bzero all members of bind parameter */
  bzero((char*) &in_bind, sizeof(in_bind));
  bzero((char*) &out_bind, sizeof(out_bind));

  in_bind.buffer_type= MYSQL_TYPE_LONG;
  in_bind.is_null= &in_is_null;
  in_bind.length= 0;
  in_bind.buffer= (void *)&in_long;
  in_is_null= 1;
  in_long= 1;

  out_bind.buffer_type= MYSQL_TYPE_STRING;
  out_bind.is_null= &out_is_null;
  out_bind.length= &out_length;
  out_bind.buffer= out_str_data;
  out_bind.buffer_length= array_elements(out_str_data);

  /* Execute several queries, all returning NULL in result. */
  for(cur_query= queries; *cur_query; cur_query++)
  {
    strmov(query, *cur_query);
    stmt= mysql_simple_prepare(mysql, query);
    check_stmt(stmt);
    verify_param_count(stmt, 1);

    rc= mysql_stmt_bind_param(stmt, &in_bind);
    check_execute(stmt, rc);
    rc= mysql_stmt_bind_result(stmt, &out_bind);
    check_execute(stmt, rc);
    rc= mysql_stmt_execute(stmt);
    check_execute(stmt, rc);
    rc= mysql_stmt_fetch(stmt);
    DIE_UNLESS(rc != MYSQL_NO_DATA);
    DIE_UNLESS(out_is_null);
    rc= mysql_stmt_fetch(stmt);
    DIE_UNLESS(rc == MYSQL_NO_DATA);
    mysql_stmt_close(stmt);
  }
}


/* Test fetch null */

static void test_fetch_null()
{
  MYSQL_STMT *stmt;
  int        rc;
  int        i, nData;
  MYSQL_BIND bind[11];
  ulong      length[11];
  my_bool    is_null[11];

  myheader("test_fetch_null");

  rc= mysql_query(mysql, "DROP TABLE IF EXISTS test_fetch_null");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE test_fetch_null("
                         " col1 tinyint, col2 smallint, "
                         " col3 int, col4 bigint, "
                         " col5 float, col6 double, "
                         " col7 date, col8 time, "
                         " col9 varbinary(10), "
                         " col10 varchar(50), "
                         " col11 char(20))");
  myquery(rc);

  rc= mysql_query(mysql, "INSERT INTO test_fetch_null (col11) "
                         "VALUES (1000), (88), (389789)");
  myquery(rc);

  rc= mysql_commit(mysql);
  myquery(rc);

  /* fetch */
  bzero((char*) bind, sizeof(bind));
  for (i= 0; i < (int) array_elements(bind); i++)
  {
    bind[i].buffer_type= MYSQL_TYPE_LONG;
    bind[i].is_null= &is_null[i];
    bind[i].length= &length[i];
  }
  bind[i-1].buffer= (void *)&nData;              /* Last column is not null */

  strmov((char *)query , "SELECT * FROM test_fetch_null");

  rc= my_stmt_result(query);
  DIE_UNLESS(rc == 3);

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

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

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

  rc= 0;
  while (mysql_stmt_fetch(stmt) != MYSQL_NO_DATA)
  {
    rc++;
    for (i= 0; i < 10; i++)
    {
      if (!opt_silent)
        fprintf(stdout, "\n data[%d] : %s", i,
                is_null[i] ? "NULL" : "NOT NULL");
      DIE_UNLESS(is_null[i]);
    }
    if (!opt_silent)
      fprintf(stdout, "\n data[%d]: %d", i, nData);
    DIE_UNLESS(nData == 1000 || nData == 88 || nData == 389789);
    DIE_UNLESS(is_null[i] == 0);
    DIE_UNLESS(length[i] == 4);
  }
  DIE_UNLESS(rc == 3);
  mysql_stmt_close(stmt);
}


/* Test simple select */

static void test_select_version()
{
  MYSQL_STMT *stmt;
  int        rc;

  myheader("test_select_version");

  stmt= mysql_simple_prepare(mysql, "SELECT @@version");
  check_stmt(stmt);

  verify_param_count(stmt, 0);

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

  my_process_stmt_result(stmt);
  mysql_stmt_close(stmt);
}


/* Test simple show */