个人封装的一些数据库的c语言接口
#include <stdio.h>
#include <stdlib.h>
#include <stdio.h>
#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;
}
