mysql_client_test.c

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

static void test_select_show_table()
{
  MYSQL_STMT *stmt;
  int        rc, i;

  myheader("test_select_show_table");

  stmt= mysql_simple_prepare(mysql, "SHOW TABLES FROM mysql");
  check_stmt(stmt);

  verify_param_count(stmt, 0);

  for (i= 1; i < 3; i++)
  {
    rc= mysql_stmt_execute(stmt);
    check_execute(stmt, rc);
  }

  my_process_stmt_result(stmt);
  mysql_stmt_close(stmt);
}


/* Test simple select to debug */

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

  myheader("test_select_direct");

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

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

  rc= mysql_query(mysql, "CREATE TABLE test_select(id int, id1 tinyint, "
                                                 " id2 float, "
                                                 " id3 double, "
                                                 " name varchar(50))");
  myquery(rc);

  /* insert a row and commit the transaction */
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 5, 2.3, 4.5, 'venu')");
  myquery(rc);

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

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

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

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


/* Test simple select with prepare */

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

  myheader("test_select_prepare");

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

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

  rc= mysql_query(mysql, "CREATE TABLE test_select(id int, name varchar(50))");
  myquery(rc);

  /* insert a row and commit the transaction */
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 'venu')");
  myquery(rc);

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

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);
  mysql_stmt_close(stmt);

  rc= mysql_query(mysql, "DROP TABLE test_select");
  myquery(rc);

  rc= mysql_query(mysql, "CREATE TABLE test_select(id tinyint, id1 int, "
                                                "  id2 float, id3 float, "
                                                "  name varchar(50))");
  myquery(rc);

  /* insert a row and commit the transaction */
  rc= mysql_query(mysql, "INSERT INTO test_select(id, id1, id2, name) VALUES(10, 5, 2.3, 'venu')");
  myquery(rc);

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

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);
  mysql_stmt_close(stmt);
}


/* Test simple select */

static void test_select()
{
  MYSQL_STMT *stmt;
  int        rc;
  char       szData[25];
  int        nData= 1;
  MYSQL_BIND bind[2];
  ulong length[2];

  myheader("test_select");

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

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

  rc= mysql_query(mysql, "CREATE TABLE test_select(id int, name varchar(50))");
  myquery(rc);

  /* insert a row and commit the transaction */
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES(10, 'venu')");
  myquery(rc);

  /* now insert the second row, and roll back the transaction */
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES(20, 'mysql')");
  myquery(rc);

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

  strmov(query, "SELECT * FROM test_select WHERE id= ? "
                "AND CONVERT(name USING utf8) =?");
  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));

  /* string data */
  nData= 10;
  strmov(szData, (char *)"venu");
  bind[1].buffer_type= MYSQL_TYPE_STRING;
  bind[1].buffer= (void *)szData;
  bind[1].buffer_length= 4;
  bind[1].length= &length[1];
  length[1]= 4;

  bind[0].buffer= (void *)&nData;
  bind[0].buffer_type= MYSQL_TYPE_LONG;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);

  mysql_stmt_close(stmt);
}


/*
  Test for BUG#3420 ("select id1, value1 from t where id= ? or value= ?"
  returns all rows in the table)
*/

static void test_ps_conj_select()
{
  MYSQL_STMT *stmt;
  int        rc;
  MYSQL_BIND bind[2];
  int32      int_data;
  char       str_data[32];
  unsigned long str_length;
  myheader("test_ps_conj_select");

  rc= mysql_query(mysql, "drop table if exists t1");
  myquery(rc);

  rc= mysql_query(mysql, "create table t1 (id1 int(11) NOT NULL default '0', "
                         "value2 varchar(100), value1 varchar(100))");
  myquery(rc);

  rc= mysql_query(mysql, "insert into t1 values (1, 'hh', 'hh'), "
                          "(2, 'hh', 'hh'), (1, 'ii', 'ii'), (2, 'ii', 'ii')");
  myquery(rc);

  strmov(query, "select id1, value1 from t1 where id1= ? or "
                "CONVERT(value1 USING utf8)= ?");
  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].buffer= (void *)&int_data;

  bind[1].buffer_type= MYSQL_TYPE_VAR_STRING;
  bind[1].buffer= (void *)str_data;
  bind[1].buffer_length= array_elements(str_data);
  bind[1].length= &str_length;

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

  int_data= 1;
  strmov(str_data, "hh");
  str_length= strlen(str_data);

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 3);

  mysql_stmt_close(stmt);
}


/* Test BUG#1115 (incorrect string parameter value allocation) */

