mysql预处之insert

时间：2010-05-03 来源：ubuntuer

#include <stdio.h>
#include <unistd.h>
#include <mysql.h>
#include <stdlib.h>
#include <string.h>

#define MAX_TEST_QUERY_LENGTH 300 /* MAX QUERY BUFFER LENGTH */
#define BUFFER_SIZE 1000000
#define bool int
#define true 1
#define false 0
#define PARAM_COUNT 7

bool Connect_Server(MYSQL **sql,char *host,char *user,char *passwd,char *dbname)
{
        my_bool b = 0;
        if(!(*sql=mysql_init(NULL)))
        {
                printf("%s\n",mysql_error(*sql));
                return 0;
        }
        mysql_options(*sql,MYSQL_REPORT_DATA_TRUNCATION,&b);
        if(!mysql_real_connect(*sql,host,user,passwd,dbname,0,NULL,0))
        {
                fprintf(stderr,"%s\n",mysql_error(*sql));
                return 0;
        }
        return 1;// true;

}

bool Stmt_Init(MYSQL *sql,MYSQL_STMT **stmt)
{
     unsigned long prefetch_rows = 5;

        *stmt = mysql_stmt_init(sql);
        if(!(*stmt))
        {
                printf("stmt_init() error!\n%s",mysql_stmt_error(*stmt));
                return false;
        }
        #if 1
         mysql_stmt_attr_set(*stmt, STMT_ATTR_PREFETCH_ROWS,(void*) &prefetch_rows);
        #endif
        return true;
}

bool Stmt_Prepare_Insert(MYSQL *sql,MYSQL_STMT **stmt,char *query)
{
        unsigned count;
        MYSQL_RES *res;
        if(sql==NULL)
        {
                printf("%s\n", "Must connect server first!!");
                return false;
        }

        if(mysql_stmt_prepare(*stmt,query,strlen(query)))
        {
                printf("%s\n", "stmt_perpare()!\n");
                printf("%s\n",query);
                return false;
        }

        ///Get parameter count

        count=mysql_stmt_param_count(*stmt);
        if(count!=PARAM_COUNT)
        {
                printf("%s\n", "parameter count error!");
                return false;
        }

        //Get MYSQL_RES

        res = mysql_stmt_result_metadata(*stmt);
        if(!res)
        {
                printf("%s\n", "Get MYSQL_RES NULL because this is insert!");
            // return false;

        }

        //Column in result

        //column_count = mysql_num_fields(res);

        //printf("res column = %d\n",column_count);

        return true;
}

bool Stmt_Execute(MYSQL_STMT *stmt)
{
        if(mysql_stmt_execute(stmt))
        {
                printf("%s\n", "Excecute()!");
                return false ;
        }
        else return true ;
}

void Stmt_Close(MYSQL_STMT *stmt)
{
        mysql_stmt_close(stmt);
}

void Mysql_Close(MYSQL *sql)
{
        mysql_close(sql);
}

void check_rc(int rc)
{
if(rc!=0)
     exit(0);
    }

static void test_prepare(MYSQL* mysql)
{
  MYSQL_STMT *stmt;
  int rc, i;
  int int_data;
  char str_data[50];
  char tiny_data;
  short small_data;
  long long big_data;
  float real_data;
  double double_data;
  unsigned long length[PARAM_COUNT];
  my_bool is_null[PARAM_COUNT];
  MYSQL_BIND my_bind[PARAM_COUNT];
  char query[MAX_TEST_QUERY_LENGTH];

  printf("%s\n", "#######test_prepare#############");

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

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

  if(!Stmt_Init(mysql,&stmt)) return;

  /* insert by prepare */
  sprintf(query, "INSERT INTO my_prepare VALUES(?, ?, ?, ?, ?, ?, ?)");
  if(!Stmt_Prepare_Insert(mysql,&stmt,query)) return;

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

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

  for (i= 0; i < (int) sizeof(my_bind)/sizeof(my_bind[0]); i++)
  {
    my_bind[i].length= &length[i];
    my_bind[i].is_null= &is_null[i];
    is_null[i]= 0;
  }

  rc= mysql_stmt_bind_param(stmt, my_bind);
  check_rc(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++)
  {
      sprintf(str_data, "MySQL%d", int_data);
    length[i]= strlen(str_data);
    Stmt_Execute(stmt);
    printf("insert %d ok\n", tiny_data);
    int_data += 25;
    small_data += 10;
    big_data += 100;
    real_data += 1;
    double_data += 10.09;
  }

  Stmt_Close(stmt);
}

int main(void)
{
//STEP 1:define varible

MYSQL *mysql;
/////////////////////////////////////////////////////////////////////////////////////

//STEP 2:connect to database

//"127.0.0.1", server ip;

//"root" and "123456",usename and passwd

//"test",which database you want to use

if(!Connect_Server(&mysql,"127.0.0.1", "root", "123456", "test"))
{
   return 0;
}

//////////////////////////////////////////////////////////////////////////////////////

test_prepare(mysql);

//STEP 5:close connect

Mysql_Close(mysql);

return 0;
}

[kenthy@kenthy mysql]$ gcc -Wall -o mysql3 mysql3.c -I/usr/include/mysql/ -L/usr/lib/mysql/ -lmysqlclient -lz
kenthy@kenthy mysql]$ ./mysql3
#######test_prepare#############
Get MYSQL_RES NULL because this is
insert 0 ok
insert 1 ok
insert 2 ok
insert 3 ok
insert 4 ok
insert 5 ok
insert 6 ok
insert 7 ok
insert 8 ok
insert 9 ok
insert 10 ok
insert 11 ok
insert 12 ok
insert 13 ok
insert 14 ok
insert 15 ok
insert 16 ok
insert 17 ok
insert 18 ok

.....

对于insert update delete这些都是没有返回值,跟insert类似,就不一一介绍了!