c语言 mysql 封装_mysql c语言接口。个人封装

#include

#include

#include

#include "mysql.h" //我的机器上该文件在/usr/include/mysql下

/***********************************************************/

/*

一下操作的表。表名为 mytable

*/

/************************************************************/

#define SELECT_QUERY "select * from mytable where name like '%s'"

//#define ALL_QUERY "select * from mytable;"

MYSQL mysql, *sock; //定义数据连接句柄

/*******************************************

函数名: service_sql_init

功能: 服务器数据库初始化

*********************************************/

int service_sql_init(void)

{

mysql_init(&mysql);

/* 连接上数据库 */

// if(!(sock = mysql_real_connect(&mysql, "localhost", "root", "my-name-is?lza1205", "rt5350_user_list", 0, NULL, 0))){

if(!(sock = mysql_real_connect(&mysql, "localhost", "root", "123456", "agent_user_list", 0, NULL, 0))){

fprintf(stderr, "Couldn't connect to engine!\n%s\n\n", mysql_error(&mysql));

perror("");

exit(1);

}

app_printf("mysql init is ok \r\n");

return 0;

}

/*******************************************

函数名: service_sql_select

功能: 根据name 从服务器中找出passwd

*********************************************/

int service_sql_select(char *name, char *passwd)

{

MYSQL_RES *res; //查询结果集

// MYSQL_FIELD *fd; //包含字段信息

MYSQL_ROW row; //存放一行查询结果的字符串数组

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, SELECT_QUERY, name);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

if(!(res = mysql_store_result(sock))){

fprintf(stderr, "Couldn't get result from %s\n", mysql_error(sock));

return -1;

}

printf("number of fields returned : %d\n", mysql_num_fields(res));

while((row = mysql_fetch_row(res))){

printf("Ther name #%s 's passwd is: %s\n", (((row[0]==NULL)&&(!strlen(row[0]))) ? "NULL" : row[0]),

(((row[1]==NULL)&&(!strlen(row[1]))) ? "NULL" : row[1]));

puts( "query ok !\n" ) ;

strcpy(passwd, (((row[1]==NULL)&&(!strlen(row[1]))) ? "NULL" : row[1]));

mysql_free_result(res);

return 0;

}

mysql_free_result(res);

return -1;

// mysql_close(sock);

}

/* 释放结果集 */

void __mysql_free_result(void)

{

MYSQL_RES *res; //查询结果集

do

{

if (!(res= mysql_store_result(sock)))

{

return;

}

mysql_free_result(res);

} while (!mysql_next_result(sock));

}

#define UPDATE_STRING_QUERY "update mytable set %s=\'%s\' where name=\'%s\'"

#define UPDATE_INT_QUERY "update mytable set %s=%d where name=\'%s\'"

/* 根据名字修改数据库中的某项 */

int server_sql_update_string(char *name, char *key, char *val)

{

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, UPDATE_STRING_QUERY, key, val, name);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

return 0;

}

int server_sql_update_int(char *name, char *key, unsigned int val)

{

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, UPDATE_INT_QUERY, key, val, name);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

return 0;

}

/*

下面的函数可以指定某个表格

*/

#define UPDATE_TABLE_STRING_QUERY "update %s set %s=\'%s\' where code=\'%s\'"

#define UPDATE_TABLE_INT_QUERY "update %s set %s=%d where code=\'%s\'"

/*

table :表名

name: 索引

key: 修改的项

val: 修改值

*/

/* 根据索引修改数据库中的某项 */

int sql_table_update_string(char *table, char *name, char *key, char *val)

{

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, UPDATE_TABLE_STRING_QUERY, table, key, val, name);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

return 0;

}

int sql_table_update_int(char *table, char *name, char *key, unsigned int val)

{

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, UPDATE_TABLE_INT_QUERY, table, key, val, name);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

return 0;

}

/* 查询数据库中是否有这个元素 */

#define SELECT_STRING_QUERY "select * from %s where %s like \'%s\'"

int sql_table_select_string(char *table, char *key, char *val)

{

char qbuf[160]; //存放查询 sql 语句字符串

MYSQL_RES *res; //查询结果集

// MYSQL_FIELD *fd; //包含字段信息

MYSQL_ROW row; //存放一行查询结果的字符串数组

sprintf(qbuf, SELECT_STRING_QUERY, table, key, val);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

if(!(res = mysql_store_result(sock))){

fprintf(stderr, "Couldn't get result from %s\n", mysql_error(sock));

return -1;

}

printf("number of fields returned : %d\n", mysql_num_fields(res));

while((row = mysql_fetch_row(res))){

mysql_free_result(res);

return 0;

}

mysql_free_result(res);

return -1;

}

/* 往数据库中插入元素 */

#define INSERT_STRING_QUERY "insert into %s (%s) values (\'%s\')"

int sql_table_insert(char *table, char *key, char *val)

{

char qbuf[160]; //存放查询 sql 语句字符串

sprintf(qbuf, INSERT_STRING_QUERY, table, key, val);

printf("qbuf is %s\n", qbuf);

/* 查询数据库 */

if(mysql_query(sock, qbuf)){

fprintf(stderr, "Query failed (%s)\n", mysql_error(sock));

return -1;

}

return 0;

}