mysql预处理之select

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


#define BUFFER_SIZE 1000000
#define bool int
#define true 1
#define false 0

bool ConnectServer(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_Search(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!=0)
        {
                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 error!");
                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 ;
}

//Stmt_Set_Bind(&bind[1],MYSQL_TYPE_TIMESTAMP,(char*)&ts,&data_size[1],&is_null[1],BUFFER_SIZE);

void Stmt_Set_Bind(MYSQL_BIND *bind,enum enum_field_types ft,char *buffer,unsigned long *length,\
                        my_bool *is_null,unsigned long buffer_length)
{
                memset(bind,0,sizeof(MYSQL_BIND));
        bind->buffer_type = ft;
        bind->buffer = buffer;
        bind->is_null = is_null;
        bind->length = length;
        if(buffer_length!=0)
        {
         bind[0].buffer_length = buffer_length;
        }
       // #endif

}

bool Stmt_Bind_Result(MYSQL_STMT *stmt,MYSQL_BIND *bind)
{
        if(mysql_stmt_bind_result(stmt,bind))
        {
                printf("%s\n", "bind_result() !");
                return false;
        }
        if(mysql_stmt_store_result(stmt))
        {
                printf("%s\n", "store_reslut() !");
                return false;
        }
        return true;
}

bool Stmt_Data_Fetch(MYSQL_STMT *stmt,my_ulonglong row)
{
        mysql_stmt_data_seek(stmt,row);
        if(mysql_stmt_fetch(stmt))
        {
                return false ;
        }
        return true;
}

bool Stmt_Data_Fetch_Next(MYSQL_STMT *stmt)
{
        if(mysql_stmt_fetch(stmt))
        {
            //printf("%s\n", "mysql_stmt_fetch error");

          return false ;
        }
        return true;
}

unsigned long Stmt_Num_Rows(MYSQL_STMT * stmt)
{
        return mysql_stmt_num_rows(stmt);
}

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

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

int main(void)
{
  char query[300];

    //STEP 1: define varible


    MYSQL *mysql;
    MYSQL_STMT *stmt;
    MYSQL_BIND bind[2];

    //data_size[0] data len

    //bb, save blob data

    //data_int,save int type data

    //is_null, null or not


    unsigned long data_size[2];
    MYSQL_TIME ts;
    int data_int;
    my_bool is_null[2];
    /////////////////////////////////////////////////////////////////////////////////////


  //STEP 2:connect database

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

  //STEP 3:query

  ////////init stmt object

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

  ////////query.

  strcpy(query,"SELECT id,ts FROM info");

  /////////bind query cmd and stmt object

  if(!Stmt_Prepare_Search(mysql,&stmt,query)) return 0;
 
  /////////execute query

  if(!Stmt_Execute(stmt)) return 0;

  //STEP 4: save query res to defined varible

  /////////set MYSQL_BIND struct

  Stmt_Set_Bind(&bind[0],MYSQL_TYPE_LONG,(char*)&data_int,&data_size[0],&is_null[0],4);
  Stmt_Set_Bind(&bind[1],MYSQL_TYPE_TIMESTAMP,(char*)&ts,&data_size[1],&is_null[1],BUFFER_SIZE);
 
  /////////bind MYSQL_BIND and query res

  if(!Stmt_Bind_Result(stmt,bind)) return 0;

  ////////fetch result

  while(Stmt_Data_Fetch_Next(stmt))
   {
      printf("ser_no=%d\n",data_int);
       if(is_null[1])
      printf("%s\n", "NULL\n");
    else
     printf("%04u-%02u-%02u %02u:%02u:%02u\n",ts.year, ts.month,ts.day, ts.hour, ts.minute, ts.second);
   }

  //STEP 5:close object

  Stmt_Close(stmt);
  Mysql_Close(mysql);

  return 0;
}


[kenthy@kenthy mysql]$ gcc -Wall -o mysql2 mysql2.c -I/usr/include/mysql/ -L/usr/lib/mysql/ -lmysqlclient -lz
[kenthy@kenthy mysql]$ ./mysql2
ser_no=1
2010-05-03 09:42:32
ser_no=2
2010-05-03 09:42:37
ser_no=3
2010-05-03 09:42:39
ser_no=4
2010-05-03 09:42:40
ser_no=5
2010-05-03 09:42:40
ser_no=6
2010-05-03 09:42:41
ser_no=7
2010-05-03 09:42:42
ser_no=8
2010-05-03 09:42:42
ser_no=9
2010-05-03 09:42:43
ser_no=10
2010-05-03 09:42:43