static void test_bug1115()
{
  MYSQL_STMT *stmt;
  int rc;
  MYSQL_BIND bind[1];
  ulong length[1];
  char szData[11];

  myheader("test_bug1115");

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

  rc= mysql_query(mysql, "CREATE TABLE test_select(\
session_id  char(9) NOT NULL, \
    a       int(8) unsigned NOT NULL, \
    b        int(5) NOT NULL, \
    c      int(5) NOT NULL, \
    d  datetime NOT NULL)");
  myquery(rc);
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES "
                         "(\"abc\", 1, 2, 3, 2003-08-30), "
                         "(\"abd\", 1, 2, 3, 2003-08-30), "
                         "(\"abf\", 1, 2, 3, 2003-08-30), "
                         "(\"abg\", 1, 2, 3, 2003-08-30), "
                         "(\"abh\", 1, 2, 3, 2003-08-30), "
                         "(\"abj\", 1, 2, 3, 2003-08-30), "
                         "(\"abk\", 1, 2, 3, 2003-08-30), "
                         "(\"abl\", 1, 2, 3, 2003-08-30), "
                         "(\"abq\", 1, 2, 3, 2003-08-30) ");
  myquery(rc);
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES "
                         "(\"abw\", 1, 2, 3, 2003-08-30), "
                         "(\"abe\", 1, 2, 3, 2003-08-30), "
                         "(\"abr\", 1, 2, 3, 2003-08-30), "
                         "(\"abt\", 1, 2, 3, 2003-08-30), "
                         "(\"aby\", 1, 2, 3, 2003-08-30), "
                         "(\"abu\", 1, 2, 3, 2003-08-30), "
                         "(\"abi\", 1, 2, 3, 2003-08-30), "
                         "(\"abo\", 1, 2, 3, 2003-08-30), "
                         "(\"abp\", 1, 2, 3, 2003-08-30), "
                         "(\"abz\", 1, 2, 3, 2003-08-30), "
                         "(\"abx\", 1, 2, 3, 2003-08-30)");
  myquery(rc);

  strmov(query, "SELECT * FROM test_select WHERE "
                "CONVERT(session_id USING utf8)= ?");
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  verify_param_count(stmt, 1);

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

  strmov(szData, (char *)"abc");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 3;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);

  strmov(szData, (char *)"venu");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 4;
  bind[0].is_null= 0;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 0);

  strmov(szData, (char *)"abc");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 3;
  bind[0].is_null= 0;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);

  mysql_stmt_close(stmt);
}


/* Test BUG#1180 (optimized away part of WHERE clause) */

static void test_bug1180()
{
  MYSQL_STMT *stmt;
  int rc;
  MYSQL_BIND bind[1];
  ulong length[1];
  char szData[11];

  myheader("test_select_bug");

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

  rc= mysql_query(mysql, "CREATE TABLE test_select(session_id  char(9) NOT NULL)");
  myquery(rc);
  rc= mysql_query(mysql, "INSERT INTO test_select VALUES (\"abc\")");
  myquery(rc);

  strmov(query, "SELECT * FROM test_select WHERE ?= \"1111\" and "
                "session_id= \"abc\"");
  stmt= mysql_simple_prepare(mysql, query);
  check_stmt(stmt);

  verify_param_count(stmt, 1);

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

  strmov(szData, (char *)"abc");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 3;
  bind[0].is_null= 0;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 0);

  strmov(szData, (char *)"1111");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 4;
  bind[0].is_null= 0;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 1);

  strmov(szData, (char *)"abc");
  bind[0].buffer_type= MYSQL_TYPE_STRING;
  bind[0].buffer= (void *)szData;
  bind[0].buffer_length= 10;
  bind[0].length= &length[0];
  length[0]= 3;
  bind[0].is_null= 0;

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

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

  rc= my_process_stmt_result(stmt);
  DIE_UNLESS(rc == 0);

  mysql_stmt_close(stmt);
}


/*
  Test BUG#1644 (Insertion of more than 3 NULL columns with parameter
  binding fails)
*/

static void test_bug1644()
{
  MYSQL_STMT *stmt;
  MYSQL_RES *result;
  MYSQL_ROW row;
  MYSQL_BIND bind[4];
  int num;
  my_bool isnull;
  int rc, i;

  myheader("test_bug1644");

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

  rc= mysql_query(mysql,
           "CREATE TABLE foo_dfr(col1 int, col2 int, col3 int, col4 int);");
  myquery(rc);

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

  verify_param_count(stmt, 4);

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

  num= 22;
  isnull= 0;
  for (i= 0 ; i < 4 ; i++)
  {
    bind[i].buffer_type= MYSQL_TYPE_LONG;
    bind[i].buffer= (void *)&num;
    bind[i].is_null= &isnull;
  }

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

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

  isnull= 1;
  for (i= 0 ; i < 4 ; i++)
    bind[i].is_null= &isnull;

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

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

  isnull= 0;
  num= 88;
  for (i= 0 ; i < 4 ; i++)
    bind[i].is_null= &isnull;

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

  rc= mysql_stmt_execute(stmt);
  check_e